CODEWITHSUNDEEP
excelperlsave-as
Chris
Chris

Reputation: 558

Using Perl to SaveAsXMLData on Excel 2010 Worksheet

I've an Excel 2010 spreadsheet with an XML map defined within it. Using Perl I want to save the worksheet as XML Data. I do not need to export the XML map file. From within Excel I can select "File > Save As > Save as type : XML Data". This is the output I want to create, but from my Perl script.

I can output the worksheet in CSV format using the SaveAs command with enum 6. I can also output the spreadsheet in XML format using SaveAs with enum 46, but this is not what I want. I want just the XML Data..

There appears to be a SaveAsXMLData function but I'm unable to get it working. Any help appreciated.

use strict;
use warnings;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);

$Win32::OLE::Warn = 3; # Die on Errors.

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

$Excel->{DisplayAlerts}=0;

my $excel_file = 'c:\\temp\\master.xlsx';
my $csv_file   = 'c:\\temp\\master.csv';
my $xml_file   = 'c:\\temp\\master.xml';

my $workbook = $Excel->Workbooks->Open($excel_file);

# Alt+F11 in Excel to start VBA and after that F2 to start Object browser.
# 6 is CSV format
# 46 is XML spreadsheet
$workbook->SaveAs( $csv_file, 6 );

# Now just the XML Data
# The map is called MDBAC_Map
my $objMapToExport = $Excel->Workbooks->XmlMaps("MDBAC_Map");
$workbook->SaveAsXMLData( $xml_file, $objMapToExport );

$workbook->Close();
$Excel->Quit();

Upvotes: 2

Views: 1009

Answers (1)

Chris
Chris

Reputation: 558

Fixed this myself (I was 99% there!). Using the macro recorder within Excel confirmed the required function calls as follows:

ChDir "C:\temp"
ActiveWorkbook.SaveAsXMLData Filename:="C:\temp\master.xml", Map:= _
        ActiveWorkbook.XmlMaps("MDBAC_Map")

The line of code for exporting the XML map is wrong. Changed the above code as follows and the script works fine:

my $objMapToExport  = $workbook->XmlMaps("MDBAC_Map");

Upvotes: 1

Related Questions