title: Automatically map an Excel XmlMap to a worksheet in VBA without knowing the schema XPaths
body: <p>I am building an Excel file that downloads from an API.</p>
  
  <p>It can automatically generate the XmlMap from the URL schema metadata. However I then need to map the XmlMap elements to ListObjects in order to pull the data and put on a worksheet.</p>
  
  <p>The code to do this is <code>range.Xpath.SetValue map xPath</code> for each item (from <a href="https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xpath-setvalue-method-excel" rel="nofollow noreferrer">MSDN</a>):</p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub CreateXMLList() 
      Dim mapContact As XmlMap 
      Dim strXPath As String 
      Dim lstContacts As ListObject 
      Dim objNewCol As ListColumn 
  
      ' Specify the schema map to use. 
      Set mapContact = ActiveWorkbook.XmlMaps("Contacts") 
  
      ' Create a new list. 
      Set lstContacts = ActiveSheet.ListObjects.Add 
  
      ' Specify the first element to map. 
      strXPath = "/Root/Person/FirstName" 
      ' Map the element. 
      lstContacts.ListColumns(1).XPath.SetValue mapContact, strXPath 
  
      ' Specify the second element to map. 
      strXPath = "/Root/Person/LastName" 
      ' Add a column to the list. 
      Set objNewCol = lstContacts.ListColumns.Add 
      ' Map the element. 
      objNewCol.XPath.SetValue mapContact, strXPath 
  
      strXPath = "/Root/Person/Address/Zip" 
      Set objNewCol = lstContacts.ListColumns.Add 
      objNewCol.XPath.SetValue mapContact, strXPath 
  End Sub
  </code></pre>
  
  <p>Here's the schema output:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;?xml version="1.0" encoding="utf-8"?&gt;
  &lt;xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" &gt;
      &lt;xsd:element name="root" nillable="true" &gt;
          &lt;xsd:complexType&gt;
              &lt;xsd:sequence minOccurs="0"&gt;
                  &lt;xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="list-item" form="unqualified"&gt;
                      &lt;xsd:complexType&gt;
                          &lt;xsd:sequence minOccurs="0"&gt;
  
                              &lt;xsd:element name="data_source_organization"
                                  minOccurs="0"
                                  nillable="true"
                                  type="xsd:string"
                                  form="unqualified"
                              /&gt;
  
                              &lt;xsd:element name="survey_name"
                                  minOccurs="0"
                                  nillable="true"
                                  type="xsd:string"
                                  form="unqualified"
                              /&gt;
                          &lt;/xsd:sequence&gt;
                      &lt;/xsd:complexType&gt;
                  &lt;/xsd:element&gt;
              &lt;/xsd:sequence&gt;
          &lt;/xsd:complexType&gt;
      &lt;/xsd:element&gt;
  &lt;/xsd:schema&gt;
  </code></pre>
  
  <p>Here's the data (from which Excel automatically gets the schema and creates the XmlMap, if using the GUI):</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;root xsi:noNamespaceSchemaLocation="/api/domain/schema/?format=xml"&gt;
      &lt;list-item&gt;
          &lt;data_source_organization&gt;An org&lt;/data_source_organization&gt;
          &lt;survey_name&gt;A Survey&lt;/survey_name&gt;
      &lt;/list-item&gt;
      &lt;list-item&gt;
          &lt;data_source_organization&gt;An org&lt;/data_source_organization&gt;
          &lt;survey_name&gt;Another Survey&lt;/survey_name&gt;
      &lt;/list-item&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>However I don't want to specify the XPath strings - I want Excel to get everything from the schema metadata, just like it does if you use the GUI functionality (Data, Get External Data, From Other Sources, XML, paste a URL) - this automatically creates an XML map, creates a ListObject on the worksheet, maps every column in the source data, and grabs and displays the data. (If you record a macro doing this, it skips the mapping step.)</p>
  
  <ul>
  <li>Can I point an XmlMap to a cell, range or ListObject?</li>
  <li>Can I iterate the XmlMap and retrieve every list-item XPath?</li>
  <li>Some other way?</li>
  </ul>
  
  <p>To experiment/reproduce, save the above XML as files, then create a sub as follows:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Set currentMap = ActiveWorkbook.XmlMaps.Add("C:\path\to\schema.xml", "root")
  currentMap.DataBinding.LoadSettings "path\to\data.xml"
  ' Do something to map the XmlMap elements to cells in the spreadsheet
  ' eg, objNewCol.XPath.SetValue currentMap, "root/data_source_organization"
  ' But some method that does not involve naming the Xml paths but iterates the schema
  currentMap.DataBinding.Refresh
  </code></pre>
  
  <p>If the XmlMap is mapped to cells, those cells will populate with data.</p>