Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

How to generate an XML document from within VBA by jrbarnett
Posted: 9 Oct 14

This FAQ contains code to use MSXML6 to generate a template XML document. To be of much use, it will need to be expanded to include your own content, and adapted for your own node name and namespace, and saved to your own preferred location, or adapted to store it in a database for example.

Although presented in the general VBA forum, this should also work in Access VBA and Visual Basic 6.0. The MSXML6 library is included with all versions of Windows since XP Service pack 3 so should be present on most Windows computers in use today. This can be added by using the VBA References menu, see item 3 in the Acknowledgements section.

It should be fairly easily adaptable to meet your own needs.

The code to generate the XML is:


Public Function GenerateXML(strPath As String) As Integer
    ' Constant values for XML namespace code and value
    Const XML_NAMESPACE_ATTR As String = "xmlns:wow"
    Const XML_NAMESPACE_VALUE As String = "http://www.example.com/ns/1.0/"
    Const QUOTE_MARK As Integer = 34        ' ASCII code for double quote mark
    Dim xmlDom As MSXML2.DOMDocument60          ' Represents the DOM document
    Dim xmlRootNode As MSXML2.IXMLDOMElement     ' Represents the XML root node
    Dim xmlVersion As MSXML2.IXMLDOMProcessingInstruction   ' This will become the <xml version="1.0"?> processing instruction
    Dim xslStylesheet As MSXML2.IXMLDOMProcessingInstruction ' Optional XSL Stylesheet processing instruction
    Set xmlDom = New MSXML2.DOMDocument60
    ' Create the <?xml version="1.0"?> processing instruction header at the top of the file. 
    Set xmlVersion = xmlDom.createProcessingInstruction("xml", "version=" & Chr(QUOTE_MARK) & "1.0" & Chr(QUOTE_MARK))
    xmlDom.appendChild xmlVersion
    ' Create the <?xml-stylesheet type="text/xsl" href="stylesheet.xsl" version="1.0" ?> processing instruction. Uncomment the following lines to activate the XSL stylesheet reference (the XSL must exist in the specified folder)
    'Set xslStylesheet = xmlDom.createProcessingInstruction("xml-stylesheet", "type=" & Chr(QUOTE_MARK) & "text/xsl" & Chr(QUOTE_MARK) & " href=" & Chr(QUOTE_MARK) & "stylesheet.xsl" & Chr(QUOTE_MARK) & " version=" & Chr(QUOTE_MARK) & "1.0" & Chr(QUOTE_MARK))
    'xmlDom.appendChild xslStylesheet
    ' Define the Database root node and append to Dom
    Set xmlRootNode = xmlDom.createElement("RootNode")
    xmlDom.appendChild xmlRootNode
    ' set the root node namespace attribute
    ' this adds the "http://www.example.com/ns/1.0/" attribute to the main root node
    ' so that it is not running in open space
    xmlDom.documentElement.setAttribute XML_NAMESPACE_ATTR, XML_NAMESPACE_VALUE

    ' Put your code in here to expand the document to your own requirements
    ' 1. Create an element, eg
    ' Dim xmlElement As MSXML2.IXMLDOMElement
    ' Set xmlElement = xmlDom.createElement("YourElementName")
    ' 2. Assign it a value
    ' xmlElement.Text = "Hello XML World"
    ' 3. Now append it to the root node
    ' xmlRootNode.appendChild xmlElement
    ' now save the data to the specified path on disk
    xmlDom.Save (strPath)
    GenerateXML = xmlRootNode.childNodes.length  ' return number of direct child nodes off the root
End Function 

The XML document produced is:


<xml version="1.0">
<RootNode xmlns:wow="http://www.example.com/ns/1.0" /> 

or with the stylesheet line added


<xml version="1.0">
<?xml-stylesheet type="text/xsl" href="stylesheet.xsl" version="1.0" ?>
<RootNode xmlns:wow="http://www.example.com/ns/1.0" /> 

This is the culmination of a lot of work, and it is only fair that credit is given where due to items that helped me get this done:
1. Create XML using the MSXML6 object library: http://www.freevbcode.com/ShowCode.asp?ID=1919
2. YouTube videos using MSXML6 in Visual Basic 6:
https://www.youtube.com/watch?v=HZm-xaaiGag and https://www.youtube.com/watch?v=cYsnWoMCTUM
3. Referencing MSXML withing VBA projects:

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close