Lon Posted June 25, 2010 Share Posted June 25, 2010 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. Link to comment Share on other sites More sharing options...
KaFu Posted June 25, 2010 Share Posted June 25, 2010 Not quite sure , but doesn't Excel support XML export by default? Take a look at help-file for the _ExcelBookSaveAs() Standard UDF. OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
ChrisL Posted June 25, 2010 Share Posted June 25, 2010 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 [u]Scripts[/u]Minimize gui to systray _ Fail safe source recoveryMsgbox UDF _ _procwatch() Stop your app from being closedLicensed/Trial software system _ Buffering Hotkeys_SQL.au3 ADODB.Connection _ Search 2d Arrays_SplashTextWithGraphicOn() _ Adjust Screen GammaTransparent Controls _ Eventlogs without the crap_GuiCtrlCreateFlash() _ Simple Interscript communication[u]Websites[/u]Curious Campers VW Hightops Lambert Plant Hire Link to comment Share on other sites More sharing options...
Lon Posted June 25, 2010 Author Share Posted June 25, 2010 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 Link to comment Share on other sites More sharing options...
Lon Posted June 25, 2010 Author Share Posted June 25, 2010 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=0I 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. Link to comment Share on other sites More sharing options...
Lon Posted June 25, 2010 Author Share Posted June 25, 2010 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. Link to comment Share on other sites More sharing options...
KaFu Posted June 25, 2010 Share Posted June 25, 2010 Looks like a good solution , please post if it works. OS: Win10-22H2 - 64bit - German, AutoIt Version: 3.3.16.1, AutoIt Editor: SciTE, Website: https://funk.eu AMT - Auto-Movie-Thumbnailer (2022-Nov-26) BIC - Batch-Image-Cropper (2023-Apr-01) COP - Color Picker (2009-May-21) DCS - Dynamic Cursor Selector (2024-Feb-16) HMW - Hide my Windows (2018-Sep-16) HRC - HotKey Resolution Changer (2012-May-16) ICU - Icon Configuration Utility (2018-Sep-16) SMF - Search my Files (2023-Jun-03) - THE file info and duplicates search tool SSD - Set Sound Device (2017-Sep-16) Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 25, 2010 Share Posted June 25, 2010 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] 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 Link to comment Share on other sites More sharing options...
Lon Posted June 25, 2010 Author Share Posted June 25, 2010 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 Link to comment Share on other sites More sharing options...
PsaltyDS Posted June 27, 2010 Share Posted June 27, 2010 Good find! I Googled up at least one reference for it still being in Excel 2007, too: MSDN: Workbook.SaveAsXMLData Method 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now