Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Extract Batch Form Data
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


This Microsoft Word Tips & Microsoft Word Help page shows you how you can process an entire folder (batch) of documents, extract data from the document form fields/or content controls and display the data collected in an Access database or Word document table.

Site Note IconNote: Extract Data from Document contains information on extracting data from single source document file.

For example, you may need to survey and collect data from your friends, co-workers, or clients. You first prepare an online (protected) form containing form fields for each survey question.

You send out the forms via e-mail and ask the participants to complete and return the forms. The easy part is over. Now the forms start pouring in and you need to collect and analyze the results. Read on!

Site Note IconNotes:
     1. To keep things simple, the questions for the example used here are 1) Participant Name, 2) Favorite Food, and 3) Favorite Color.

     2. Documents containing the appropriate content controls can be used in lieu of form fields in a protected documents with Word version 2007 and later.

The following illustration depicts the example form. It contains three formfields bookmarked "Name" "FavFood" and "FavColor."

extract data ff or cc 1

As you receive the returned files you should save them as numbered documents in a common folder on your hard drive. For this exercise, I have created a folder "D:\Batch\Tally Data Forms." Since only three form were returned my folder is pretty small.

extract data ff or cc 2

You will also need an Access database table with the appropriate fields to receive the data. I called my database Tally Data and for simplicity I saved it with my returned forms.  The database in "design" and "data sheet" view is shown below:

extract data ff or cc 3
Database (design view)

extract data ff or cc 4
Database (data sheet view)

The final step is to run a macro that opens each Word file in the batch directory and writes the formfield results into the appropriate database fields. Simply copy the macro and function shown below to a standard module in your VBA project.

VBA Script:
Option Explicit
Sub TallyDataInDataBase()
'Extract data from document form fields and store in Access database
Dim oPath As String
Dim FileArray() As String
Dim oFileName As String
Dim i As Long
'Requires reference to MS ActiveX Data Objects 2.8 Library or later
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim myDoc As Word.Document
Dim FiletoKill As String
  'Call function to get path to saved forms
  oPath = GetPathToUse
  If oPath = "" Then
    MsgBox "A folder was not selected"
    Exit Sub
  End If
  'Call function to create a processed forms folder
  CreateProcessedDirectory oPath
  'Identify files names
  oFileName = Dir$(oPath & "*.doc")
  ReDim FileArray(1 To 10000) 'User a number larger the expected number of files to process
  'Add file name to the array
  Do While oFileName <> ""
    i = i + 1
    FileArray(i) = oFileName
    'Get the next file name
    oFileName = Dir$
  Loop
  If i = 0 Then
    MsgBox "The selected folder did not contain any forms to process."
    Exit Sub
  End If
  'Resize and preserve the array
  ReDim Preserve FileArray(1 To i)
  Application.ScreenUpdating = False
  'Provide connection string.
  vConnection.ConnectionString = "data source=D:\Batch\Tally Data Forms\Tally Data.mdb;" & _
  "Provider=Microsoft.Jet.OLEDB.4.0;"
  'NOTE if using an ".accdb" format data base use the following connection string:
  'vConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
  & "Data Source=D:\Batch\Tally Data Forms\Tally Data.accdb;"
  vConnection.Open
  vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
  'Retrieve the data
  vConnection.Execute "DELETE * FROM MyTable"
  For i = 1 To UBound(FileArray)
    Set myDoc = Documents.Open(FileName:=oPath & FileArray(i), Visible:=False)
    FiletoKill = oPath & myDoc 'Identify the file to move after processing
    vRecordSet.AddNew
    With myDoc
      If .FormFields("Name").Result <> "" Then _
        vRecordSet("Participant Name") = .FormFields("Name").Result
      If .FormFields("FavFood").Result <> "" Then _
        vRecordSet("Favorite Food") = .FormFields("FavFood").Result
      If .FormFields("FavColor").Result <> "" Then _
        vRecordSet("Favorite Color") = .FormFields("FavColor").Result
      .SaveAs oPath & "Processed\" & .Name 'Save processed file in Processed folder
      .Close
      'File as been saved in the processed file folder. Delete it from the batch folder
      Kill FiletoKill 
    End With
  Next i
  vRecordSet.Update
  vRecordSet.Close
  vConnection.Close
  Set vRecordSet = Nothing
  Set vConnection = Nothing
  Application.ScreenUpdating = True
lbl_Exit:
  Exit Sub
End Sub

Private Function GetPathToUse() As Variant
Dim fDialog As FileDialog
  Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
  With fDialog
    .Title = "Select Folder containing the completed form documents to and click OK"
    .AllowMultiSelect = False
    .InitialView = msoFileDialogViewList
    If .Show <> -1 Then
      GetPathToUse = ""
      Set fDialog = Nothing
      Exit Function
    End If
    GetPathToUse = fDialog.SelectedItems.Item(1)
    If Right(GetPathToUse, 1) <> "\" Then GetPathToUse = GetPathToUse + "\"
  End With
lbl_Exit:
  Exit Function
End Function

Sub CreateProcessedDirectory(oPath As String)
'Requires Reference to Microsoft Scripting Runtime
Dim Path As String
Dim FSO As FileSystemObject
Dim NewDir As String
  Path = oPath
  Set FSO = CreateObject("Scripting.FileSystemObject")
  NewDir = Path & "Processed"
  If Not FSO.FolderExists(NewDir) Then
    FSO.CreateFolder NewDir
  End If
lbl_Exit:
  Exit Sub
End Sub

Site Note icon See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.

The results are now available in your Access database.

extract data ff or cc 5

Content controls introduced with Word 2007 are extremely versatile. They can be used in place of form fields in the example above. Instead of creating the form fields and protecting the document you simply add the three necessary plain text content controls as shown:

extract data ff or cc 6

The code necessary to process the content controls is similar to the code shown above.  That code and other code samples (including code to extract data to a Word document table in lieu of a data base) is available in the demonstration and examples document you can download here: Tally Data

Site Note IconThe demonstration document "Tally Data.dot" is a Word 2003 template document including a small custom toolbar. The toolbar propagate in the Word 2007/2010 Add-Ins tab, Custom Toolbars group.

extract data ff or cc 7

Word 2003 Toolbar

extract data ff or cc 8
Word 2007/2010 Custom Toolbars group

The first command "Extract to Database" executes a procedure containing the code shown above. The second command "Extract to Word Table" executes one of several included procedures to display the extracted data in a Word table. The third command "Extract CC Data to Database" executes codes similar to the code shown above but modified to process content controls vice form fields.

I am including several variations of methods I have used as a record of the effort to get my head around the processes illustrated.

Credit for parts of the code and much of what I have learned putting this page together is due to outstanding assistance of some of the Word MVPs and regular newsgroup contributors. Special thanks is due Doug Robbins for the Access piece, Jezebel for the Class piece, and Tony Jollans/Jonathan West for the array pieces. Thank you all!!

That's it! I hope you have found this tips page useful and informative.

Share

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo