Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Creating desired number of worksheets in excel

by talnewtoperl (Initiate)
on Sep 11, 2013 at 10:04 UTC ( #1053443=perlquestion: print w/ replies, xml ) Need Help??
talnewtoperl has asked for the wisdom of the Perl Monks concerning the following question:

I'm looking for a command in OLE that will create several excel worksheets, with a number i choose first of all, and not to add one every time. thank you very much

Comment on Creating desired number of worksheets in excel
Re: Creating desired number of worksheets in excel
by Corion (Pope) on Sep 11, 2013 at 10:19 UTC
Re: Creating desired number of worksheets in excel
by marto (Chancellor) on Sep 11, 2013 at 10:23 UTC
Re: Creating desired number of worksheets in excel
by ansh batra (Friar) on Sep 11, 2013 at 10:25 UTC
Re: Creating desired number of worksheets in excel
by ansh batra (Friar) on Sep 11, 2013 at 10:31 UTC

    you mean ?

    for(1.2..$count) { $Sheet = $Book->Worksheets($_); $Sheet->Activate(); }

Re: Creating desired number of worksheets in excel
by poj (Curate) on Sep 11, 2013 at 10:49 UTC
    Use count in add method.
    my $add = 10; my $workbook = $excel-> Workbooks -> Add(); my $last = $workbook->Sheets->Count; $workbook->Sheets->Add({After=>$workbook->Sheets($last), Count=>$add});
    poj
Re: Creating desired number of worksheets in excel
by talnewtoperl (Initiate) on Sep 11, 2013 at 11:00 UTC
    thank you! one more question, is there a command that i can check if "Sheet3" for example is open? by sheet number or name?

      Have you read Using Win32::OLE and Excel - Tips and Tricks? It contains many helpful tips, and also how to find the Excel methods within the Excel documentation.

      If you think you've seen this response before, maybe now is the time to follow its advice.
Re: Creating desired number of worksheets in excel
by sundialsvc4 (Monsignor) on Sep 11, 2013 at 12:10 UTC

    The ways of doing this in Perl are pretty much identical to that of any other language, since the underlying subsystem is OLE.   You can directly reference any sheet in the workbook without affecting the user-interface (e.g. by activating or opening the sheet), and you probably don’t want to except to “put on an I’m Busy Show” for the end-user.   You can reference the elements in any Collection (such as Sheets either by index or by name.   Again, the principles and modus operandi are the same everywhere.   Learn how to do it first (say, in Excel’s own built-in VB/macro facility), then translate the known-good methods into Perl-talk.

    Incidentally, I am of the opinion that “when in Rome, do as the Romans do.”   In the Windows environment, that’s OLE.   It works, and it works quite well.   Much better, IMHO, than trying to build a file.

Re: Creating desired number of worksheets in excel
by davies (Vicar) on Sep 11, 2013 at 12:18 UTC

    Beating my own drum, but the technique for creating n sheets in a new file without looping is shown in RFC Tutorial - Deleting Excel Rows, Columns and Sheets. Just don't leave the user with a mess to clear up, but the technique for that is shown too, as is the explanation of what creates a mess.

    Regards,

    John Davies

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1053443]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2014-08-01 03:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (256 votes), past polls