Copy vs Add when adding sheets to a workbook past 255

[Glen Plantz]

Title: Message

Hi Folks,

 

Last Thursday I posted a query about an error I was getting when trying to create sheets in an Excel workbook past a sheet count of 255.

 

Justin Allegakoen was kind enough to send me a script that works to add sheets past 255, using the "Add" function.

 

Here is a snippet of the code that Justin sent me;

my $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit');

$Excel->{Visible} = 1;

$Excel->{SheetsInNewWorkbook} = 255;

my $Book = $Excel->Workbooks->Add();

foreach my $i(1 .. 255) {

print "Renaming Sheet$i\n";

my $Sheet = $Book->Worksheets($i);

$Sheet->{Name} = "Glen $i";

}

$Book->SaveAs($File);

$Book->Close;

for(1 .. 10)

{

$SavedBook->Worksheets->Add();

}

This code works great, but why does the "Add " function work here past a sheetcount of 255, when the "Copy" function that I mentioned in my original post, throws an error when when the sheetcount > 255 ???

 

I should have mentioned in my original post that I have an existing Excel sheet that I need to open and use as a "Template" for all the other sheets in this workbook that I'm creating.

 

Thanks in advance for any help here.... I'm stumped.

 

Glen Plantz

 

 

Here is my original post...

# *********************************************************************************************************************************************************************************

I need some help with getting around the 255 sheet limit for NEW workbooks in Excel. Here is a snippet from the book "Excel: The Missing Manual"

 

Note: Although you're limited to 255 sheets in a new workbook, Excel doesn't limit how many worksheets you can add after you've created a workbook. The only factor that ultimately limits the number of worksheets your workbook can hold is your computer's memory. However, most modern day PCs can easily handle even the largest of workbooks.

 

I've tried closing and and immediately reopening a Workbook, to no avail. My system has 1 Gig of memory. No other apps are running.

 

 

I keep a running count of the number worksheet's I've created in my Perl script, and no matter whether I've closed and reopened, or what, as soon as the count gets to 256 worksheets, the following error occurs:

 

D:\Perl>perl CsaaRetrieveToTemplate.pl  >  outputA3_for_lv.txt
OLE exception from "Microsoft Excel":

 

Unable to get the Copy property of the Worksheet class

 

Win32::OLE(0.1701) error 0x800a03ec
    in METHOD/PROPERTYGET "Copy" at CsaaRetrieveToTemplate.pl
line 976.

 

Here is a snippet of the code ( the last line is throwing the above error )

 

# ******************************************************************************************************

 

        my $templateCopy      = "<Hierarchy Member>";
           
        $templateSheet = $Template_Workbook->Worksheets("$templateCopy");
        $templateSheet->Activate();
       
        my $shCount = $Template_Workbook->Worksheets->{Count};
        print "shCount = $shCount\n";
      
        $templateSheet->Copy({After=>$Template_Workbook->Worksheets($shCount)}) or die Win32::OLE->LastError();

 

# *******************************************************************************************************************************************************

 

I hope someone has a solution... this is a "show stopper" for the work I'm doing.

 

Thanks in advance for any help...

 

Glen Plantz

 

_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs