I have seen a lot about this and I though I would share the fruits of my effort.
I used to process XML with a XSLT style sheet by using the following command:
Workbooks.OpenXML Filename:="Path_to_XML_file", Stylesheets:=Array(1)
This used to work until I had Excel upgraded from Excel 2003 to Excel 2010. However, Excel 2010 by default does not allow the stylesheet to contain scripting or references to additional stylesheets. After much searching on the web I found some documentation by Microsoft (http://msdn.microsoft.com/en-u…op/ms765520(v=vs.85).aspx) and (http://msdn.microsoft.com/en-u…op/ms766487(v=vs.85).aspx)
Then I came up with the following:
Option Explicit
'Note: From the Tools -> References menu the following references need to be selected
' Microsoft XML, v6.0 (other versions might also work)
' Microsoft Scripting Runtime
Private Function CreateDOM()
'This function is handy for creating the DOM objects
'It enables stylesheets to include (reference) other stylesheet
'It enables scripts to be used in the stylesheets
Dim dom As DOMDocument60
Set dom = New DOMDocument60
dom.async = False
dom.validateOnParse = False
dom.resolveExternals = True
dom.setProperty "AllowXsltScript", True
Set CreateDOM = dom
End Function
Private Sub TransformXML()
Dim doc As DOMDocument60, xsl As DOMDocument60, out As DOMDocument60, str
Dim FSO As FileSystemObject, f1
Dim InputXML As String 'Path to XML file to transform
Dim OutputXML As String 'Path to XML file to output after transforming
Dim StyleSheet As String 'Path to XSL stylesheet
InputXML = "J:\Excel\XSL_hacks\FGI - translation status report.xml"
OutputXML = "J:\Excel\XSL_hacks\FGI-XSL_test_output4.xml"
StyleSheet = "J:\Excel\XSL_hacks\FGI - translation status report.xsl"
Set FSO = New FileSystemObject
Set doc = CreateDOM
doc.Load InputXML
Set xsl = CreateDOM
xsl.Load StyleSheet
str = doc.transformNode(xsl)
'MsgBox "doc.transformNode: " & vbCrLf & str
Set f1 = FSO.CreateTextFile(Filename:=OutputXML, overwrite:=True)
f1.WriteLine (str)
f1.Close
Workbooks.OpenXML Filename:=OutputXML
'The following does not work for stylesheets that output HTML.
'Set out = CreateDOM
'doc.transformNodeToObject xsl, out
'MsgBox "doc.transformNodeToObject:" & vbNewLine & out.XML
'out.Save "J:\Excel\XSL_hacks\test_output.xml"
End Subsorry, I misspelled the word "transform" in the title but the "Edit Post" did not let me change it.