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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".


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

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...