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

Why am I not getting the name of the file or am getting "Can't call method Worksheets on an undefine value at h:\\Script\\.....". The worksheet is called JUN05 in the exel spreadsheet, so Im trying to make sure it uses that worksheet. 
#!/usr/bin/perl

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

#$Win32::OLE::Warn = 3;                                # die on errors
+...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

# open Excel file
my $Book = $Excel->Workbooks->Open("Reten_template.xls"); 


my $Sheet = $Book->Worksheets('JUN05');

[download]
I tried testing to see what the value is an it gives me Win32::OLE=Hash(0x1832544) How would I be able to grab the actual name rather than this hash? 
#!/usr/bin/perl

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';


# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

# open Excel file
my $Book = $Excel->Workbooks->Open("H:\\Scripts\\Reten_template.xls");
+ 



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

 print "$Sheet\n";

[download]
Thanks in advance....

Replies are listed 'Best First'.
Re: Accessing Worksheet name in excel
by Tortue (Scribe) on Oct 09, 2005 at 19:24 UTC
    Not sure why you're showing two different code samples. Is it that the latter one works?

    In the first code sample:

    Try making sure you are indeed opening the file. "Can't call method Worksheets on an undefine value" suggests you haven't opened it. Uncomment the "die on errors" line. And/or check the return code: 

    my $Book = $Excel->Workbooks->Open("Reten_template.xls") || die "can't
+ open file";

    [download]
    Then try displaying all the sheets to make sure you have the right name: 
    for (1..$Book->Worksheets->Count) {
    print "$_: <", $Book->Worksheets($_)->Name, ">\n";
}

    [download]

    By the way, here's a good way to make sure you have the full path of the file name: 

    # open Excel file
use File::Spec;
my $file = File::Spec->rel2abs("Reten_template.xls");
my $Book = $Excel->Workbooks->Open($file) or die "can't open $file: $!
+\n";

    [download]
    If you manage to open the Excel file, and you have the right name, then this should work: 
    my $Sheet = $Book->Worksheets('JUN05');

    [download]
[reply]
[d/l]
[select]
      Sorry to confused you but the 1st code gives the the undefine value error and the 2nd one gave me the Win32::OLE=Hash(0x1832544) but I want the actual text or name of the worksheet. Thanks for your input.. I ran your File::Spec code and it gave me the same undefine value error as my 1st set of code.
[reply]
        Ok, then it looks like you have three problems. You can't open the right file (in the first script), you can't access a sheet by name, and you're surprised to get "Win32::OLE=Hash(0x1832544)" (in the second).

        In the second script, you can open the right file. So, for now, forget about file access and solve the problem of accessing a sheet by name by working from the second script. Have you tried listing the names of the worksheets based on your second script, using the example I gave you?

        Your second script is working normally. print "$Sheet" returns a strange value because $Sheet is an object. To access the sheet's name and print it, use 

        print $Sheet->Name, "\n";

        [download]

        If you continue to have problems, please post your exact code and the output you get.

[reply]
[d/l]

Back to Seekers of Perl Wisdom