-----8<--------
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.
-----8<--------
Now't to do with memory. But it's a good tip. Try and play along with
what it's suggesting. The following does what you're after:-
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $File = "C:\\Temp\\Glen.xls";
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;
my $SavedBook = $Excel->Workbooks->Open($File);
for(1 .. 10)
{
$SavedBook->Worksheets->Add();
}
print "Three cold Corona's please ;)\n";
Cheers,
Just in
_______________________________________________
Perl-Win32-Users mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
