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

I encountered a problem while developing my PERL script which reads Excel files and do some calculations. I use ActivePerl 5.14.2 Build 14R02 on Windows XP (I am not asking you to help debug ActivePerl but just to provide full info).

Is there a bug list for Win32::OLE that documents issues with Excel?

If there is a more appropriate forum to raise this question, please let me know.

Note that the code was working before (and I saved old versions of the working code).

All of a sudden the script fails on opening the Excel workbook using the call $in_Excel->Workbooks->open($workbook) & $workbook is the workbook filename . This is the error message: "Can't call method "Worksheets" on an undefined value at xls2csv.pl line 15"

I put a "print $workbook; " statement before the call and the proper filename (residing in the same directory). Running the old working code gets me the same error.

Please help.

Code extraction: 

sub LoadFile

{

my $map_file = shift;

print "$map_file\n";

my $in_Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win
+32::OLE->new('Excel.Application', 'Quit');

my $in_eBook = $in_Excel->Workbooks->open($map_file) || die "Failed to
+ open Workbook $map_file.\n";;

# dies at the last statement

}

[download]

Replies are listed 'Best First'.
Re: Win32::OLE Excel issue
by davies (Monsignor) on Feb 02, 2012 at 10:13 UTC

    See Re: Manipulating open excel sheet for (a) working code on taking control of an existing instance of Excel and (b) reasons why this is not a good idea. Any of the things I mention could be the cause of the erratic behaviour you report. There may also be other issues I haven't thought of.

    Regards,

    John Davies

[reply]
Re: Win32::OLE Excel issue
by Corion (Patriarch) on Feb 02, 2012 at 08:49 UTC

    Nowhere in your code do you mention Worksheets, so either the code you show is wrong or the error message. Please post a short, self-contained example that reproduces the error, together with the error message.

    As a side node, if you wrap your code or data in <code>...</code> tags, it renders and downloads much nicer, which makes it even easier for us to try to reproduce your problem.

[reply]
[d/l]
[select]
Re: Win32::OLE Excel issue
by Anonymous Monk on Feb 02, 2012 at 08:56 UTC 
    use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';

sub LoadFile
{
my $map_file = shift;
print "$map_file\n";
my $in_Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win
+32::OLE->new('Excel.Application', 'QUIT');
print "$in_Excel  \n";
$in_Excel->{DisplayAlerts}=1;    #0 is hide alerts
my $in_eBook = $in_Excel->Workbooks->Open($map_file);
print "$in_eBook  \n";

$in_eBook->SaveAs({Filename =>'D:\Perl Programs\OLE Progs\file11.xls',
              FileFormat => xlWorkbookNormal});  
$in_eBook->Close();
$in_Excel->Quit();
} 
&LoadFile('D:\Perl Programs\OLE Progs\file.xls');

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom