Exporting to XML from Excel

[Lon]

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.

[KaFu]

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

[ChrisL]

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

[Lon]

Not quite sure , 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

[Lon]

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.

[Lon]

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.

[KaFu]

Looks like a good solution , please post if it works.

[PsaltyDS]

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]

[Lon]

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]

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

[PsaltyDS]

Good find!

I Googled up at least one reference for it still being in Excel 2007, too: MSDN: Workbook.SaveAsXMLData Method