Extract Word Form Field Data to Excel


Board Regular
Jun 9, 2014
Hello all,

Google makes it sound like this can be done but I can't understand the guidance. Hoping you can help. We have a new word document with about 16 form fields (a mix of legacy form fields and content controls). I'd like to be able to automatically compile the data from the 16 form fields into a spreadsheet.

It would be even better if this could be an ongoing thing, meaning today I could extract a few documents to excel, and next week if I do more, I can add them to excel sheet.

Any tips?

I know this is easier as a PDF form but we want to stay in word so we can edit non-form-field text if needed.

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.


MrExcel MVP
Oct 15, 2007
Try this Excel macro, which loops through all *.doc* files in the specified folder and puts the form fields in Sheet1. The file names are put in column A and the field names are put in row 1 as column headings in the order they are found, with their values below. You can start with an empty sheet or with data already in it. You can also rearrange the columns (except column A which is used for the file name) and the macro will extract the field values into the correct column.

The code uses early binding of Word data types so you must set a reference to MS Word n.0 Object Library in Tools->References in the VBA editor.

Public Sub Extract_Word_Fields()

    Dim destSheet As Worksheet
    Dim r As Long, c As Long
    Dim fileSpec As String, folderPath As String
    Dim fileName As String
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdFF As Word.FormField
    Dim wdCC As Word.ContentControl
    fileSpec = "C:\folder\path\*.doc*"   'CHANGE FOLDER PATH AND FILE SPEC
    Set destSheet = Worksheets("Sheet1")  'CHANGE SHEET NAME
    With destSheet
        r = .Cells(.Rows.Count, 1).End(xlUp).Row
        If r = 1 Then .Range("A1").Value = "File"
        r = r + 1
    End With
    Set wdApp = New Word.Application
    folderPath = Left(fileSpec, InStrRev(fileSpec, "\"))
    fileName = Dir(fileSpec)
    While fileName <> vbNullString
        destSheet.Cells(r, 1).Value = fileName
        Set wdDoc = wdApp.Documents.Open(fileName:=folderPath & fileName, ReadOnly:=True)
        wdApp.Visible = True  'False to hide
        For Each wdFF In wdDoc.FormFields
            Debug.Print wdFF.Type, wdFF.Name, wdFF.Range.Text
            c = Get_Field_Column(wdFF.Name, destSheet)
            destSheet.Cells(r, c).Value = wdFF.Range.Text
        For Each wdCC In wdDoc.ContentControls
            Debug.Print wdCC.Type, wdCC.Title, wdCC.Range.Text
            c = Get_Field_Column(wdCC.Title, destSheet)
            destSheet.Cells(r, c).Value = Get_CC_Value(wdCC)
        wdDoc.Close SaveChanges:=False
        r = r + 1
        fileName = Dir


End Sub

Private Function Get_Field_Column(fieldName As String, destSheet As Worksheet) As Long

    Dim c As Variant
    With destSheet
        c = Application.Match(fieldName, .Range("B1", .Cells(1, .Columns.Count)), 0)
        If IsError(c) Then
            Get_Field_Column = .Cells(1, .Columns.Count).End(xlToLeft).Column
            If Not IsEmpty(.Cells(1, Get_Field_Column).Value) Then Get_Field_Column = Get_Field_Column + 1
            .Cells(1, Get_Field_Column).Value = fieldName
            Get_Field_Column = c + 1
        End If
    End With
End Function

Private Function Get_CC_Value(CC As Word.ContentControl) As Variant
    Select Case CC.Type
        Case wdContentControlText: Get_CC_Value = CC.Range.Text
        Case wdContentControlRichText: Get_CC_Value = CC.Range.Text
        Case wdContentControlDate: Get_CC_Value = CC.Range.Text
        Case wdContentControlCheckBox: Get_CC_Value = CC.Checked
        Case wdContentControlDropdownList: Get_CC_Value = CC.Range.Text
        Case wdContentControlComboBox: Get_CC_Value = CC.Range.Text
        Case Else
            MsgBox "Unexpected Content Control Type" & vbCrLf & vbCrLf & _
                   "Title=" & CC.Title & vbCrLf & _
                   "Type=" & CC.Type
    End Select
End Function


Retired Moderator
Aug 27, 2007
We have a new word document with about 16 form fields (a mix of legacy form fields and content controls). I'd like to be able to automatically compile the data from the 16 form fields into a spreadsheet.
First off, you should not be using formfields and content controls in the same document. They weren't designed to work together and trying to do so is a known source of problems.

That said, to extract both, see: http://www.msofficeforums.com/word-vba/19914-how-transferring-word-data-excel-sheet.html#post59665

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics