MS Access embed xslt into vba

Question

I have two xslt files loaded into my ms access vba. This access database will be passed around so to minimize the xslt files being lost I was wondering if it is possible write the xsl into the vba?

Here is my vba code:

    Private Sub btnImport_Click()
    Dim strFile As String, strPath As String
    ' REFERENCE MS XML, v6.0
    Dim xmlDoc As New MSXML2.DOMDocument60, xslDoc As New MSXML2.DOMDocument60
    Dim newDoc As New MSXML2.DOMDocument60

    strPath = "C:\Users\1122335\Desktop\iavms\IAVM XML dump on 2017-01-20\"
    strFile = Dir(strPath & "*.xml")

    ' LOAD XSL ONLY ONCE
    xslDoc.Load "C:\Users\1122335\Desktop\secondLoad.xsl"

    While strFile <> ""
        ' REINITIALIZE DOM OBJECTS
        Set xmlDoc = New MSXML2.DOMDocument60
        Set newDoc = New MSXML2.DOMDocument60

        ' LOAD XML SOURCE
        xmlDoc.Load strPath & strFile

        ' TRANSFORM SOURCE
        xmlDoc.transformNodeToObject xslDoc, newDoc
        newDoc.Save "C:\Users\1122335\Desktop\temp.xml"

        ' APPEND TO TABLES
        On Error Resume Next
        Application.ImportXML "C:\Users\1122335\Desktop\temp.xml", acAppendData
        strFile = Dir()
    Wend

   ' RELEASE DOM OBJECTS
   Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    strPath = "C:\Users\1122335\Desktop\iavms\IAVM XML dump on 2017-01-20\"
    strFile = Dir(strPath & "*.xml")

        ' LOAD XSL ONLY ONCE
    xslDoc.Load "C:\Users\1122335\Desktop\finally.xsl"

    While strFile <> ""
        ' REINITIALIZE DOM OBJECTS
        Set xmlDoc = New MSXML2.DOMDocument60
        Set newDoc = New MSXML2.DOMDocument60

        ' LOAD XML SOURCE
        xmlDoc.Load strPath & strFile

        ' TRANSFORM SOURCE
        xmlDoc.transformNodeToObject xslDoc, newDoc
        newDoc.Save "C:\Users\1122335\Desktop\temp.xml"

        ' APPEND TO TABLES
        On Error Resume Next
        Application.ImportXML "C:\Users\1122335\Desktop\temp.xml", acAppendData
        strFile = Dir()
    Wend

   ' RELEASE DOM OBJECTS
    Set xmlDoc = Nothing: Set xslDoc = Nothing: Set newDoc = Nothing

End Sub

You want to embed the file binary into VBA as a base-64 string? Why not just embed them in an Access table as `Attachment`s? — ThunderFrame, Feb 16 '17 at 19:08
Can't you just store the raw xlst code in a table in the database? — Comintern, Feb 16 '17 at 19:15
Create a new table, add a field, and give it a type of `Attachment`, and add an ID field as Primary Key while you're there. Then view the table in Datasheet mode, and double click in the Attachment field, and follow the wizard to attach a file. Then use VBA to open that table and read the attachment. — ThunderFrame, Feb 16 '17 at 19:16
That's what @ThunderFrame suggested. What would be the vba code to call that table? — 1122335, Feb 16 '17 at 19:19
Brainfart: I read "XLST" as "XLTX", and thought you were embedding an Excel template. If it's just XLST, then as @comintern says, a memo field will work just fine. — ThunderFrame, Feb 16 '17 at 19:45
No worries I did it both ways;) so would the code just be `xsldoc [table].[field] where id = 1`? — 1122335, Feb 16 '17 at 19:47
When I run the code now I get an error '-2147467259 (800004005)' saying the stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed xml document. I get this error at `xmlDoc.transformNodeToObject xslDoc, newDoc` any suggestions? — 1122335, Feb 16 '17 at 20:14
re: retrieving attachment files - see [this answer](http://stackoverflow.com/a/25867795/2144390) — Gord Thompson, Feb 16 '17 at 21:10
Did you test it with [an xml validator](http://www.utilities-online.info/xsltransformation/)? — Comintern, Feb 16 '17 at 22:13
@GordThompson I'm new to VBA and XSL, would you mind explaining how that answer could be used for my problem? — 1122335, Feb 16 '17 at 22:14
@Comintern it looks like when vba calls the field it adds a double quote to every double quote. Instead of ` ` vba reads ` ` throughout the entire xsl file — 1122335, Feb 16 '17 at 22:22
@Comintern Nevermind, I don't know what that was about. But both the XML and XSL ran through the validator. — 1122335, Feb 16 '17 at 22:31
@1122335 Works in both ADO and DAO for me. `Sub test(): Dim daoRST As DAO.Recordset: Set daoRST = CurrentDb.OpenRecordset("Table1"): Debug.Print daoRST.Fields("XML").Value: Dim adoRST As ADODB.Recordset: Set adoRST = CurrentProject.Connection.Execute("Table1"): Debug.Print adoRST.Fields("XML").Value: End Sub` — ThunderFrame, Feb 16 '17 at 22:32
@ThunderFrame and @Comintern I'm still getting this error `stylesheet does not contain a document element. The stylesheet may be empty, or it may not be a well-formed xml document` the XML passes validator. Any suggestions? — 1122335, Feb 17 '17 at 20:35

score 0 · Answer 1 · edited Sep 12 '19 at 13:19

0

My answer is probably coming too late but to solve this issue, just change the method "load" to "loadXML":

Change from: xmlDoc.Load strPath & strFile

to: xslDoc.loadXML strPath & strFile

edited Sep 12 '19 at 13:19

Jelle Hoekstra

662
3
16

answered Sep 12 '19 at 12:57

Az1807

23
6

MS Access embed xslt into vba

1 Answers1