merrymonk has asked for the wisdom of the Perl Monks concerning the following question:

I want to:
a. create a new Excel spreadsheet
b. add more than one table
c. give a name to each table
d. add data to each table
e. save the spreadsheet with all the data intact.

The Perl below is my attempt to do this. It asks you for the number of sheets.
For 1 all is ok and the spreadsheet is saved.
For 2, two tables, a spreadsheet is saved but there is only data in the 2nd table and only the second table has been renamed (I am reasonably sure data has been written
to the first table because with a ‘larger’ case I can see it being written).
For 3, it fails to add the third table
Can a wiser Monk explain how to cure these problems? 
use strict ;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel, $workbook, $sheet, $j, $row, $range, $cell_str, $content, 
+$contentb, , $contentc, $width_found, $id, $test_cell_idg );
my ($worktable_name, $jwk, $jr, $sheets_total, $new_wk); 
# request the number of worktables
print "Enter the number of worktables ";
$sheets_total = <STDIN>;
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;
for($jwk = 1; $jwk <= $sheets_total; $jwk ++) {
#___ ADD NEW WORKBOOK
    $workbook = $excel    -> Workbooks -> Add;
#    $sheet    = $workbook -> Worksheets("Sheet 1");
    if($jwk == 1) {
        $sheet    = $workbook -> Worksheets("Sheet1");
        $sheet                -> Activate;
#___ ADD NEW WORKSHEET

    } else {
        $workbook -> Worksheets -> Add({After => $workbook -> Workshee
+ts($workbook -> Worksheets -> {Count})});
        $new_wk = "Sheet" . $jwk;
        print($new_wk);
        $sheet    = $workbook -> Worksheets($new_wk);
        $sheet                -> Activate;
#___ CHANGE WORKSHEET NAME
    }
    $worktable_name = "worktable_name-" . $jwk;
    print "\njwk <$jwk> sheet name <$worktable_name> workbook <$workbo
+ok> sheet <$sheet>\n";
    $sheet -> {Name} = $worktable_name;
    for ($jr = 1; $jr <= 10; $jr++) {
        $range = 'A' . $jr;
        $sheet -> Range($range) -> {Value} = $jr . '-' . $jwk;
    }
}
$excel -> {DisplayAlerts} = 0; # This turns off the "This file already
+ exists" message.
$workbook -> SaveAs ("c:\\n-sheets-test");
$excel    -> Quit;

[download]

Replies are listed 'Best First'.
Re: Excel - adding & writing to multiple worksheets query
by technojosh (Priest) on Oct 15, 2010 at 17:30 UTC
    I added use warnings; and moved one of the excel OLE calls out of your for loop... your code comments are oddly placed IMO but that has no effect on behavior ;) 
    use strict ;
use warnings;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
my ($excel, $workbook, $sheet, $j, $row, $range, $cell_str, $content, 
+$contentb, , $contentc, $width_found, $id, $test_cell_idg );
my ($worktable_name, $jwk, $jr, $sheets_total, $new_wk); 
# request the number of worktables
print "Enter the number of worktables ";
$sheets_total = <STDIN>;
#___ DEFINE EXCEL
$excel = CreateObject OLE "Excel.Application";
#___ MAKE EXCEL VISIBLE
$excel -> {Visible} = 1;

## I MOVED THIS OUT OF THE FOR LOOP ##
$workbook = $excel    -> Workbooks -> Add;

for($jwk = 1; $jwk <= $sheets_total; $jwk ++) {

#___ ADD NEW WORKBOOK
    
#    $sheet    = $workbook -> Worksheets("Sheet 1");
    if($jwk == 1) {
        $sheet    = $workbook -> Worksheets("Sheet1");
        $sheet                -> Activate;
        
#___ ADD NEW WORKSHEET

    } else {
        $workbook -> Worksheets -> Add({After => $workbook -> Workshee
+ts($workbook -> Worksheets -> {Count})});
        $new_wk = "Sheet" . $jwk;
        print($new_wk);
        $sheet    = $workbook -> Worksheets($new_wk);
        $sheet                -> Activate;
#___ CHANGE WORKSHEET NAME

    }
    $worktable_name = "worktable_name-" . $jwk;
    print "\njwk <$jwk> sheet name <$worktable_name> workbook <$workbo
+ok> sheet <$sheet>\n";
    $sheet -> {Name} = $worktable_name;
    for ($jr = 1; $jr <= 10; $jr++) {
        $range = 'A' . $jr;
        $sheet -> Range($range) -> {Value} = $jr . '-' . $jwk;
    }
}
$excel -> {DisplayAlerts} = 0; # This turns off the "This file already
+ exists" message.
$workbook -> SaveAs ("c:\\n-sheets-test");
$excel    -> Quit;

    [download]
    -TJ
[reply]
[d/l]
[select]
      Many thanks - that works just as I wanted it to!!
      I am sure your efforts have saved me much toil and trouble.
[reply]
        merrymonk:

        You need to STOP coming here solely to avoid "toil and trouble" and come, instead, to learn. - - for abusing the Monastery (again!) as a code-writing or code-editing service.

        And, as you have been told several times, </br> is not a valid code here. PM doesn't use a full-spec html, xml, xhtm, or anything standard in user-written nodes. The acceptable markup can be found, profusely illustrated, in Markup in the Monastery; a briefer version is in Perl Monks Approved HTML tags .

        When you want to make a bulleted list, use <ul><li>an item</li><li>next item</li><li>last</li></ol>; if you want your list numbered, use <ol> (li pairs, to taste) </ol> which will be much easier to read.

        You'll also make your questions more readily readable (comprehensible) if you separate your ideas (topics) into paragraphs. Stream-of-consciousness narrative may be fine in fiction, but it really doesn't help you get help here.

        Update: linked to wrong faq for "Approved HTML Tags;" now fixed.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom