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.
Since the ImportXML and 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
with this:
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?
Who would have thought
This weblog reminded me to try out Access' import XML capabilities. I only used the GUI, but was quite impressed. At least with my simple example, it really made it easy for me to review/query some simple data structures. Now if only I could 'link' a table instead...
Thanks.
ExportXML with unbound forms
Hi,
I have a problem with "ExportXML". Although it does export tables, it doesn't work with unbound forms.
I am trying to export some form from a database but I get an error that Access can't export *unbound* forms and reports. (I can successfully export tables, but not forms.)
Do you know why this happens?
Thank you!
ExportXML with unbound forms
I've never had much luck with exporting forms of any kind. My advice, clunky though it may sound, would be to create a routine that saves the form data to a table temporarily, exports the table, and then (if necessary) obliterates the table or its contents.
Alternately, you could write a routine that walked the form fields and output XML directly, without relying on the built-in export functionality. You could either write to a text file directly (risky) or use the MSXML object to create and fill an XML document with your data.
ExportXML with unbound forms
Thanks! The initial idea was to walk through the XML, but I thought ExportXML should work with forms since it includes a specific type ("Access.AcExportXMLObjectType.acExportForm")
Problem with the ExportXML Transform
Hi.. :)
I love this code that you wrote, it is exactly what I need due to the fact that I am exporting out linked images and I need to have an XSLT template built so that the images automatically are viewable in the HTML.
I have one little problem. Everything is exporting just fine and it goes through without any errors but when I go to the .html file that it created, the .html file is empty. For some reason it's not passing any data into it.
Here is the code that I currently have:
Option Compare Database
Option Explicit Private
Sub Transform(sourceFile, stylesheetFile, resultFile)
Dim source As New MSXML.DOMDocument30
Dim stylesheet As New MSXML.DOMDocument30
Dim result As New MSXML.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
Private Sub ExportReportButton_Click()
Dim strFilePath, strXML, strHTM, strXMLLoc, strHTMLoc, strReportID, strTemplate As String
strFilePath = "S:\Test\MyTest\"
strXML = ".xml"
strHTM = ".htm"
strReportID = Me.ReportID
strHTMLoc = strFilePath + strReportID + strHTM
strXMLLoc = strFilePath + strReportID + strXML
strTemplate = "S:\Test\MyTest\Template.xsl"
Application.ExportXML acExportQuery, "ExportXMLQ", strXMLLoc
Transform strXMLLoc, strTemplate, strHTMLoc
End Sub
I would really appreciate it if you could tell me what I am doing wrong. Thank you for your time and thank you for such amazing code. I've pulled a lot of ideas from the stuff you have done.
Sincerely,
Brandon