Aggregate Batch Data from many word docs into an Excel (i.e.Check Boxes, Text Form Fields)

Hey Guys,

This one might be a bit of a bruiser but any help is appreciated.

I have lots of word documents 75-100 that are a 3 page form that our staff completed and emailed to me. My job is to collect the forms from each of the staff and aggregate the data from each form that people filled out and put it all into a master 3-page form. The good news is each form is the same format, just the information checked and entered is specific to the individual that filled it out. My deadline is the end of the month, so at some point if I can't figure this one out, I am going to print and do it manually. I have always been of the opinion, if something has been entered electronically in an organized fashion, there MUST be a way to get it into excel.

I was thinking, is there is some way to pull the data from each of the word documents (in this case, people are checking check boxes and entering names/free text into Text Form Fields) and drop it into an excel spreadsheet, it would make my life easier. If it's all in an excel document, I can calculate and sort much easier. The alternative is to print all 100 or so and tally by hand...such an ill-fated task awaits me in purgatory - Not to mention all the trees that will suffer.

If the final spreadsheet, if each row is designated for each person that filled out the form then each column could be a "cell" from the table:
- for the check boxes - enter cell value 1 for a check yes and 0 for check no
- for the free text - enter the value that the person typed into the check box

Attached, is a very simple arrangement of how most of the form looks - each cell in the table has content that I would like to separate by column


Form Sample.docx

On a tangent - is there some way in excel to reference (I don't know, V_Lookup?) a single "Cell" in a microsoft word document's table? At least it would save me re-typing everything. Is there some way to convert a bunch into csv files and upload to excel that way?

The following Excel macro assumes your form uses Content Controls:

Please Login or Register  to view this content.

If your form uses formfields (you should not be using content controls and formfields in the same form), change:
Dim CCtrl As Word.ContentControl
to:
Dim FmFld As Word.FormField

Change:
strFile = Dir(strFolder & "\*.docx", vbNormal)
to:
strFile = Dir(strFolder & "\*.doc", vbNormal)

Change:
For Each CCtrl In .ContentControls
to:
For Each FmFld In .FormFields

Change:
WkSht.Cells(i, j) = CCtrl.Range.Text
to:
WkSht.Cells(i, j) = FmFld.Result

Originally Posted by Chrisdudley7

What are Content Controls? The FormFields are what they type the free text into right? Are content controls where they select an option from many?

Content controls and formfields can both be used for inputting data, selecting from dropdown options, and checking checkboxes. Both are accessed via the Controls group on Word's Developer tab. The main ones you'll see there are the content controls; formfields are accessed via the 'Legacy Tools' icon. You need to understand what kind of forms you're working with before you'll be able to progress.

I need some help with this - I don't do macros often. What aspects of the Macro do I need to change?

If you're using content controls, you don't need to change anything. If you're using formfields, you'll need to make the changes indicated in my previous post.

1. I opened Visual Basic
2. Inserted a new module
3. Paste the information in
4. what do I do to go from there

Did you do this in Excel?

Word object model is a blank version of the form right?

Wrong. It's a reference, set via Tools|References in the VBA IDE. You open that, scroll down to where the list refers to 'Microsoft Word 14.0 Object Library' and check that option.

When you've done that it's just a matter of selecting the worksheet you want the data to appear on, then running the 'GetFormData' macro. The macro includes its own browser, so all you need do is use that to select the folder to process. All .docx files in that folder will be processed. If you use .doc files and not .docx, change the .docx reference in the code to .doc.

Cool, thanks for your help! once I fixed the 'Microsoft Word 14.0 Object Library', everything worked smoothly with the Content Controlled macro.

For the formfields, I replaced in the formfield specific elements but did not have success - I have a question: Is this macro designed to pull form fields were there is a drop down menu of options to pick (e.g. Pick a color --> Blue Red Green)? When I run the macro, nothing spits out and I am wondering if it is because the form fields in this checklist are not the type that people select an answer from a finite list. These are free text form field where you can type in anything.

I am attaching a picture of what the form fields look like on the form - people just left click in the grey (the it turns blue), and then they type in their custom text.

formfields.jpg

If you're using formfields and you make the code changes as indicated, the macro will pull in the data from every formfield in the body of the document (you can't put them in headers & footers anyway), be it a text formfield, a checkbox formfield or a dropdown formfield.