Win32::OLE Excel worksheet selection question 2

[wraheem]

I have a Perl script that performs various operations on files in a folder. I orginally set it up to do one file and stop. I then got it to output the data to Excel. Now I want it to do all of the files in the folders and report the data to different worksheets. I can make it name all the worksheets the appropiate names but when it goes to fill in the data it is += incrementing the numbers. So I instead did a foreach $file(@files) to work on the files individually.
Heres the problem; when I had it write to the worksheets it basically overwrited (sp, is that a word?) the same first sheet over and over. So I had a sheet for each file but only the first sheet has info. So I tried selecting every sheet like so:

foreach $csv(@csvfiles){
$c++; #to create number of sheets corresponding w/ # of      
      #files
}


$excel->{Visible} = 1;
$excel->{SheetsInNewWorkBook} = $c;
$workbook = $excel->Workbooks->Add();







foreach $cs(@csvfiles){
$kount = 1;
$worksheet = $workbook->Sheets->Add();
$worksheet->{Name} = "$cs";
$worksheet = $workbook->Sheets($cs)->Select();
$worksheet = $workbook->Worksheets($kount)->Activate();

$range=$worksheet->Range('A1:G1');

I was trying $cs as the Worksheets object but it didn't work as well... the error I received is:
Can't call method "Range" without a package or object reference

I just basically want to select or activate or whatever the next sheet to begin writing again.

TIA
wali

 

[dmazzini]

if the current Excel VBA Macro just works for one sheet..then when you run this macro from Perl will do the same..run just for this sheet.

You could change the Excel VBA macro to execute it on all sheets. And then run this macro from Perl. It might be more easy..Lrt me know if it is possible for you or we could find the way to do it from Perl.


dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

I was able to make the change at the Excel macro so that it ran for all sheets. I still don't understand the sheet activation rules when done via PERL. I knew I had to run the macro twice as it was previously written and I thought I could simply activate the appropriate sheet prior to running the macro 2X as I attempted but I clearly am missing the boat on all this and need to do some reading.

Thanks for your help throughout.

ljs

 

[dmazzini]

Hi

You need to use the right sheet when you are reading, writing, applying functions or returning properties of the excel sheet document.

To use sheet 1. e.g.

$Sheet = $Book->Worksheets(1);

Another example: To use a workbook. Routine protecting excel file.

sub protecting_excel_file{

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{DisplayAlerts}=0;
my $Book = $Excel->Workbooks->Open("$excel_file");
$Book->ProtectSharing({Filename=>$filename_datafill,SharingPassword=>"password"});
$Book->Save;

}


dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

More helpful and useful info...Thanks. Have a great day.

ljs