Importing multiple InfoPath XML files into Excel

Question

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:

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 <> ""
        colFiles.Add strFile
        strFile = Dir
    Wend
    If colFiles.Count > 0 Then
        For i = 1 To colFiles.Count
             Application.ScreenUpdating = False
             Application.DisplayAlerts = False
             strTargetFile = strPath & colFiles(i)

             Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadOpenXml)
             Application.DisplayAlerts = True

             wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("XMLDATA").Range("A" & i * 3 + 1)
             ThisWorkbook.Sheets("XMLDATA").Rows(i * 3 + 3).Copy ThisWorkbook.Sheets("Result").Range("A" & 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

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.

Answer 1

The simplest change would be to leverage OpenXML's Stylesheets parameter to specify an XSLT stylesheet to format the XML in the way you want. You could then extract the content in the whatever order and format you like, as specified within the stylesheet.

A more complex change (and my preferred method for this type of thing) is to load the content into a DOMDocument object and use functions like selectNodes and selectSingleNode to extract the content you want.

Answer 2

I found this old question while searching to accomplish the same thing. I was able to get the code above to run but opening and closing all the files would usually cause Excel 2016 to crash after a while. I ended up with this indirect approach using powershell which worked much faster:

$files=Get-ChildItem "x:\folder with xml files\*" -Include *.xml
$outFile="x:\folder with xml files\Results.csv"
Remove-Item $outFile -ErrorAction Ignore
$xml = New-Object -TypeName -XML
foreach ($file in files){
  $xml.Load($file)
  Write-Host "Adding $file"
  $xml.myfields | Select-Object -Property Question1, Quesiton2, Question3 |Export-CSV $outFile -NoTypeInformation -Deliminter "|" -Append -encoding UTF8
}

This spits out a CSV file (I used | as a delimiter because my responses contained commas) that I was able to quickly import into Excel.