Extract Data from Word Form Fields to Excel Spreadsheet

[mfernandes]

I have a problem similar to this: vbaexpress.com/forum/showthread.php?40406-Extracting-Word-form-Data-and-exporting-to-Excel-spreadsheet (had to remove hyperlink since I'm a new user)

I created a template (see attached) in Word with several legacy form fields. Each form field has a unique reference name which matches a column on a spreadsheet. These forms will be completed by other people and then sent to me where I need to get the data onto the spreadsheet, under the corresponding names. The forms will be stored in one folder and sent to me sporadically, so I'll need to update the spreadsheet over time and have new entries populate on a new row. I've tried to alter a few solutions to fit my needs but I'm afraid I'm not familiar enough with VBA to make it work fully. Any help is much appreciated and I hope to use this to help me better understand VBA. Thanks!

[mfernandes]

Ok, so I was able to tinker with the code by Macropod (thanks!) in another thread as follows:


Sub GetFormData()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.ContentControl
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each FField In .FormFields
j = j + 1
WkSht.Cells(i, j) = FField.Range.Text
Next
End With
wdDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True
End Sub


Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function


This gets me mostly there though checkbox data shows up as a box drawing character where I'd want it to be 1/0 or true/false. Any ideas how to resolve this?

EDIT: Just to clarify what I mean by "box drawing character," these characters populate the cells which relate to the checkboxes on the form:
12830

[gmayor]

There are several pages on my web site related to this - The main ones can be found at http://www.gmayor.com/ExtractDataFromForms.htm (http://www.gmayor.com/ExtractDataFromForms.htm)and if the forms are e-mailed to you as part of the message body, then see http://www.gmayor.com/extract_data_from_email.htm

As for the formfield in your code you probably need something like


Dim strResult as String
With wdDoc
j = 0
For Each FField In .FormFields
With FField
Select Case .Type
Case Is = wdFieldFormTextInput, wdFieldFormDropDown
strResult = .Result
Case Is = wdFieldFormCheckBox
strResult = .CheckBox.Value
End Select
End With
j = j + 1
WkSht.Cells(i, j) = strResult
Next
End With