XML, XSL Transformation in VBA

MS- office2010 does not support document function of xsl stylesheet, so you can't open XML file in Excel.
Microsoft has disabled document function access in stylesheet because of dom security issue.
you need to use referance option from visual basic editor of Excel and select MSXML6.0 for running this macro.
It's step by step process:-

• Transform xml using xsl.
• Write transformed data[xhtml or html] in temp file.
• Open this temp file in new workbook.

Sub VXmlOpen(xmlname As String, xslname As String, strfolder As String) Dim xmlfile As New MSXML2.DOMDocument

' below line is reason to write this macro.

xmlfile.setProperty "AllowDocumentFunction", True xmlfile.async = False xmlfile.Load xmlname Dim xslfile As New MSXML2.DOMDocument xslfile.setProperty "AllowDocumentFunction", True xslfile.async = False xslfile.Load xslname strhtml = xmlfile.transformNode(xslfile) Dim txml As String txml = strfolder + CStr(lcount) + "TempXml.xls" Set xmlfile = Nothing Set xslfile = Nothing On Error GoTo errLbl: Open txml For Output As #1 Print #1, strhtml Close #1 Application.Workbooks.Open txml lcount = lcount + 1 Exit Sub errLbl: MsgBox Err.Description Close #1 End Sub