Sign in to follow this  
Followers 0
Lon

Exporting to XML from Excel

10 posts in this topic

I am using an Excel spreadsheet with function calls that grab NetVoyant data (a network utilization poller app), exporting the results to XML. The XML is consumed by a Flash app built by Xcelsius. If you aren't lost yet, I would like to automate the spreadsheet exportation to XML, but cannot find any AutoIt functions that would allow me to do this. I don't want to stoop to sending mouse movements and clicking.

Any ideas?

Thanks

Lon.

Share this post


Link to post
Share on other sites



Not quite sure :mellow:, but doesn't Excel support XML export by default? Take a look at help-file for the _ExcelBookSaveAs() Standard UDF.

Share this post


Link to post
Share on other sites

Not quite sure :mellow:, but doesn't Excel support XML export by default? Take a look at help-file for the _ExcelBookSaveAs() Standard UDF.

I saw that, but the help on the _ExcelBookSaveAs didn't include xml as an option. It only listed xls|csv|txt|template|html. How can I distinguish between an XML_Data versus XML Spreadsheet save? I need the XML_Data Save.

-Lon

Share this post


Link to post
Share on other sites

You might have to use the _Excel functions (help file) combined with XML DOM Wrapper http://www.autoitscript.com/forum/index.php?showtopic=19848&hl=_XML&st=0

I could maybe use the XML_DOM Wrapper, but was hoping to have Excel do the formatting of the XML_Data output rather than me. I did see that the author LocoDarwin was at one time working on a non-common Excel UDF. I don't see where this was ever completed.

Share this post


Link to post
Share on other sites

Here is what I am going to do. I found that there is an MS Excel Constant called xlXMLData with a value of 47. I am going to add this to the global constant list in the include file of Excel.au3 (or perhaps make my own version altogether) and appropriately modify the _ExcelBookSaveAs function.

Share this post


Link to post
Share on other sites

Handy list of MS Office VBA constants.

There is an xlXMLSpreadsheet = 46 format from Excel 2002/XP and up.

Then there is xlXMLData = 47. Problem is, xlXMLData may or may not exist in your version. According to that chart and Microsoft, it's only in Excel 2003.

Note xlXMLData is listed here: Microsoft Excel Constants [Excel 2003 VBA Language Reference]

But not here: XlFileFormat Enumeration [Excel 2007]

:mellow:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

Handy list of MS Office VBA constants.

There is an xlXMLSpreadsheet = 46 format from Excel 2002/XP and up.

Then there is xlXMLData = 47. Problem is, xlXMLData may or may not exist in your version. According to that chart and Microsoft, it's only in Excel 2003.

Note xlXMLData is listed here: Microsoft Excel Constants [Excel 2003 VBA Language Reference]

But not here: XlFileFormat Enumeration [Excel 2007]

:mellow:

I was able to find another SaveAs method. This one is called SaveAsXMLData (imagine that). The code is below:

$oExcel.ActiveWorkBook.SaveAsXMLData("c:\DataTest.xml", $oExcel.ActiveWorkbook.XmlMaps(1))

Refer to an msdn document section called Creating XML Documents. I don't know if this will still work in Excel 2007. But this will work for me now :P

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
Sign in to follow this  
Followers 0