Read Xml File From Vba

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. 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. XmlGrid2.png' alt='Read Xml File From Vba' title='Read Xml File From Vba' />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. Usb Lock Standard 3.5 Activation Code Software here. Loading XML document in VBAThe MSXML2. Excel VBA Open Workbook Easily Open Excel Files In VBA With These 2 Methods And Macros. By Jorge A. Gomez. 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. Xwtbi.png' alt='Read Xml File From Vba' title='Read Xml File From Vba' />The Office XML Formats change the way that customers work with data. Consider some of the following scenarios, now possible with the new file formats. 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. 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. Format. Extension. Description. Excel Workbook. xlsx. The default XMLbased file format for Excel 2010 and Excel 2007. Cannot store Microsoft Visual Basic for. The Windows version of Excel supports programming through Microsofts Visual Basic for Applications VBA, which is a dialect of Visual Basic. Programming with VBA. 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. 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. Visual Basic file. VBA is a file extension for a Visual Basic file used with the Microsoft Office Suite. VBA stands for Visual Basic for Applications. 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. ObjectMSXML2. DOMDocument. XDoc. async 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. Oil Well Drilling Problems Pdf here. 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. 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. XDoc. 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 rightWell 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. Control Plan'>Control Plan. 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. 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.