Programming Excel with VBA and .NET

Transform in Code

As mentioned previously, there are several ways to transform XML. Transforming XML from Visual Basic code uses the Microsoft XML object library to call the Microsoft XML Parser (msxml4.dll). The Microsoft XML object library also provides a means to create new XML files; navigate between nodes; copy, delete, and add nodes; and more.

To perform a transformation in code , follow these steps:

1. In Visual Basic, add a reference to the Microsoft XML object library. The Microsoft XML object library provides the DOMDocument object, which is used to load, transform, and save XML documents.

2. In code, create two instances of DOMDocument objects from the Microsoft XML object library.

3. Load the XML spreadsheet in the first DOMDocument object.

4. Load the XSLT file in the second DOMDocument object.

5. Use the TransformNode method of the first DOMDocument object to perform the transformation.

For example, the following code loads the TestStrip.xml XML spreadsheet and Strip.xslt transformation, processes the transformation, and saves the result:

Sub Strip( ) ' Requires reference to Microsoft XML Dim xdoc As New DOMDocument, xstyle As New DOMDocument Dim xml As String xdoc.Load (ThisWorkbook.path & "\TestStrip.xml") xstyle.Load (ThisWorkbook.path & "\Strip.xslt") xml = xdoc.transformNode(xstyle) SaveFile xml, "Out.xml" End Sub   Sub SaveFile(content As String, fileName As String) ' Requires reference to Microsoft Scripting Runtime Dim fso As New FileSystemObject, strm As TextStream fileName = ThisWorkbook.path ...