Jump to content

Convert XML to Excel

Recommended Posts

Hello everyoneI need to transform an XML file to Excel. The style sheet (XSL) should be applied on import. There appears to be an acknowledged bug:http://support.microsoft.com/kb/307230/en-USThe workaroudn involves converting XML to HTML first, then to Excel-format. The script is in VBA:Sub Macro3()'Load the XML and the XSL (the stylesheet).Dim oXML As Object, oXSL As ObjectSet oXML = CreateObject("MSXML.DOMDocument")Set oXSL = CreateObject("MSXML.DOMDocument")oXML.Load "c:customers.xml"oXSL.Load "c:customers.xsl"'Transform the XML using the stylesheet.Dim sHTML As StringsHTML = oXML.transformNode(oXSL)'Save the results to an HTML file.Open "c:customers.htm" For Output As #1 '!fails herePrint #1, sHTMLClose #1'Automate Excel to open the HTML file.Dim oApp As Excel.ApplicationSet oApp = CreateObject("excel.application")oApp.Visible = TrueoApp.Workbooks.Open "c:customers.htm"End SubConverted to VBScript the code fails at the line indicated above, presumably because VBScript does not know the method. I'm not proficient in VBScript so any help is appreciated
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...