Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Win32::OLE Excel issue

by sgp_perl_user (Initiate)
on Feb 02, 2012 at 06:03 UTC ( #951364=perlquestion: print w/ replies, xml ) Need Help??
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 }

Comment on Win32::OLE Excel issue
Download Code
Replies are listed 'Best First'.
Re: Win32::OLE Excel issue
by davies (Vicar) 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

Re: Win32::OLE Excel issue
by Corion (Pope) 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.

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');

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://951364]
Approved by rovf
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (12)
As of 2015-07-08 08:40 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (98 votes), past polls