title: Load XML into Excel through VBA
body: <p>I've got a bit of VBA that is loading an XML file through VBA. However when it is imported it is all in one column and not split into a table. </p>
  
  <p>When I manually import this through the Data tab I get the warning there is no schema but asks if I would like Excel to create one based on source data. This then places all the data in a nice table.</p>
  
  <p>I would like this to happen automatically within my current VBA code:</p>
  
  <p>VBA looks like</p>
  
  <pre><code>      Sub refresh()
  
  '--------------------------------1. Profile IDs-----------------------------------'
  
  
  'date variables
  
  Dim start_period As String
  start_period = Sheets("Automated").Cells(1, 6).Value
  Dim end_period As String
  end_period = Sheets("Automated").Cells(1, 7).Value
  
  'report id variable names
  Dim BusinessplanningReportID As String
  
  '--------------------------------REST queries--------------------------------'
  Dim Businessplanning As String
  
  
  
  'REST query values
  Businessplanning = "URL;http://api.trucast.net/2/saved_searches/00000/pivot/content_volume_trend/?apikey=0000000&amp;start=" + start_period + "&amp;end=" + end_period + "&amp;format=xml"
  
  
  
  
  '--------------------------------------------Data connections-----------------------------------'
  'key metrics
  With Worksheets("Sheet1").QueryTables.Add(Connection:=Businessplanning, Destination:=Worksheets("Sheet1").Range("A1"))
  
    .RefreshStyle = xlOverwriteCells
    .SaveData = True
  
  End With
  </code></pre>
  
  <p>Currently the data then presents itself like this, unstructured. How can I automatically turn this into a table?</p>
  
  <pre><code>&lt;result&gt;
  &lt;entry&gt;
  &lt;published_date&gt;20130201&lt;/published_date&gt;
  &lt;post_count&gt;18&lt;/post_count&gt;
  &lt;/entry&gt;
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>::Final solution::</p>
  
  <pre><code> Sub XMLfromPPTExample2()
  Dim XDoc As MSXML2.DOMDocument
  Dim xresult As MSXML2.IXMLDOMNode
  Dim xentry As MSXML2.IXMLDOMNode
  Dim xChild As MSXML2.IXMLDOMNode
  Dim start_period As String
      start_period = Sheets("Automated").Cells(1, 6).Value
      Dim end_period As String
      end_period = Sheets("Automated").Cells(1, 7).Value
  Dim wb As Workbook
  Dim Col As Integer
  Dim Row As Integer
  
  
  Set XDoc = New MSXML2.DOMDocument
  XDoc.async = False
  XDoc.validateOnParse = False
  XDoc.Load ("http://api.trucast.net/2/saved_searches/0000/pivot/content_volume_trend/?apikey=00000&amp;start=" + start_period + "&amp;end=" + end_period + "&amp;format=xml")
  LoadOption = xlXmlLoadImportToList
  
  Set xresult = XDoc.DocumentElement
  Set xentry = xresult.FirstChild
  
  
  Col = 1
  Row = 1
  
  For Each xentry In xresult.ChildNodes
   Row = 1
  
  
      For Each xChild In xentry.ChildNodes
        Worksheets("Sheet2").Cells(Col, Row).Value = xChild.Text
               'MsgBox xChild.BaseName &amp; " " &amp; xChild.Text
        Row = Row + 1
        'Col = Col + 1
  
            Next xChild
  'Row = Row + 1
  Col = Col + 1
  Next xentry
  
  End Sub
  </code></pre>