Manipulating XML files with Excel VBA & Xpath

Click here for more reading

Reading from XML into Excel. Sample

This is a better example. Click here

There is no syntex difference in Access and Excell VBA for manupulating XML files.

For manupulating XML through VBA you have to have MSXML.4.0 or on your system. This comes in Internet Explorer 6, if you have this installed then you need not to worry about the dll for XML. NOw you simply open your VBA application editor (i.e. Microsoft Visual Basic 6.0 editor) Goto Tools --> References,
Select Microsoft XML, v3.0 Reference from the list.

Now you are ready to code for the Xml file in VBA.



Sub Macro1()
Dim iRow As Integer
Dim oxmlDoc As MSXML2.DOMDocument
Set oxmlDoc = New MSXML2.DOMDocument
Dim oXmlNode As MSXML2.IXMLDOMNode
Dim oXmlNode1 As MSXML2.IXMLDOMNode
Dim oXmlNodes As MSXML2.IXMLDOMNodeList

'Debug.Print xmlDoc.XML
'Debug.Print xmlDoc.Text

oxmlDoc.async = False
oxmlDoc.Load ("C:\WSOutput1.xml")

Set oXmlNodes = oxmlDoc.selectNodes("//XML_OUTPUT/ITEM/ELEMENTS[@TYPE='1']") ' reading attribute
Sheets("sheet1").Activate ' Sheets(strCostData).Activate

iRow = 4
For Each oXmlNode In oXmlNodes

'Debug.Print "ID :" & oXmlNode.childNodes.item(ConsumableFields.ID).Text
'Debug.Print "Name :" & oXmlNode.childNodes.item(ConsumableFields.Name).Text
Sheets("sheet1").Range("A" & Trim(str(iRow))).Value = oXmlNode.childNodes.item(0).Text

iRow = iRow + 1
Next oXmlNode
Set xmlDoc = Nothing

End Sub