Extract Word Form Field Data to Excel

[cgfrank]

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.

 

[John_w]

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
        Next
        
        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)
        Next
        
        wdDoc.Close SaveChanges:=False
        
        r = r + 1
        fileName = Dir
    Wend

    wdApp.Quit

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
        Else
            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

 

[Macropod]

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