Export xlsx file to xml using PowerShell

Question

I want to export data that are on an xlsx file (pets.xlsx) to xml format.

When I use the Excel GUI, I can save the Sheet1 to XML and get the desired output file.

I can also get the desired result by exporting the data through the Developer Tab in Excel GUI.

Now I want to do this in PowerShell. I used this code that I found here :

$xlXMLSpreadsheet = 46
$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("d:\pets.xlsx")
$WorkBook.SaveAs("d:\pets.xml", $xlXMLSpreadsheet)
$Excel.Quit()

The problem is that the generated xml file is not the expected result.

Thanks in advance

Amine

Note : I've also posted the problem here to maximize my chances to solve the problem.

The list of supported formats is here. The only plausible candidate is xlOpenXMLWorkbook, but I'm not sure that's it. — mklement0
– mklement0, Commented Jun 25, 2021 at 22:49
I don't think $xlXMLSpreadsheet = 46 corresponds to the XML file format you need; rather, it corresponds to an XML Spreadsheet file format, which is why you are not getting the output you desire. You are probably going to have to use an approach similar to this. — D-squared
– D-squared, Commented Jun 25, 2021 at 22:51

Amine · Accepted Answer · 2021-06-27 22:11:14Z

0

After many searches and tries, I've written this, and it works :

$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("d:\pets.xlsx")
$WorkBook.XmlMaps("pets_Mappage").Export("d:\pets.xml")
$Excel.Quit()

answered Jun 27, 2021 at 22:11

Amine

176 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Export xlsx file to xml using PowerShell

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related