Jump to content

Convert XML to Excel


fboehlandt

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 comment
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
×
×
  • Create New...