Exporting Fields from MS Word Template

Question

I am creating a template in MS Word 2010. I am using the "Developer" functionality to create fields (text, pull-down, date) for people to fill in the template. Is it possible to easily/automatically export all of the values (that users of the template have entered) in the fields, say, to Excel? (Apart from copy and paste.)

Answer 1

The following Excel macro extracts data from all formfields & content controls in all Word documents in the selected folder and populates the first available row in the active Excel worksheet with the data for each document.

Sub GetFormData()

'Note: this code requires a reference to the Word object model.

'See under the VBE's Tools|References.

Application.ScreenUpdating = False

Dim wdApp As New Word.Application, wdDoc As Word.Document

Dim FmFld As Word.FormField, 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 & "\*.doc", vbNormal)

While strFile <> ""

  i = i + 1

  Set wdDoc = wdApp.Documents.Open(FileName:=strFolder & "" & strFile, AddToRecentFiles:=False, Visible:=False)

  With wdDoc

    j = 0

    For Each FmFld In .FormFields

      j = j + 1

      WkSht.Cells(i, j) = FmFld.Result

    Next

    For Each CCtrl In .ContentControls

      j = j + 1

      WkSht.Cells(i, j) = CCtrl.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

You can comment-out/delete the loop relating to FormFields or ContentControls, as applicable.

Note: As coded, the macro looks for files with the .doc extension (which can't contain content controls) as well as files with the docx & docm extensions. To work with docx files only, for example, change:

strFile = Dir(strFolder & "\*.doc", vbNormal)

to:

strFile = Dir(strFolder & "\*.docx", vbNormal)

Answer 2

VBA on Macs has numerous differences from VBA on PCs. Word's Documents.Open method, for example, doesn't support the Visible parameter. File paths are also expressed differently and I doubt the GetFolder function would work on a Mac, either. In sum, the code would require a major rewrite to work on a Mac. As I have only limited knowledge of that platform, I wouldn't be in a position to do that.

Answer 3

This macro looks like *exactly* what I need!!! Unfortunately though, I'm having an issue implementing it. For reference, I'm using MS Office 2011 Mac, I have changed the file ending referenced to .docx and I have activated the Word object model. I have a series of .docx (Word form) files all located in the same folder and I want to pull the form data from these files into Excel.

From what I understand of the relevant setup, what I need here is the FormFields, so I've removed lines 23-26 of the macro code (the ContentControls loop) as well as the reference ", CCtrl As Word.ContentControl" on line 6.

However, when I run the macro, I get the following error: "Compile error: Named argument not found". When this error appears, the portion of the macro code that is highlighted is "Visible:=" on line 16.

Is there an adjustment I can make here? Do you know what may be wrong? Unfortunately, I'm not familiar with VB programming. In advance, thank you very very much for any and all assistance!!!!!!!

Answer 4

Word does have an options to 'Save form data as a delimited text file', but that doesn't of itself get the data into your workbook. Nor does it output the field names (and it's not clear where they'd come from anyway). Granted, the macro doesn't generate the field names either, but they're easily added once you know which fields go into what columns on the worksheet.

Answer 5

Dear Paul,

thank you for your prompt and very detailed response. Wow! It looks amazing.  I'm not experienced with Visual Basic, but I'll try it out. Thanks!

(I was hoping there would be a command in MS Word somewhere to export all the fields, for example it would copy into the clipboard two rows - one with field names and one with corresponding entries. Then one could paste it into an Excel file.)