Excel open XML File

[HighlanderSword]

Hello,

I'm trying to open an xml file in excel using the code below

 

$oExcel = _Excel_Open()
$oExcel.Workbooks.OpenXML('Filename:="d:\test1.xml"', 'LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList')

 

I get the error message

$oExcel.Workbooks.OpenXML('Filename:="D:\test1.xml"', 'LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList')
$oExcel.Workbooks^ ERROR.

 

I have searched the forums and have not found any matched on how to open an xml file in Excel .

Below is the Macro code when I record the steps to manually do it in excel, how do I do this using the Excel UDF ?

Workbooks.OpenXML Filename:=  "d:\test1.xml", LoadOption  :=xlXmlLoadImportToList

[water]

AutoIt does not support named parameters. Needs to be something like this:

; XlXmlLoadOption Enumeration: https://msdn.microsoft.com/en-us/library/ff194637(v=office.14).aspx
Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table
$oExcel = _Excel_Open()
$oExcel.Workbooks.OpenXML("d:\test1.xml", "", $xlXmlLoadImportToList)

as described here: https://msdn.microsoft.com/en-us/library/ff838643(v=office.14).aspx

[HighlanderSword]

Gives error

 

"D:\Listing.au3" (12) : ==> The requested action with this object has failed.:
$oExcel.Workbooks.OpenXML("D:\ysb.xlm", "", $xlXmlLoadImportToList)
$oExcel.Workbooks^ ERROR

 

; XlXmlLoadOption Enumeration: https://msdn.microsoft.com/en-us/library/ff194637(v=office.14).aspx
Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table
$oExcel = _Excel_Open()
$oExcel.Workbooks.OpenXML("D:\ysb.xlm", "", $xlXmlLoadImportToList)

[water]

Maybe (can't test at the momen):

; XlXmlLoadOption Enumeration: https://msdn.microsoft.com/en-us/library/ff194637(v=office.14).aspx
Global $xlXmlLoadImportToList = 2 ; Places the contents of the XML data file in an XML table
Global $oExcel = _Excel_Open()
Global $oWorkbook = $oExcel.Workbooks.OpenXML("d:\test1.xml", Default, $xlXmlLoadImportToList)

 

[HighlanderSword]

Thank You !!!!!!! that worked !!!!!!!

[water]

Glad you Like this solution

[SkysLastChance]

Does it hurt to be that good water? You helped me out to thank you.

[SkysLastChance]

[double post]

Edited November 30, 2016 by SkysLastChance
double post

[water]

No, it doesn't hurt But it takes ages to become a genius
Understanding Microsofts COM model is not easy, but writing Office UDFs like I did (Word, Excel, Outlook, ExcelChart) helps a lot