Vba loading xml documents in Excel

In the previous chapter, we saw the basics of XML documents and its applications.

The XML documents are the most preferred format for data sharing.

In this chapter, we will discuss how to work with XML documents in VBA.

The first step is to load the XML document in the program, which is as discussed below.

To work with XML in VBA, the XMLDOM object has to be referrenced and loaded in the program.

The following code shows, how to load a XMLDOM object.

  1. Dim xmlDoc As Object
  2. Set xmlDoc=CreateObject("Microsoft.XMLDOM")

Here, DOM stands for Document Object Model.

The DOM defines the document structure of XML and also it speficifies the ways to access the XML.

With the DOM, programmers can create and build documents, navigate their structure, and add, modify, or delete elements and content.

However DOM interfaces for the internal subset and external subset have not yet been specified.

In DOM, documents have a logical structure which is very much like a tree.

The term "DOM" was chosen because it is an "object model" and is used in the traditional object oriented design sense.

The "CreateObject" method is used to create a referrence to any kind of object.

There are various COM components in Excel and they can be accessed using this method.

In our Example, We have created XMLDOM object.

Next, to load the XML dicument, the following code is used.

  1. xmlDoc.Load(<xml_file_name>)

As in the above code, the Load method is used to initialize XML document.

Another way of loading the XML document is as shown below

This is done by MSXML2.DomDocument namespace.

This is illustrated as follows:

  1. Dim XMLDOC As MSXML2.DOMDocument
  2. XMLDOC.Load (<xml_file>)


You can find similar Excel Questions and Answer hereunder

1) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

2) How can I generate random numbers between 2 limits?

3) Here some explanations about xml and how to use xml in VBA

4) How to find the column number from the cell address in VBA

5) How can I avoid updates to cell values during macro execution?

6) What is activeX data object in Excel. How to use it? Here the basic explanations

7) How to print a worksheet in Excel VBA

8) Here some explanations about the XML DOM node in VBA, DOM stands for Document Object Model

9) Formula does not calculate in Excel

10) In my organization different users have different versions of Excel - how do I check compatibility issues with my WorkBook across versions?


Here the previous and next chapter