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]

It seems that you are not passing the object "Workbook" to this part of the script:

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

One test:

Use:

my $sheet = $workbook->Worksheets($cs);

Instead of:

$worksheet = $workbook->Sheets($cs)->Select();

In this case, $cs is an integer, thus you are specifying the sheet number to use.

Cheers

dmazzini
GSM System and Telecomm Consultant

 

[wraheem]

Thanks dmazzini fo replying, that of course worked (now to attack the other problems) 1 question however.

1.You say that $cs is an integer; the instance of the array that $cs would be in is actually a file name (a string) and the corresponding sheet is named as such. (ie $cs = one.csv). How is it an integer?

(If it is some rudementory part of Perl I should already know, just say so and I'll look it up ;-) ).

Thanks

 

[dmazzini]

There are two things to bear in mind:

Sheetname and SheetNumber.

You can also select the sheet by sheetname.

my $sheet = $workbook->Worksheets("FIRST_SHEET");


You also could get the sheetnumber if you know the sheetname or vice-versa. That;s what I am doing when I write my scripts....


my $sheetcnt = $Book->Worksheets->Count();
$iSheet=1;
foreach ($iSheet..$sheetcnt){
print "$iSheet =>" . "\t" . $Book->Worksheets($_)->{Name} ."\n";
$iSheet_start= $iSheet if ( $Book->Worksheets($iSheet)->{Name} =~m"Start");
$iSheet_alpha= $iSheet if ( $Book->Worksheets($iSheet)->{Name} =~m"ALPHA");
$iSheet_beta= $iSheet if ( $Book->Worksheets($iSheet)->{Name} =~m"BETA");
$iSheet++;
}


dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

Hi,

I am having the same original problem but I still do not know the fix after reading this post. My code is simpler however, I still only write out to Worksheet 1 instead of Worksheet 2 when needed. Can anyone tell me what I am doing wrong ? Also, is there a book or website that explains the PERL Excel interface code and all the possible commands/ syntax?

Here is abbreviated code... Thanks:

$Book 		= $Excel->Workbooks->Open($myexcelfile);
$Sheet1		= $Book->Worksheets(1);
$Sheet2		= $Book->Worksheets(2);

foreach my $comp_id (%array) {
    if ($comp_id eq "Funded") { 
        $Sheet= $Book->Worksheets(1);
    } else {
        $Sheet= $Book->Worksheets(2);
    } #-end if		      
    $c = 1;
    $Sheet->Cells($i,$c)->{Value} = $tape_server_list; $c++;
    $Sheet->Cells($i,$c)->{Value} = $tape_server_list; $c++;

 

[dmazzini]

Some test.

Check the value of COMPID1 and COMP2...

Try using declaring the variable with my

Could you put the full code to se what is happening, it is possible some data too..





my $Book = $Excel->Workbooks->Open($myexcelfile);
my $Sheet1 = $Book->Worksheets(1);
my $Sheet2 = $Book->Worksheets(2);

foreach my $comp_id (%array) {
if ($comp_id eq "Funded") {
print "COMPID1=$comp_id\n";
$Sheet= $Book->Worksheets(1);
} else {
print "COMPID2=$comp_id\n";
$Sheet= $Book->Worksheets(2);
} #-end if
$c = 1;
$Sheet->Cells($i,$c)->{Value} = $tape_server_list; $c++;
$Sheet->Cells($i,$c)->{Value} = $tape_server_list; $c++;


dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

Changing a little bit...


my $Book = $Excel->Workbooks->Open($myexcelfile);
my $Sheet1 = $Book->Worksheets(1);
my $Sheet2 = $Book->Worksheets(2);

foreach my $comp_id (%array) {
if ($comp_id eq "Funded") {
$Sheet1->Cells($i,$c)->{Value} = $tape_server_list; $c++;
$Sheet1->Cells($i,$c)->{Value} = $tape_server_list; $c++;

} else {
$Sheet2->Cells($i,$c)->{Value} = $tape_server_list; $c++;
$Sheet2->Cells($i,$c)->{Value} = $tape_server_list; $c++;

} #-end if
$c = 1;

dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

Thanks for the reply...I can send the actual code but I thought the abbreviation might be best to simplify the issue. I now notice that my abbreviation wasn't completely accurate but the real point is that the second worksheet is never written to. The IF/ ELse logic has been tested and works as expected when I pront to STDOUT.

I see from your second response that I have to repeat each line of the Excel cell write into both the IF and the ELSE. There are hundreds of these lines and I would rather not have to repeat them(I guess this could be a called subroutine instead). I just don't understand why I am having this issue. Is it an addressng reference issue and
therefore requires a specific calling protocol ? I can't find any documentation on any of this. Do you know of any ?
Thanks so much.

-ljs

 

[dmazzini]

What are the values of %array. Is it a hash?



dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

This abbreviated code is a little more accurate. The Print statement inserted prove that $comp_id when "Funded" should and do print out that message and write to Worksheet 1. When $comp_id is "Pending" or something other than "Funded" the correct print statement prints out and blank lines are written to Worksheet 1 and nothing is written to Worksheet2.
It's an addressing issue I suppose...just not sure how to resolve. Thanks...here is the code again:

$Book         = $Excel->Workbooks->Open($myexcelfile);
$Sheet1        = $Book->Worksheets(1);
$Sheet2        = $Book->Worksheets(2);

foreach my $comp_id (sort keys %hash) {
    if ($comp_id eq "Funded") { 
        print "\$comp_id WK1=$comp_id\n";
        $Sheet= $Sheet1;
    } else {
        print "\$comp_id WK2=$comp_id\n";
        $Sheet= $Sheet2;
    } #-end if              
    $i=1;$c = 1;
    $Sheet->Cells($i,$c)->{Value} = $tape_server_list{$comp_id}; $c++;
    $Sheet->Cells($i,$c)->{Value} = $tape_server_list{$comp_id}; $c++;

 

[dmazzini]

Hey

What you should do is assign the value to the $sheet using the prperty. I mean
Instead of

$Sheet= $Sheet2; you should use

$worksheet = $workbook->Worksheets(2);

Here the full example:

use Win32::OLE;
$file = shift || "C:\\temp\\MyTest.xls";
$excel = Win32::OLE->GetActiveObject('Excel.Application');
unlink($file) if (-e $file);
unless($excel)
{
$excel = new Win32::OLE('Excel.Application', \&QuitApp)
or die "Could not create Excel Application object";
}

$excel->{Visible} = 1;
$excel->{SheetsInNewWorkBook} = 2;
$workbook = $excel->Workbooks->Add();
$worksheet1->{Name} = "MySheet1";
$worksheet2->{Name} = "MySheet2";

@array = ("Funded","Funded","Pending","Pending","Funded","Funded","Funded");

$i=1;$c = 1;


foreach my $comp_id (@array) {

if ($comp_id eq "Funded") {
$tape_server_list{$comp_id}="Funded";
$worksheet = $workbook->Worksheets(1);
} else{
$tape_server_list{$comp_id}="Other";
$worksheet = $workbook->Worksheets(2);
}

$worksheet->Cells($i,$c)->{Value} = $tape_server_list{$comp_id}; $c++;

}

$workbook->SaveAs($file);




dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

Thanks so much...I will take a closer look at this and adapt it with my code in the morning to see if it does the trick. I still do not understand it and I wish there were some doc to help with it. Do you know either a good manual or web site that gives out this information? If so, could you pass it along...thanks once again for your help.

ljs

 

[dmazzini]

links:

http://www.google.com/search?q=perl+excel+win32::ole&hl=en&lr=&start=0&sa=N

dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

I implemented your code exactly and there is no change in the outcome. It writes out to "MySheet1" but never to "MySheet2" even though the print to STDOUT indicates that non- "Funded" entries exist. I am sure it is an object addressing or Sheet activation issue but I have added $Sheet->Activate() statement after I determine the sheet to use and this still doesnt help. Did the code work for you ?

Again, thanks for any help. -ljs

 

[dmazzini]

ljsmith

Did you copy and paste it? I have tested it and it works.
It writes to sheet1 and sheet2.
Copy the example that I posted and test it.

Let me know the results. May be somebody else for the forum Can do it as well.

Regards





dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

Your code does work...I tested it. I then went back to my code and discovered the problem....I didnt set up a second scalar for $i for the second worksheet. The first several hundered entries were "Funded" and showed on that worksheet. When a non- "Funded" showed up the row that it wrote too was much farther down the sheet rather than writing in row # 1 like I thought it would. Separate scalars for the row iteration corrected this and its working fine now.

I still think that there must be some location where I can get better doc for this. Your google html link just doesnt give me enough detail.

As an example: $excel->{SheetsInNewWorkBook} = 2; How am I supposed to know this statement is needed or even exists if there is no doc on this ? How did you know ?

Everytime I see sample code, I see logic and commands that are new to me only because I can't find any good documentation.

Thanks for all of your help. It seems there are not many experts out there for this subject matter. -ljs

 

[ljsmith91]

dmazzini,

Another question....I know how to run a macro against a worksheet with the run command...

$Excel->Run("VBAProject.mod_TapeProjects.format_tape_projects");

However, I dont seem to know how to direct it to the worksheet I want. It always runs against worksheet1 and I need it to run against worksheet1 and worksheet2. What might the secret to this be?

Thanks.

 

[dmazzini]

Hi

Win32::OLE provides an interface to OLE Automation from Perl. OLE Automation brings VisualBasic like scripting capabilities and offers powerful extensibility and the ability to control many Win32 applications from Perl scripts.

I have been learning this Module reading examples and using the Excel VBA help. You can translate the Excel VBA functions, properties and attributes to Perl structure.

There is a link with some theory

http://cpan.uwinnipeg.ca/htdocs/libwin32/Win32/OLE.html

Regarding the macro question. Can you post some of the perl code? When you run the macro from Excel, it does for al sheets?










dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

Another hint..

The Win32::Ole module use the object oriented programming style. It uses Object methods and properties

e.g $worksheet = $workbook->Worksheets(1);

It means that $worksheet is not really a perl variable. It's an object.

I ca recommend you read a little bit about object oriented programming. It will help you a lot when you are translating excel vba function to Perl.

dmazzini
GSM System and Telecomm Consultant

 

[ljsmith91]

dmazzini,

I do have some reading to do, I just havent been able to find where to look. You have given me some direction...thanks.

The Excel macro does not run against all sheets.... I will have to figure out how to do that. I will start searching the Excel help. It only runs against the active sheet now. My Perl code looks like this now although I have tried many different ways of accomplishing it without success. The Macro only works on worksheet # 1.

$Excel = Win32::OLE->GetActiveObject('Excel.Application');
unless($Excel)	{
$Excel = new Win32::OLE('Excel.Application', \&QuitApp) or die "Could not create Excel Application object";
}#-end unless
$Excel->{Visible} = 1;
$Book 		= $Excel->Workbooks->Open($pfileI_report);
$Worksheet1->{Name} = "Funded";
$Excel->Run("VBAProject.mod_TapeProjects.format_tape_projects");
$Worksheet2->{Name} = "Non-Funded";
$Excel->Run("VBAProject.mod_TapeProjects.format_tape_projects");

[\code]