About Ashvin Expertise I can handle all the queries related to XML. I can sort out problems regarding XSLT and XSL. I work using MSXML components. I can handle XML-SQl, XML-VB and XMl-ASP related queries.
Experience I have worked for 2years in this feild. I have worked in SGML and DTDs, and now working in XML and Schemas.
XML - Manipulating XML files with Excel VBA & Xpath
Expert: Ashvin - 4/28/2004
Question Hello. My name is Jim Owens. I have a number of XML files that I need to query in order to construct relationship tables. An example of one table would include fields for Parent ID, Parent Name, Child ID, Child Name. The Xpath queries I should use to get this info have been provided to me.
I am new to XML, but well versed in Excel VBA. However, I have not been able to find any examples of how to use Excel VBA to manipulate XML via Xpath queries. The idea is that I would create an XML object, load one of my XML files into it, and then use a series of XPath queries to pull out the info I need. I have a vague notion that I'll need xmldom, and possible a class module, but I'm not sure of the syntax. Do you know where I can find an example that will help me with the syntax, etc? Even if it was in Access VBA, that would be better than nothing
Answer Hello Jim,
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.
Dim XMLDOC As MSXML2.DOMDocument
XMLDOC.Load ("D:\ashvin\myXML.xml")
Now you have XML file in XMLDOC and you can manupulate as you like.
Hope this will Help you, if not please send your XML file and the fields whcih you want to retrive.
