View Full Version : Loading XML file using VBA

02-04-2010, 12:08 PM
Sub Button1_Click()
MsgBox "Inside"
Dim code As String
Dim name As String
Dim strXML As String
Dim newFileName As String
Dim objNodeList As String
code = Sheet1.Range("B1")
name = Sheet1.Range("B2")

newFileName = code & "_" & "MyTestXML.xml"

Dim objXML As New MSXML2.DOMDocument

objXML.Load ("c:\MyTestXML.xml")
' get the example node
Set objElem = objXML.SelectSingleNode("generalInfo")

'objNodeList = objXML.getElementsByTagName("generalInfo")
MsgBox "Inside"

End Sub


<generalInfo code="UK_ABCD_MA_hrl" currencyCode="GBP" name="ABCD Master Agreement" startDate="01/01/2009" _
supplierCode="0020054719GB00P062" negotiableType="NOT_TO_EXCEED" supplierName="test" orderEndDateApplicable="Y">
<description>Hourly Rates</description>


02-04-2010, 08:01 PM
I'm confused, what's the question?

02-04-2010, 10:11 PM
Thanks for you reply.
I am trying to change the attribute code="" of <generalInfo> tag. I'm using MSXML.DOMDocument to load XML file. I'm able to read the element by using getAttribute() method, but when I call setAttribute() method it gives me an error. objElem.setAttribute("code","USA")

MsgBox "Inside"

Dim codeCol As String
Dim nameCol As String
Dim changeCode As Variant

changeCode = "Test"

Dim newFileName As String
codeCol = Sheet1.Range("B1")
nameCol = Sheet1.Range("B2")

newFileName = codeCol & "_" & "MyTestXML.xml"

MsgBox newFileName

Dim objXML As New MSXML.DOMDocument

Set objXML = New MSXML.DOMDocument
objXML.Load ("c:\MyTestXML.xml")

Dim objElem As MSXML.IXMLDOMElement
Set objElem = objXML.SelectSingleNode("//generalInfo")
MsgBox objElem.getAttribute("code")

02-05-2010, 09:39 PM
Got a small sample of the XML?