Importing data from a completed InfoPath form (.xml) (Access 2007)

[AOB]

Hi all,

I'm trying to collaborate data on a daily basis from multiple sources across multiple regions with no network/server common to all participants. The plan is to distribute a daily dashboard based on the collected responses and monitor trends etc. I'd like to automate this as much as possible so that there is minimal work required from whoever has to co-ordinate all of this once it is up and running...

To achieve this, I intend to create a number of InfoPath form templates and store them on a SharePoint site to which each participant will have access. Each user will populate their respective forms each day and submit them (which sends the completed form to a central e-mail address)

My hope then is to create an Access database which pulls the data from each of the submitted forms within the appropriate Outlook folder, adds the appropriate records to the database and - eventually - generates some kind of dashboard of metrics.

I'm at the stage where I have one 'test' form template published to the SharePoint and can successfully complete and submit data which is routed to the appropriate inbox / folder.

I was hoping it would be relatively straightforward to programmatically import the data contained within each .xml file but it appears to be more troublesome than I thought. So I'm trying the VBA route...

I have a subroutine written which can identify the inbox / folder, loop through each of the mails / attachments and save them to a local drive (ideally, I would streamline the process by taking the data directly from the attachment rather than saving it first, but just want to get it working for now and improve it later...)

Now I'm working on a second sub to import the data from each saved .xml file. The only way I can see (which I know will not be sufficient) is the following :

Sub OpenInfoPathForm(stFormPath As String)
 
    Dim objIP As Object
    Dim objIPForm As Object
    Dim stFormInfo As String
 
    Set objIP = CreateObject("InfoPath.Application")
    Set objIPForm = objIP.XDocuments.Open(stFormPath)
 
    stFormInfo = objIPForm.DOM.Text
 
    objIP.XDocuments.Close (0)
 
    Set objIP = Nothing
    Set objIPForm = Nothing
 
End Sub

(Not overly fond of this either, as I'd rather not have to actually open the file, merely refer to it) This method at least provides me with a single string (stFormInfo) containing the concatenated field values, although it is not delimited, making it difficult to parse the string into its constituent fields. And I can't assume a space to be a delimiter as some of the fields are free-format text and therefore will have spaces within them.

Can anybody provide some guidance on how to identify/extract individual fields/controls in the .xml file, and their associated values, from which I could then proceed to write the code to add them to the various tables?

Apologies for the long-windedness, just thought a little background would be no harm... And thanks in advance to anyone who can point me in a direction!

AOB

 

[AOB]

For anyone who is interested, this seems to work reasonably well, if perhaps not particularly elegant...

Function takes as argument the path + filename of the .xml file, returns a two-dimensional array of the form data (the control names + the value/text contained within each...)

Function ExtractData(stFormPath As String) As Variant
 
    Dim xmlDoc As MSXML2.DOMDocument
    Dim xmlElement As MSXML2.IXMLDOMElement
    Dim xmlNode As MSXML2.IXMLDOMNode
    Dim arrData() As Variant
    Dim x As Integer
 
    Set xmlDoc = New MSXML2.DOMDocument
 
    xmlDoc.Load stFormPath
 
    Set xmlElement = xmlDoc.documentElement
 
    ReDim arrData(1 To 2, 1 To xmlElement.childNodes.length)
 
    x = 1
 
    For Each xmlNode In xmlElement.childNodes
 
        With xmlNode
 
            arrData(1, x) = .baseName
            arrData(2, x) = .Text
 
        End With
 
        x = x + 1
 
    Next xmlNode
 
    ExtractData = arrData
 
End Function

 

[wolfstep]

AOB,
I appreciate this post quite a bit. I am having trouble locating a solution to save an outlook infopath form item into a stand-alone file (e.g. "c:\test.xml"). I would like to do this in order to then automatically parse out the data values based on the script in this thread. It sounds like you developed the code to scroll through outlook emails and to save off the infopath form. I've tried several methods - none of which work. Help! Thanks!

 

[AOB]

Hi wolfstep,

Glad somebody got some value out of it!

Here is a modified version of my code to retrieve the Infopath (.xml) files from a folder of e-mails and save them locally which should help to get you started.

You may need to make some modifications based on how your Outlook mailbox is structured (Mailbox > Inbox > Folder > Subfolder etc.) but the logic should be the same regardless of the number of levels.

Also, worth noting that this will keep the same name as the attachment when saving it to the specified path so if you have multiple files with the same name it will cause a conflict - again, you can modify to suit (e.g. incorporate a timestamp into the filename when saving so every filename becomes unique). In my version, I import the file to the database and then kill it straight away so there's never more than 1 file saved at any one time which is how I got around it.

Let me know if this helps?

Option Compare Database
Option Explicit
 
Const strMailboxName = "<Insert Mailbox Name Here>"
Const strFolderName = "<Insert Folder Name Here>"
Const strSavedFilesPath = "<Insert Path Here>"
 
Sub CheckInbox()
[INDENT]Dim appOutlook As Object
Dim objNameSpace As Object
Dim objMailbox As Object
Dim objFolder As Object
Dim objItem As Object
 
Dim att As Variant
 
Dim lngNoOfEMails As Long
Dim i As Long
Dim strFileName As String
 
' Connect to MS Outlook
 
Set appOutlook = CreateObject("Outlook.Application")
Set objNameSpace = appOutlook.GetNamespace("MAPI")
objNameSpace.Logon
 
' Connect to mailbox
 
For i = 1 To objNameSpace.Folders.Count
 
[INDENT]If objNameSpace.Folders(i).Name Like strMailboxName Then
[INDENT]Set objMailbox = objNameSpace.Folders(i)
Exit For
[/INDENT]End If
 
[/INDENT]Next i
 
If objMailbox Is Nothing Then
[INDENT]If MsgBox("Can't connect to " & strMailboxName, vbCritical, "Can't Connect To Mailbox") = vbOK Then End
[/INDENT]End If
 
' Connect to folder
 
For i = 1 To objMailbox.Folders.Count
 
[INDENT]If objMailbox.Folders(i).Name Like strFolderName Then
[INDENT]Set objFolder = objMailbox.Folders(i)
Exit For
[/INDENT]End If
 
[/INDENT]Next i
 
If objFolder Is Nothing Then
[INDENT]If MsgBox("Can't connect to " & strFolderName, vbCritical, "Can't Connect To Folder") = vbOK Then End
[/INDENT]End If
 
' Loop through each received e-mail in folder
 
lngNoOfEMails = objFolder.Items.Count
 
For i = lngNoOfEMails To 1 Step -1
 
[INDENT]Set objItem = objFolder.Items(i)
strFileName = ""
 
' Check if mail item has attachments
 
If objItem.Attachments.Count > 0 Then
 
[INDENT]' Loop through attachments
 
For Each att In objItem.Attachments
 
[INDENT]' Check if attachment has xml file extension
 
If Right(att.FileName, 4) = ".xml" Then
 
[INDENT]' Save file
 
strFileName = strSavedFilesPath & att.FileName
att.SaveAsFile (strFileName)
 
[/INDENT]End If
 
[/INDENT]Next att
 
[/INDENT]End If
 
[/INDENT]Next i
 
[/INDENT]End Sub

 

[AOB]

If you tell me where it's falling down for you, I might be able to help?...

 

[wolfstep]

AOB,
I can't begin to thank you enough for your response. I am up and running now exporting the forms from outlook and happily reading the embedded data and the forms.

I had been trying to save the email message itself without recognizing that the inforpath form is treated as an attachment of the email. I knew it would be something relatively easy, but I had struggled with it for quite some time despite a lot of internet research. Again, thanks SO much!

 

[AOB]

Glad to hear it wolfstep - and thanks for taking the time to give feedback and confirm it's working for you! Best of luck with the rest of your coding

(You know you can hit the 'Thanks' button to formally register your satsifaction?... I haven't been 'thanked' yet so you can have the honour...!! )