Exporting an Excel Chart to PNG using Win32::OLE

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

Hi, I am using win32::OLE to draw chart in MS Excel & then exporting it to a png file. I was able to draw graph on excel & save it. but am getting an error while exporting it to the png file. Exporting part of the script is as follows.

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);
use Win32::OLE;
use Win32::OLE::Const;
$Win32::OLE::Warn = 3; # die on errors...



my $filter = 'PNG'; # can be GIF, JPG, JPEG or PNG    

$filename = "C:\\graph1.xls";
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit'); # use the Exc
+el application if it's open, otherwise open new
        my $Book = $Excel->Workbooks->Open( $filename ); # open the fi
+le
        foreach my $Sheet (in $Book->Sheets) { # loop through all shee
+ts
        foreach my $ChartObj (in $Sheet->ChartObjects) { # loop throug
+h all chartobjects in the sheet
        #$pngsave = join("",split(/\s+/,$graphtitle));
        $dirpath = "c:\\ExcelTest";
        mkdir ("$dirpath", 0777); 
        my $savename = "$dirpath\\test" . ".$filter";
        
        
        # Write image to PNG file
        $ChartObj->Chart->Export({
        FileName => $savename1,
        FilterName => $filter,
        Interactive => 0});
        
        }
        }
[download]

The error I get is

Win32::OLE<0.1701> error 0x800a03ec
in METHOD/PROPERTYGET "Export" at C:\test.pl line 26
[download]

What does this mean?

Can anyone plz help me in resolving it?

20050328 Edit by castaway: Changed title from 'Perl & Win32'

Comment on Exporting an Excel Chart to PNG using Win32::OLE Select or Download Code

Replies are listed 'Best First'.
Re: Perl & Win32 by mlh2003 (Scribe) on Mar 18, 2005 at 11:32 UTC
Not sure if it was a typo in your code, but you define $savename in the innermost loop, but write to $savename1 (the FileName parameter) in the Export method when writing the image. _______ Code is untested unless explicitly stated mlh2003	[reply]
Re^2: Perl & Win32 by gellyfish (Monsignor) on Mar 18, 2005 at 11:42 UTC
Well spotted, this is of course why it says: Execute your scripts with perl -w and use strict - this catches most of your errors. in the Using OLE with Perl FAQ that comes with ActivePerl. /J\	[reply]
Re: Perl & Win32 by gellyfish (Monsignor) on Mar 18, 2005 at 11:33 UTC
This is possibly not really a Perl problem at all - as you can see - it appears to be a problem with the regional settings in Excel as described in these articles at MSDN. Update: It is likely that Re: Perl & Win32 is the actual reason behind this - the error number seems to be some generic Excel automation error. /J\	[reply]
Re^2: Perl & Win32 by bart (Canon) on Mar 18, 2005 at 11:43 UTC
The "Perl problem" is, IMO, turning these obscure Win32::OLE error messages into something far more informative.	[reply]
Re^3: Perl & Win32 by spurperl (Priest) on Mar 18, 2005 at 11:53 UTC
It would be difficult since errors of this kind are tunneled directly from the OLE engine. It's OLE's cryptic messages that should be fixed - then Win32::OLE will be fine.	[reply]
Re^4: Perl & Win32 by gellyfish (Monsignor) on Mar 18, 2005 at 12:13 UTC
Re^4: Perl & Win32 by bart (Canon) on Mar 18, 2005 at 12:12 UTC

Back to Seekers of Perl Wisdom