title: Importing multiple InfoPath XML files into Excel
body: <p>I'm using InfoPath forms to collect data from field teams and all completed forms are saved to the standard InfoPath XML format. Now I want to import all these XML files into Excel to compile reports. I was doing this one XML file at a time but this is of course silly. Anyway a friend wrote this Excel VB Macro code for importing multiple XML files to a Excel sheet tab (XMLData) and then copying the data across neatly to another tab (Results). The problem is the data in the XML files are imported out of sequence into the Excel columns. Is there a simple fix to the code for this? Here is Excel VB Macro code:</p>
  
  <pre><code>Sub ReadXML()
      Dim strFile As String
      MsgBox "I'll start reading please don't touch the computer"
      Dim strPath As String
      Dim colFiles As New Collection
      Dim i As Integer
      Dim wb As Workbook
      strPath = ActiveSheet.Range("C2")
      strFile = Dir(strPath)
      While strFile &lt;&gt; ""
          colFiles.Add strFile
          strFile = Dir
      Wend
      If colFiles.Count &gt; 0 Then
          For i = 1 To colFiles.Count
               Application.ScreenUpdating = False
               Application.DisplayAlerts = False
               strTargetFile = strPath &amp; colFiles(i)
  
               Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadOpenXml)
               Application.DisplayAlerts = True
  
               wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("XMLDATA").Range("A" &amp; i * 3 + 1)
               ThisWorkbook.Sheets("XMLDATA").Rows(i * 3 + 3).Copy ThisWorkbook.Sheets("Result").Range("A" &amp; i + 2)
               wb.Close False
               Application.ScreenUpdating = True
               ActiveSheet.Range("P2") = i
          Next i
          ThisWorkbook.Sheets("XMLDATA").Rows(4).Copy ThisWorkbook.Sheets("Result").Rows(1)
          ThisWorkbook.Sheets("XMLDATA").Rows(5).Copy ThisWorkbook.Sheets("Result").Rows(2)
          MsgBox "I'm Done!"
      End If
  End Sub
  </code></pre>
  
  <p>Maybe there is a simpler more elegant solution for importing InfoPath XML files to Excel, just haven't found it yet. Your help greatly appreciated.</p>