Working with XML files in VBA VBA XML The Analyst Cave. XML files are one of the most common type of data files apart from text and CSV comma separated values files. Reading data files which are not hierarchical as XML files or JSON is relatively easy. You can read in the data row by row and process columns separately. With XML and JSON the task is not as easy as the data is hierarchical parent child relationships exist between records in the schema and the number of underlying nodes may vary as opposed to tabular data which usually has a constant number of columns separated with a specific delimiter. Fortunately, we can use the MSXML2. DOMDocument object in VBA. Lets however, as always, start with a short introduction as to how XML files a structure before we dive into the examples. Loading XML document in VBAThe MSXML2. DOMDocument object allows you to easily traverse through an XML structure an extract any XML node andor attribute needed. Lets look at the example below. How to work with XML files in VBA VBA XML. Learn how to use XPath and how to traverse the XML Dom with the MSXML2. DOMDocument object. If you want to create an XML data file and XML schema file from a cell range on a worksheet, you can use version 1. Excel 2003 XML Tools Addin to enhance the. MSDN Magazine Issues and Downloads. Read the magazine online, download a formatted digital version of each issue, or grab sample code and apps. Read about the XML Formats used in Excel, PowerPoint, and Word. Below we start by loading the XML document. Notice that I am selecting the load to be performed synchronously and not validation be carried out on parsing the document. Feel free to change these options if needed. Dim XDoc As Object, root as Object. Set XDoc Create. ObjectMSXML2. DOMDocument. XDoc. False XDoc. On. Parse False. XDoc. Load This. Workbook. Path test. Set root XDoc. Document. Element. Alternatively load an XML from a string. Dim XDoc As Object, root as Object. Set XDoc Create. ObjectMSXML2. DOMDocument. XDoc. False XDoc. On. Parse False. XDoc. Load. XML lt root lt child lt child lt root. Set root XDoc. Document. Element. Thats it. You have loaded the XML document into memory into the DOMDocument object. The document has been parsed and you can easily traverse the enclosed elements. See next section. XML DOM nodes in VBAThe XML document will provide you with the root of the entire DOM of type XDoc. Document. Element. Each Document. Element XML DOM node facilitates the following node references parent. Node XDoc. Document. IC296836.jpg' alt='Read Xml File From Vba' title='Read Xml File From Vba' />XmlHttpRequest object is used to make HTTP requests in VBA. HTTP requests can be used to interact with a web service, API or even websites. VBScript can also be used to create applications that run directly on a workstation running Microsoft Windows. The simplest example is a script that makes use of the. You can convert your Excel file to an XML data file, or vice versa. This enables you to exchange data between different applications. First, turn on the Developer tab. Element the parent node, one node higher in the DOM hierarchyfirst. Child XDoc. Document. Element the first child node, first node lower in the DOM hierarchylast. Child XDoc. Document. Element the last child node, last node lower in the DOM hierarchychild. Nodes Array of type XDoc. Document. Element all child nodes of the current node, all nodes lower in the DOM hierarchynext. Sibling XDoc. Document. Element next sibling node i. DOM hierarchy, having the same parent nodeprevious. Sibling XDoc. Document. Element previous sibling node i. DOM hierarchy, having the same parent node. All the above references allow you to free move within the XML DOM. Child. Nodes. Lets start by extracting the first list and printing its XML and text contents. The basics to moving around the XML DOM is using Child. Nodes. Dim XDoc As Object. Set XDoc Create. ObjectMSXML2. DOMDocument. Excel VBA Open Workbook Easily Open Excel Files In VBA With These 2 Methods And Macros. By Jorge A. Gomez. Hi Experts I have an XML file which I am using to store settings with which I wish to populate a user form in Word VBA. I have previously used. XDoc. async False XDoc. On. Parse False. XDoc. Load This. Workbook. Path test. Get Document Elements. Set lists XDoc. Document. Element. Get first child same as Child. Nodes0. Set get. First. Child lists. First. Child. Print first child XML. Debug. Print get. First. Child. XML. Print first child Text. Debug. Print get. First. Child. Text. Set XDoc Nothing. This is the result. Print first child XML. Name Reconlt Name. TO John Bob Rob Chrislt TO. CC Jane Ashleylt CC. BCC Brentlt BCC. Print first child Text. Recon John Bob Rob Chris Jane Ashley Brent. Traversing through the whole XML in VBANow that we got the basics lets print out the whole contents of the XML DOM including the basenames node names. Dim XDoc As Object. Set XDoc Create. ObjectMSXML2. DOMDocument. XDoc. False XDoc. On. Parse False. XDoc. Load This. Workbook. Path test. Get Document Elements. Set lists XDoc. Document. Element. Traverse all elements 2 branches deep. For Each list. Node In lists. Child. Nodes. Debug. Print Email. For Each field. Node In list. Node. Child. Nodes. Debug. Print field. Node. Base. Name field. Node. Text. Next field. Node. Next list. Node. Set XDoc Nothing. This is the result. Name Recon. TO John Bob Rob Chris. CC Jane Ashley. BCC Brent. Name Safety Metrics. TO Tom Casper. CC Ashley. Name Performance Report. TO Huck Ashley. CC Tom Andrew. BCC John Seema. Easy right Using the basics above we can easily move around the document. But this still seems like a lot of coding right Well there is an easier way of moving extracting items using the DOMDocument object called XPath. XML Document example node references. Now that we have a hang of our XML document. Lets work on a simple example. Below an XML document assigned with references to each nodenodes Example XML Document. Distribution. Lists. List. lt Name Reconlt Name. TO John Bob Rob Chrislt TO. CC Jane Ashleylt CC. BCC Brentlt BCC. List. List. Name Safety Metricslt Name. TO Tom Casperlt TO. CC Ashleylt CC. BCC Johnlt BCC. List. lt List. Name Performance Reportlt Name. TO Huck Ashleylt TO. CC Tom Andrewlt CC. BCC John Seemalt BCC. List. lt Distribution. Lists. XML Document nodes reference examples. Distribution. ListsFirst. ChildListChild. Nodes0Name ReconChild. Nodes0. Child. Nodes0. TextTO John Bob Rob Chris Child. Nodes0. Child. Nodes1. TextList. ListChild. Nodes1Name Performance ReportChild. Nodes1. Child. Nodes0. TextXPath in VBAInstead of traversing the elementsnodes in your XML using the. Child. Nodes. First. ChildNext. Child properties we can also use XPath. XPath is a query language used for selecting XML nodes in an XML document. Micro Expression Training Tool 3.0 more. It is represented by a single string. It allows you to extract any number of nodes 0 or more which match the specified XPath query. If you want to learn XPath I can recommend this overview https www. Now lets jump into an example Example 1 Extract all Lists. Dim XDoc As Object. Set XDoc Create. ObjectMSXML2. DOMDocument. XDoc. False XDoc. On. Parse False. XDoc. Load This. Workbook. Path test. Set lists XDoc. Select. NodesDistribution. ListsList. Set XDoc Nothing. Example 2 Extracting all TO fields. Set to. Fields XDoc. Select. NodesDistribution. ListsListTO. Example 3 Extracting the first and last Name field. Set first. Name. Field XDoc. Select. NodesDistribution. ListsList0Name. Set last. Name. Field XDoc. Select. NodesDistribution. ListsList2Name. Example 3 Extracting all child List nodes Name, TO, CC, BCC. Set list. Children. Field XDoc. Select. NodesDistribution. ListsList. XML Attributes in VBALets tackle one last example attributes. Lets slightly modify the XML above and include an example attribute named attribute. Distribution. Lists. List. lt Name attributesome Reconlt Name. Using XPath or traversing the DOM we can easily extract the attribute as shown below. Set first. Name. Field XDoc. Select. NodesDistribution. ListsList0Name. Debug. Print first. Name. Field0. Attributes0. Text. Result some. Creating XML documents. Creating documents is also quite straight forward in VBA.