This is a variation to a previous post (see where I misspelled transform as tramsform, lol).
This shows how to read the stylesheet from a textbox on a worksheet. I did this so I could distribute the application as one file without the users having to do any zipping, unzipping, etc.
I'm not sure how big of a stylesheet can be embedded this way but mine was a bit over 25 kB.
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() As DOMDocument60 'This function is handy for creating the DOM objects. 'It enables stylesheets to include (reference) other stylesheets. '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() 'Call this routine to transform and open the XML file. Dim xmlDOCin As DOMDocument60, xslDoc As DOMDocument60, xmlDOCout As DOMDocument60 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 txtBox As Shape 'Worksheet text box Dim strXSL As String 'XSL stylesheet instead of loading from a text file Dim strXML As String 'XML to write to a temp file to open with the Workbooks.OpenXML statement Dim myPath As String 'Path to this workbook myPath = ThisWorkbook.Path InputXML = myPath & "\OM_status_Alladdin.xml" OutputXML = myPath & "\Temp01.xml" Set txtBox = ActiveSheet.Shapes("Text Box 1") strXSL = txtBox.TextFrame.Characters.Text Set FSO = New FileSystemObject Set xmlDOCin = CreateDOM xmlDOCin.Load InputXML 'Load in the XML file Set xslDoc = CreateDOM xslDoc.LoadXML strXSL 'Load the stylesheet (string instead of a file) 'Transform the xml and output as string instead of a DOM 'because my stylesheet outputs HTML. 'Note also that any stylesheets specified with processing instructions (PIs) 'are ignored. strXML = xmlDOCin.transformNode(xslDoc) 'If you use the transformNodeToObject method, you get an error if 'the output is not (well-formed ?) xml. 'Write the transformed xml to a temporary file. Set f1 = FSO.CreateTextFile(Filename:=OutputXML, overwrite:=True) f1.WriteLine (strXML) f1.Close 'Open the temp file (xml) Workbooks.OpenXML Filename:=OutputXML End Sub