Workbooks.OpenXML VBA Method when running Excel as a PowerShell ComObject. How to specify the LoadOption parameter?

[PinPinPoola]

I am trying to open an XML file with Excel running as a PowerShell ComObject, but I cannot work out how to correctly specify the LoadOption parameter?

I am using the Workbooks.OpenXML method as described here: Workbooks.OpenXML method (Excel)

I don't have a Stylesheet, so only want to supply the FileName and LoadOption parameters.

When I open the XML file via the Excel UI, I need to select the 'As an XML table' to get result I want.

And the Worksheet looks like this:

This is the VBA that works natively in Excel (Recorded as a Macro):

Sub Macro1()
'
' Macro1 Macro
'

'
    ChDir "D:\Temp\TempVisioStencils\HPE"
    Workbooks.OpenXML Filename:= _
        "D:\Temp\TempVisioStencils\HPE\HPE-ProLiant-DL-Logical_masters.xml", _
        LoadOption:=xlXmlLoadImportToList
    Windows("Book1").Activate
End Sub

Here is the PowerShell I am testing with:

Remove-Variable -Name * -ErrorAction SilentlyContinue

$xmlFile = "D:\Temp\TempVisioStencils\HPE\HPE-ProLiant-DL-Logical_masters.xml"
$xlsxFile = "D:\Temp\TempVisioStencils\HPE\HPE-ProLiant-DL-Logical.xlsx"

$xlsSpreadsheet = 51
$openXMLFilename = "Filename:=`"$($xmlFile)`""
$openXMLLoadOption = "LoadOption:=xlXmlLoadImportToList"
$openXMLParams = "$openXMLFilename, $openXMLLoadOption"

$Excel = New-Object -ComObject Excel.Application

$WorkBook = $Excel.Workbooks.OpenXML($openXMLParams)
$WorkBook.SaveAs($xlsxFile, $xlsSpreadsheet)

$Excel.Quit()

I get an error popup and when I select OK, the script exits and no .xlsx file is created.

If I specify the XML file name as the only OpenXML parameter:

$WorkBook = $Excel.Workbooks.OpenXML($xmlFile)

The .xlsx file is created, but not in the way I want:

I am stuck and would really appreciate any help or suggestions here.

Many Thanks
Pin

 

[PinPinPoola]

I dropped the HPE-ProLiant-DL-Logical_masters.xml file in to a PasteBin here https://pastebin.com/raw/C35LFS3r

 

[PinPinPoola]

I dropped the HPE-ProLiant-DL-Logical_masters.xml file in to a PasteBin here https://pastebin.com/raw/C35LFS3r

PasteBin moderated the XML - "Pastebin’s SMART filters have detected potentially offensive or questionable content in your paste." - #notsosmartfilters

I have now put it on Ghostbin

 

[PinPinPoola]

Solved. ??

I feel a bit daft that I did not try this before posting here.

$WorkBook = $Excel.Workbooks.OpenXML(FileName,Stylesheets,LoadOption)

Where:

FileName = Full path to the XML file
Stylesheets = A value greater than 0.
LoadOption = One of the values from XlXmlLoadOption enumeration (Excel)

Name​	Value​	Description​
xlXmlLoadImportToList	2	Places the contents of the XML data file in an XML table.
xlXmlLoadMapXml	3	Displays the schema of the XML data file in the XML Structure task pane.
xlXmlLoadOpenXml	1	Opens the XML data file. The contents of the file will be flattened.
xlXmlLoadPromptUser	0	Prompts the user to choose how to open the file. DON'T USE THIS ONE AS EXCEL HANGS WAITING FOR INPUT

So in my case:

$WorkBook = $Excel.Workbooks.OpenXML($xmlFile,1,2)