Use Access VBA to import XML and Stylesheet to local drive (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 16:04
Joined
Oct 22, 2009
Messages
2,803
Got this to work in Access 2010. Took a while to solve my specific problem, an XML file with a Stylesheet.
This web site has many different XML examples.
Hopefully, some of you can modify this for other XML examples and to save into a Access Table. There does not appear to be a lot of solutions to be found.


Code:
Public Sub Transform(sourceFile, stylesheetFile, resultFile)
      ' For Access 2010 VBA  ----> Menu Tools Reference   choose  Microsoft XML, 6.0
      ' Example of using Access VBA to grab an XML file from the web (or file location)
      ' that has 2nd site with a Style Sheet.
      Dim source As New MSXML2.DOMDocument30
      Dim stylesheet As New MSXML2.DOMDocument30
      Dim result As New MSXML2.DOMDocument30
      ' Load data.
10    source.async = False
20    source.Load sourceFile
      ' Load style sheet.
30    stylesheet.async = False
40    stylesheet.Load stylesheetFile
50    If (source.parseError.ErrorCode <> 0) Then
60       MsgBox ("Error loading source document: " & source.parseError.Reason)
70       Else
80    If (stylesheet.parseError.ErrorCode <> 0) Then
90          MsgBox ("Error loading stylesheet document: " & stylesheet.parseError.Reason)
100      Else
            ' Do the transform.
110         source.transformNodeToObject stylesheet, result
120         result.Save resultFile
130   End If
140   End If
150   Debug.Print "Error is " & Err.Number
' To run the above - This is a web site that has simple XML sites
' The example is a XML file with an associated XML Style Sheet
' To run the above code - and save the result in C:\MyFolder
' Transform "http://www.w3schools.com/xml/simplexsl.xml", _
    "http://www.w3schools.com/xml/simple.xsl", _
    "C:\myfolder\AtemptempImport.xml"
' Rx_


End Sub

Lets say you have a XML document and just want to create a new table that matches the simple format:

Code:
Public Sub ImportXMLFile()
         ' Purpose: Imports the data and structure of an XML file.
         ' Go create a new blank Access DB in C:\MyFolder\XMLtestDB1.accdb   with the name XMLtestDB1.accdb
         ' this XML structure does not have a style sheet - just import it into a new Access table
         Const STRUCTURE_AND_DATA = 1
        Dim appAccess As Object
10       Application.ImportXML DataSource:="http://www.w3schools.com/xml/simple.xml", _
             ImportOptions:=STRUCTURE_AND_DATA
20    Set appAccess = CreateObject("Access.Application")
30    appAccess.OpenCurrentDatabase ("C:\MyFolder\XMLtestDB1.accdb")
40    appAccess.ImportXML "http://www.w3schools.com/xml/simple.xml"
50    appAccess.CloseCurrentDatabase
60    appAccess.Quit acExit
70    Set appAccess = Nothing
        ' now go open up the Access DB and look at your new table - Pick something tasty on the Lunch Menu
        'Rx_    Prescribing Solutions
        ' with out a stylesheet - just raw data
End Sub


' I didn't have to stay after work to post this - if you like it click the Thanks button


Please feel free to modify this and add your own examples.
Please list the version of Access it was tested on
The version of the XML Reference.
And, the type of XML document tested.
 

Users who are viewing this thread

Top Bottom