Writing XML in VBA

add reference to microsoft xml

Public Sub CreateXML()

Dim objDom As DOMDocument
Dim objXMLRootelement As IXMLDOMElement
Dim objXMLelement As IXMLDOMElement
Dim objXMLChildElement As IXMLDOMElement
Dim objXMLattr As IXMLDOMAttribute

Set objDom = New DOMDocument

'~~> Creates root element
Set objXMLRootelement = objDom.createElement("Entry")
objDom.appendChild objXMLRootelement

'~~> Create Time element
Set objXMLelement = objDom.createElement("Country")
objXMLelement.Text = "Sri Lanka"
objXMLRootelement.appendChild objXMLelement

'~~> Create Date element
Set objXMLelement = objDom.createElement("Date")
objXMLRootelement.appendChild objXMLelement

'~~> Creates Attribute to the Date Element and set value
Set objXMLattr = objDom.createAttribute("Value")
objXMLattr.NodeValue = "3/2/2012"
objXMLelement.setAttributeNode objXMLattr

'~~> Create Time element
Set objXMLelement = objDom.createElement("Time")
objXMLRootelement.appendChild objXMLelement

'~~> add a child element to an element
Set objXMLChildElement = objDom.createElement("Local")
objXMLChildElement.Text = "9:30"
objXMLelement.appendChild objXMLChildElement

'~~> Saves XML data to a file
objDom.Save ("C:\CSharpNoob2011.xml")
Debug.Print objDom.XML

End sub

Read XML Document into string

// using System.IO;
// using System.Xml;


XmlDocument xdoc = new XmlDocument();
xdoc.Load("C:\\Export DataSet_INPUT.xml");

// Now create StringWriter object to get data from xml document.
StringWriter sw = new StringWriter();
XmlTextWriter xw = new XmlTextWriter(sw);
xdoc.WriteTo(xw);
String xmlstring = sw.ToString();

Manipulating XML files with Excel VBA & Xpath

Click here for more reading

Reading from XML into Excel. Sample

This is a better example. Click here

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.



Sub Macro1()
Dim iRow As Integer
Dim oxmlDoc As MSXML2.DOMDocument
Set oxmlDoc = New MSXML2.DOMDocument
Dim oXmlNode As MSXML2.IXMLDOMNode
Dim oXmlNode1 As MSXML2.IXMLDOMNode
Dim oXmlNodes As MSXML2.IXMLDOMNodeList

'Debug.Print xmlDoc.XML
'Debug.Print xmlDoc.Text

oxmlDoc.async = False
oxmlDoc.Load ("C:\WSOutput1.xml")

Set oXmlNodes = oxmlDoc.selectNodes("//XML_OUTPUT/ITEM/ELEMENTS[@TYPE='1']") ' reading attribute
Sheets("sheet1").Activate ' Sheets(strCostData).Activate

iRow = 4
For Each oXmlNode In oXmlNodes

'Debug.Print "ID :" & oXmlNode.childNodes.item(ConsumableFields.ID).Text
'Debug.Print "Name :" & oXmlNode.childNodes.item(ConsumableFields.Name).Text
Sheets("sheet1").Range("A" & Trim(str(iRow))).Value = oXmlNode.childNodes.item(0).Text

iRow = iRow + 1
Next oXmlNode
Set xmlDoc = Nothing

End Sub