Extract infopath xml data into excel

Question

I have around 2000 InfoPath forms in SharePoint 2010 document library. I want to extract all forms in excel using macro. Any help?

Answer 1

If your SharePoint site supports browsing a document library with Windows Explorer, do that first and then run the following snippet from within a code module in Excel.

The code loops through the library and captures any file with an XML file type:

Dim strFile As String
Dim strFileType As String

strFile = Dir("\\sharepoint.[yoursite].com@SSL\DavWWWRoot\[folder path that contains the InfoPath files]\")

Do While strFile <> ""
    strFileType = Right(strFile, Len(strFile) - InStr(strFile, "."))
    If strFileType = "xml" Then
        Debug.Print strFile
        'Do something here
    End If
    strFile = Dir()
Loop

InfoPath files are really just a cabinet file packaging a series of XML (and similar) files. If the actual InfoPath form is what you really need, then change the code above to look for the "xsn" file type.