Jump to content
HighlanderSword

Excel open XML File

Recommended Posts

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

Share this post


Link to post
Share on other sites
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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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)

 

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
HighlanderSword

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

Share this post


Link to post
Share on other sites
water

Glad you Like this solution ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
SkysLastChance

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


Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites
SkysLastChance

[double post]

Edited by SkysLastChance
double post

Life's simple. You make choices and you don't look back.

Share this post


Link to post
Share on other sites
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 :D

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×