VBScript to convert XML to Excel

[fboehlandt]

Hello everyone

I 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-US

The 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 Object
Set 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 String
sHTML = oXML.transformNode(oXSL)

'Save the results to an HTML file.
Open "c:\customers.htm" For Output As #1 '!fails here
Print #1, sHTML
Close #1

'Automate Excel to open the HTML file.
Dim oApp As Excel.Application
Set oApp = CreateObject("excel.application")
oApp.Visible = True
oApp.Workbooks.Open "c:\customers.htm"
End Sub

Converted 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

 

[PHV]

Have a look at FileSystemObject (aka FSo).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fboehlandt]

This is how far I can get from your input: I can create the html-file and write something to it. However, I don't know how to write the contents from the XML file to the newly created HTML using the style sheet.

Sub XMLtoXLS()

Const ForWriting = 2
Const TristateTrue = -1
Dim fso, MyFile, FileName

'Load the XML and the XSL (the stylesheet).
Dim oXML, oXSL
Set oXML = CreateObject("MSXML.DOMDocument")
Set oXSL = CreateObject("MSXML.DOMDocument")
oXML.Load "c:\customer.xml"
oXSL.Load "c:\customer.xsl"
       
'Transform the XML using the stylesheet.
Dim sHTML
sHTML = oXML.transformNode(oXSL)

Set fso = CreateObject("Scripting.FileSystemObject")

' Open the file for output.
FileName = "c:\customer.htm"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True, TristateTrue)

'!THIS TO BE REPLACED
' Write to the file.
MyFile.WriteLine "first line"
MyFile.WriteLine "second line"
MyFile.Close

End Sub

I ommitted the Excel-transformation since that is (hopefully) quite straight-forward

Cheers

 

[PHV]

I'd use simply this:
MyFile.WriteLine sHTML

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fboehlandt]

Well I'll be...! That's easy enough indeed. I did say I didn't know VBScript though. Thanks for your input, this worked for me

 

[fboehlandt]

For completion sake, the entire code:

Sub XMLtoXLS()

Dim oXML, oXSL, filestr, Targetfolder
Dim sHTML, fso, FileName, MyFile
Dim xlApp, xlWbk

Const ForWriting = 2
Const TristateTrue = -1
Const xlNormal = 51

'Load the XML and the XSL (the stylesheet).
Set oXML = CreateObject("MSXML.DOMDocument")
Set oXSL = CreateObject("MSXML.DOMDocument")
Set fs = CreateObject("Scripting.FileSystemObject")

filestr="D:\a directory\XMLsource.xml" 
TargetFolder="D:\a directory\xls"
oXML.Load filestr
oXSL.Load "D:\a directory\XSLsource.xsl"
       
'Transform the XML using the stylesheet.
sHTML = oXML.transformNode(oXSL)

Set fso = CreateObject("Scripting.FileSystemObject")

' Open the file for output
BaseName = fs.getbasename(filestr)
FileName = TargetFolder & "\" & BaseName & ".htm"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True, TristateTrue)

' Write to the file.
MyFile.WriteLine sHTML
MyFile.Close

'Automate Excel to open the HTML file.
Set xlApp = CreateObject("excel.application")
xlApp.Visible = False
Set xlWbk = xlApp.Workbooks.Open(Filename)
FullTargetPath = TargetFolder & "\" & BaseName & ".xlsx"
xlWbk.SaveAs FullTargetPath, xlNormal
xlWbk.close

Set xlWkb = Nothing
Set xlApp = Nothing
Set fs = Nothing

End Sub