How to load XML from a local .xml file using Msxml2.DOMDocument.6.0 and LoadXML (VBA)

VBA/Microsoft Access/Mircosoft Excel

The following code shows how to read the contents of an XML file into a string and then load it into a MSXML2 DOMDocument object. The DOM object is then negotiated using ChildNodes and SelectSingleNode to read the information in a meaningful way.

Function ReadXML(ByVal strFile As String)
Dim intFile As Integer 
Dim strXML As String 
Dim strOrderText As String 
Dim objDOM As Object 
Dim DOMOrder As Object 
Dim thisOrder As Object 
Dim thisOrder_ItemLine As Object
'Open file 
intFile = FreeFile 
Open strFile For Input As intFile
'Load XML into string strXML 
While Not EOF(intFile)    
    Line Input #intFile, strXML
Close intFile
'Load the XML into DOMDocument object
Set objDOM = CreateObject("Msxml2.DOMDocument.6.0″) 
objDOM.LoadXML strXML 
 'Traverse the XML
For Each DOMOrder In objDOM.ChildNodes
         For Each thisOrder In DOMOrder.ChildNodes 
                 With thisOrder             'Header Information 
                         MsgBox .SelectSingleNode("OrderHeader/PO_Number").Text
                         MsgBox .SelectSingleNode("OrderHeader/Order_Status").Text
                         MsgBox .SelectSingleNode("OrderHeader/Delivery_Required_Date").Text
                        'Delivery Information             
                        MsgBox .SelectSingleNode("Delivery/Name").Text
                        MsgBox .SelectSingleNode("Delivery/Address1").Text
                        MsgBox .SelectSingleNode("Delivery/Address2").Text
                        MsgBox .SelectSingleNode("Delivery/Town").Text
                        MsgBox .SelectSingleNode("Delivery/County").Text
                        MsgBox .SelectSingleNode("Delivery/Postcode").Text                        
                 ' For each Item line 
                 For Each thisOrder_ItemLine In thisOrder.SelectSingleNode("OrderDetails").ChildNodes
                      With thisOrder_ItemLine                     
                             MsgBox .SelectSingleNode("LineID").Text                     
                             MsgBox .SelectSingleNode("Product_Type").Text
                             MsgBox .SelectSingleNode("Range").Text  
                             MsgBox .SelectSingleNode("Colour").Text
                             MsgBox .SelectSingleNode("Qty").Text
                             MsgBox .SelectSingleNode("Size").Text 
                      End With
          End With     
Set objDOM = Nothing
End Function

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s