how to extract XML info with VBA?

[Fred Smif]

Can I use Excel VBA to extract information from an XML file _without_
opening the XML file as a worksheet in Excel? If so...can y'all point me
to some tutorials on the web or to a good book?

I have only Excel 2002 available.

Thank you.

 

[Fred Smif]

Can I use Excel VBA to extract information from an XML file _without_

opening the XML file as a worksheet in Excel? If so...can y'all point me
to some tutorials on the web or to a good book?

I have been using Excel and macros and VBA for many years, but am new to
this XML stuff.

What I have is an XML file generated by another program, that file contains
a wealth of information to be used in a manufacturing plant. I guess I am
expecting to be able to use some sort of functions to extract my desired
information from the "data fields" in the XML file. Maybe I have a totally
wrong impression of what this stuff does.

thanks. Fred.

 

[Guest]

Fred,

You need to establish a reference to the Microsoft MSXML 3.0 or 4.0 Library
and code against it. It has an elaborate object model but you and use it to
do all the programming you need.

Alok Joshi

 

[Harald Staff]

Hi Fred

Here is a simple code only demo that loads the xml and parse the nodes. Set
a reference to Microsoft XML 3 or similar in the Tools > References menu.

This demo has no error handling. E.g. "Load" may err if < is not the first
character. Ok:

Sub test()
Dim XMLdok As DOMDocument
Dim XMLRootNode As IXMLDOMNode

Set XMLdok = New DOMDocument
XMLdok.async = False
XMLdok.resolveExternals = False

XMLdok.Load ("C:\Temp\test.xml")

Set XMLRootNode = XMLdok.childNodes(0)
Call TraverseTree(XMLRootNode)

Set XMLRootNode = Nothing
Set XMLdok = Nothing
End Sub

Sub TraverseTree(objNode As IXMLDOMNode)
Dim ThisNode As IXMLDOMNode

Set ThisNode = objNode

Do
On Error Resume Next

MsgBox ThisNode.nodeName & vbNewLine & _
ThisNode.XML & vbNewLine & _
ThisNode.baseName & vbNewLine & _
ThisNode.nodeValue & vbNewLine & _
ThisNode.Text

If Not ThisNode.childNodes(0) Is Nothing Then
Call TraverseTree(ThisNode.childNodes(0))
End If

Set ThisNode = ThisNode.nextSibling
On Error GoTo 0
Loop While Not ThisNode Is Nothing
End Sub

HTH. Best wishes Harald