Monday, December 22, 2008

Reading XML from VBA

I have faced with this problem (reading XML file from VBA and populate these values in a PowerPoint 2000 template) for quite sometime and I thought it is not possible to read XML file from Office 2000 (which later I came to know is possible). Also, I didn’t read much of the article, as I know we can open XML file in Excel 2003. So I made up myself to read this XML file via Excel and then populate these values in PowerPoint, which I successfully did.

But later I found we can add an XML reference and can read the XML file easily from Word, Excel or PowerPoint. So here I am providing an example on how to read XML file from PowerPoint.

Our XML file has the below structure. If you want to test it, copy the Code below and save it as "Employee.XML"

Employee.XML

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<EmpDetails>
<Employee>
<Name>ABC</Name>
<Dept>IT-Software</Dept>
<Location>New Delhi</Location>
</Employee>
<Employee>
<Name>XYZ</Name>
<Dept>IT-Software</Dept>
<Location>Chennai</Location>
</Employee>
<Employee>
<Name>IJK</Name>
<Dept>HR Operations</Dept>
<Location>Bangalore</Location>
</Employee>
</EmpDetails>

Open up VBA editor in PowerPoint and add references to "Microsoft XML, v3.0" (this reference is for Office 2000).

Create a new module and copy paste the following code and run it.

Below is the code to read the above XML file:

Sub XMLfromPPTExample()
Dim XDoc As MSXML2.DOMDocument
Dim xEmpDetails As MSXML2.IXMLDOMNode
Dim xEmployee As MSXML2.IXMLDOMNode
Dim xChild As MSXML2.IXMLDOMNode

Set XDoc = New MSXML2.DOMDocument
XDoc.async = False
XDoc.validateOnParse = False
XDoc.Load ("C:\Emp.xml")
Set xEmpDetails = XDoc.documentElement
Set xEmployee = xEmpDetails.firstChild
For Each xEmployee In xEmpDetails.childNodes
For Each xChild In xEmployee.childNodes
MsgBox xChild.baseName & " " & xChild.Text
Next xChild
Next xEmployee
End Sub

7 comments:

Hello Everyone! said...

Thanks a lot for this post, it has helped me a great deal.

Hello Everyone! said...
This comment has been removed by the author.
niks said...

Hi Great work!!!

Can we list them in cells.

say for example
cellA cellB cellC
Employee Name XYZ
Dept IT Software

nodename value

venkat ramana said...

Thank you so much. Very USeful

venkat ramana said...

Thank you so much. Very Useful

lee woo said...

A strong, positive self-image is the best possible preparation for success. See the link below for more info.


#possibles
www.ufgop.org

Silvia Jacinto said...

Reading your article is such a privilege. It does inspire me, I hope that you can share more positive thoughts. Visit my site too. The link is posted below.

n8fan.net

www.n8fan.net