xml parser in Excel

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Hi NG

I have some meta-data stored as text in cells in a worksheet e.g.
<info><type>base</type><version>test</version></info>

I want to start an XML parser in VBA and parse the above line to my program.

Does any of you have any comments on how that is accomplished in Excel?

Regards
Mark
 
Replying to my own post. I am using Office XP if thats any help. Found bits
and pieces of tutorial stuff on msdn about xml but it was only for Office
2003

Basically i just want to parse a string through an XML parser in VBA. That
must be possible! XML been round for years!

Mark
 
Again replying to my own mail ;-)

The deal is this. On Microsofts site you will find something called MSXML it
is microsofts XML parser. It works in javascript (via activeX) and in C++
and in VB. It also works in VBA.

Here is how you make it work in VBA

From the Tools menu in the Visual Basic Editor, select References and find
MSXML
Thats it! Pure and simple!!

Now I just need to make it work on strings! Be right back...

Mark
 
For the last time replying to my on post ;-)

To make XML parsing work with strings from within VBA just do this:

Dim source As MSXML2.DOMDocument40
Set source = New MSXML2.DOMDocument40

okay now "source" is initialized and we can start using it!!
Let's read a string

source.loadXML("<test>Hello World</test>")

It works!
Hope you guyz in the NG could use this information...

Regards
Mark
 
Found a better example

Sub Test()

Dim xmldoc As MSXML2.DOMDocument
Dim doc As MSXML2.IXMLDOMElement
Dim node As MSXML2.IXMLDOMNode

Dim xs As String

' replace { with <

xs = "{a}{b}test one{/b}{b}test two{b}{/a}"

Set xmldoc = CreateObject("Microsoft.XMLDOM")

xmldoc.loadXML xs '-> it's a well formed xml string

Set doc = xmldoc.documentElement

For Each node In doc.childNodes

'code

MsgBox node.Text

Next

End Sub
 
Back
Top