fboehlandt Posted May 19, 2014 Share Posted May 19, 2014 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 More sharing options...
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
Already have an account? Sign in here.Sign In Now