Extract Word Form Field Data to Excel
Results 1 to 3 of 3

Thread: Extract Word Form Field Data to Excel
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Location
    Red Deer, AB
    Posts
    51
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract Word Form Field Data to Excel

    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.

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,807
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Extract Word Form Field Data to Excel

    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.

    Code:
    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

  3. #3
    Retired Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,279
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Extract Word Form Field Data to Excel

    Quote Originally Posted by cgfrank View Post
    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-v...html#post59665
    Cheers
    Paul Edstein
    [MS MVP - Word]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •