I mentioned in an earlier weblog that Access supported XSLT transformations on import and export through the GUI, but not through VBA. Here, extracted from something I wrote for Access Hacks, is a solution, if an imperfect one.
ExportXML routines don’t offer an XSLT transformation along the way, you have to take an extra step, reparsing an XML file, transforming it, and saving the transformation out to disk. Here’s what the key
Transform function looks like in VBA:
Private Sub Transform(sourceFile, stylesheetFile, resultFile) Dim source As New MSXML2.DOMDocument30 Dim stylesheet As New MSXML2.DOMDocument30 Dim result As New MSXML2.DOMDocument30 ' Load data. source.async = False source.Load sourceFile ' Load style sheet. stylesheet.async = False stylesheet.Load stylesheetFile If (source.parseError.errorCode <> 0) Then MsgBox ("Error loading source document: " & source.parseError.reason) Else If (stylesheet.parseError.errorCode <> 0) Then MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.reason) Else ' Do the transform. source.transformNodeToObject stylesheet, result result.Save resultFile End If End If End Sub
To use it on an import, you could replace this:
Application.ImportXML "http://simonstl.com/ora/updateBook.xml", acAppendData
Transform "http://simonstl.com/ora/updateBook.xml", _ "C:xsltattsToElem.xsl", _ "C:temptempImport.xml" Application.ImportXML "C:temptempImport.xml", acAppendData
On an export, you add a transformation step after the export:
Application.ExportXML acExportTable, "books", "C:temptempExport.xml" Transform "C:temptempExport.xml", _ "C:xsltbooksToHTML.xsl", _ "C:exportexportedBooks.html"
Maybe Microsoft will get around to adding this functionality to future versions of Access so we can drop the overhead of saving to disk and reading it right back out, but for now, this opens up a lot more possibilities for using XML with Access. It also works in Word and Excel, but isn’t as necessary there.
(And there’s lots, lots more in Access Hacks that’s far cooler than this little workaround.)
How many wheels have you reinvented in VBA?