http://www.perlmonks.org?node_id=498619


in reply to Accessing Worksheet name in excel

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]

Replies are listed 'Best First'.
Re^2: Accessing Worksheet name in excel
by Anonymous Monk on Oct 10, 2005 at 00:07 UTC
    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]

In Section Seekers of Perl Wisdom