Esteemed Monks:
I am using Perl to add some charts to an Excel spreadsheet generated in 2003 XML on another system. I can open the XML file and add the charts but when I try to save the modified spreadsheet in the 2007 xlOpenXMLWorkbook format, I get this error:
OLE exception from "Microsoft Office Excel"
Unable to get the SaveAs property from the Workbook class
Win32::OLE(0.1709) error 0x800a03ec
in METHOD/PROPERTY "SaveAs" in PerlMonks.pl line 31

Here is the code the error message refers to:

#!/usr/bin/perl -w

use diagnostics;
use diagnostics -verbose;

use strict;
use Cwd;
use Win32::OLE;
use Win32::OLE::Const "Microsoft Excel";
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);


my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
                Win32::OLE->new('Excel.Application', 'Quit');
$Excel->{Visible} = 1;
$Excel->{DisplayAlerts} = 0;

my $dir = cwd();
$dir =~ s/\//\\/g;

my $path = $dir . "\\PerlMonksTest.xml";

my $Book = $Excel->Workbooks->OpenXML($path);
$Book->Activate;

my $new_file= $dir . "\\PerlMonksTestWB.xlsx";

if(-e $new_file) {unlink $new_file or die "Could not remove '$new_file
+': $!\n";}

$Book->SaveAs( {Filename => "$new_file", FileFormat => 'xlOpenXMLWorkb
+ook', CreateBackup => 'False'} );
#$Book->SaveAs( {Filename => "$new_file"} );

[download]

When I use the SaveAs in line 32 instead of 31, I don't get the error, but the workbook is saved as 2003 XML, which doesn't support charts (as far as I can tell).

I can supply the XML file referred to in the code above (PerlMonksTest.xml) if necessary.

Thanks for your help.

In reply to Problem using SaveAs when trying to change Excel format by JRBJhawk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.