Extract data from Word Form responses to Excel Spreadsheet

[pdalton]

Disclaimer: My only prior experience with the VBA Editor has been minor editing of Word Macros after they have been recorded. I know I'm in over my head and badly in need help, and I don't think I can do what we need done without using VBA -- of course, please advise me if there is an easier way.

Our group is beginning to plan for a 30th high school reunion and the very first thing we must do is to update information about each of our 500+ classmates so we can effectively communicate with them.

he school gave us an Excel spreadsheet containing over 30 fields for information about our class members (not all of which contain data). Some of the alumni information has been updated, most has not, and we cannot be sure of the accuracy of anything other than each person's name at graduation. Of course, many female classmates' last names have changed, as has the address, phone #, etc. for almost everyone. So we need to verify and update the informaton for everyone.

We have e-mail addresses for a number of these people, each of whom we hope will have e-mail addresses for others. What we hope to do is send a form that can be (1) completed by the recipient and e-mailed back to us and (2) forwarded by that recipient to his/her e-mail contacts to be completed, returned to us, and again forwarded along to others. We wish to circulate a "form" so that, when returned, the data can more easily be extracted and reliably placed into a spreadsheet.

While it would be nice to be able to have something that would cause the imported data to automatically update the old data on the existing spreadsheet, we would be happy just to be able to pull the data from the forms and create a new spreadsheet with the correct fields.

It seemed to me that the most ubiquitous (other than pure text) file format is Word ( I don't think I can expect nearly as many people to have or use Excel, for example). So, I have created a Word Template to serve as a data collection form that will only allow the user to put data into specific fields. This is something we can e-mail. What I cannot figure out is:

1. How to automate getting to that data (either file by file, or from a group all at once) and

2. How to move that data into an Excel spreadsheet.

I can not imagine that someone has not already developed a way to accomplish this, but I do not even know where to go look. Any help would be greatly appreciated.

I have attached the form I created, in case that helps.

- Paul Dalton

[mdmackillop]

Hi Paul,
Welcome to VBAX
This can certainly be done, but your attachment is missing.
Regards
MD

[pdalton]

>> your attachment is missing

Hmmm. I thought I had dome the steps correctly.

I've tried again &, this time, there is data in the form.

- Paul

[mdmackillop]

A bit basic but something like this. (Save it in the same folder as your documents)

[geekgirlau]

Word allows you to save form data in a comma-delimited text file, so you could save all returned documents in a single folder, loop through them and save the form data.

[vba]
ActiveDocument.SaveAs FileName:="Tom Johnson.txt", FileFormat:= _
wdFormatText, SaveFormsData:=True
[/vba]

[pdalton]

Thanks! It took me a bit to figure out what to do with it, so I ended up entering the filename into the blank several different ways -- what worked was putting in the filename without an extension. I assume this was as intended.

A couple of problems that occurred, however, are that the data populates a column, instead of a row, AND it seems to always want to populate the SAME row, so that processsing a second file overwrites the data from the first file.

Can you tell me how to edit it to:

1. cause it to populate a row (instead of a column); and

2. have it index to the next empty row before inserting the data?

Also, and this may be asking too much, but it also would be helpful if, instead of prsenting a blank into which teh filename must be typed correctly (that requires me to go look at the file to assure that I get the spelling right), the program would open an Explorer window listing all the files in that directory and then allow the user to simply click on the desired file and the program would then process the selected file? Is that do-able?

Thanks for your help.

- Paul

[mdmackillop]

Hi Paul,
The Browse function had been added to my edited version above. This addresses the Rows issues as well.
Regards
MD

[lucas]

Another slick solution Malcolm.

[mdmackillop]

Thanks Steve.
Greatly simplified by Paul's nice form, and my eventual recollection of FileDialog!
[vba]
Option Compare Text
Option Explicit

Sub DataFrom()

'Remember: this code requires a reference to the Word object model

Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim fName As String
Dim i As Long, Rw As Long

ChDir ActiveWorkbook.Path

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Word", "*.doc", 1
.Show
On Error GoTo Exits
fName = .SelectedItems(1)
End With

Set wdDoc = wdApp.Documents.Open(fName)

Rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(Rw, 1) = Cells(Rw - 1, 1) + 1
i = 1
For Each f In wdDoc.FormFields
i = i + 1
On Error Resume Next
Cells(Rw, i) = f.Result
Next
Exits:
End Sub

[/vba]

[pdalton]

WOW!!! This is SO ELEGANT!!!

It does precisely what was needed, including intelligently making sure other data does not get overwritten and providing the browser access so that we only need to "point & click" to process a file!

THANK YOU VERY MUCH !!!

- Paul

[yogin]

Hello, I would word template that are saved with multiple file name in a folder, and I need to export only the data fields I need into an excel spreadsheet. Is this possible? sorry i am not very good with programming so can some one please help me with this? I can attach the template and excel file where i need this information and folder location if needed?

[mdmackillop]

You can attach both files by zipping them together. See Manage Attachments in the Go Advanced reply section.

[yogin]

Thanks mdmackillo. i have attached the 2 files i need to combine. the template is same but it has been saved under multiple file name with different details and these file names keep increasing depending on the invoice. These files are saved in z:\NSW

[Jens]

Hi,
I'm a novice in VBA, and I am not sure how to make this work.

I have to extract data from about 1000+ Word forms and place in Excel for analysis.

I use Office 2011 for Mac which supports VBA.

I would be extremely grateful for step-by-step guidance on how to implement this code.

Thanks Steve.
Greatly simplified by Paul's nice form, and my eventual recollection of FileDialog!
[vba]
Option Compare Text
Option Explicit

Sub DataFrom()

'Remember: this code requires a reference to the Word object model

Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim fName As String
Dim i As Long, Rw As Long

ChDir ActiveWorkbook.Path

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Filters.Add "Word", "*.doc", 1
.Show
On Error GoTo Exits
fName = .SelectedItems(1)
End With

Set wdDoc = wdApp.Documents.Open(fName)

Rw = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(Rw, 1) = Cells(Rw - 1, 1) + 1
i = 1
For Each f In wdDoc.FormFields
i = i + 1
On Error Resume Next
Cells(Rw, i) = f.Result
Next
Exits:
End Sub

[/vba]

[macropod]

Hi Jens,

Have you tried using the code? If so, with what result?

If you don't know how to install the code (which goes into an Excel workbook) see, for example:
http://dmcritchie.mvps.org/excel/install.htm
http://www.teachexcel.com/exceltips/...adsheet_3.html

[stephen_]

Another option that works for me;

Add VBA below; (Open VBA editor by Alt & F11)
[vba]
Sub CollateForms()
Dim myPath As String
Dim myWord As New Word.Application
Dim myDoc As Word.Document
Dim myField As Word.FormField
Dim n As Long, m As Long
Dim fs, f, f1, fc
Range("A2").Select
myPath = InputBox("Path?")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
m = 0
For Each f1 In fc
n = 0
Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
For Each myField In myDoc.FormFields
ActiveCell.Offset(m, n).Value = myField.Result
n = n + 1
Next
myDoc.Close wdDoNotSaveChanges
m = m + 1
Next
Set myField = Nothing
Set myDoc = Nothing
Set myWord = Nothing
End Sub[/vba]


In VBA click on Tools > References > Click for Microsoft Word Object Library.

Close VBA

Now run the macro

Click on cell A2 > add path to either file or folder with collection of same files (word forms)

Hopefully the magic will work.

Make sure your forms are the same format otherwise the rows will contain different fields.

Hope this helps someone out there searching even though the original post was 6 years ago!

[gahamchoi]

Another option that works for me;

Add VBA below; (Open VBA editor by Alt & F11)
[vba]
Sub CollateForms()
Dim myPath As String
Dim myWord As New Word.Application
Dim myDoc As Word.Document
Dim myField As Word.FormField
Dim n As Long, m As Long
Dim fs, f, f1, fc
Range("A2").Select
myPath = InputBox("Path?")
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(myPath)
Set fc = f.Files
m = 0
For Each f1 In fc
n = 0
Set myDoc = myWord.Documents.Open(myPath & "\" & f1.Name)
For Each myField In myDoc.FormFields
ActiveCell.Offset(m, n).Value = myField.Result
n = n + 1
Next
myDoc.Close wdDoNotSaveChanges
m = m + 1
Next
Set myField = Nothing
Set myDoc = Nothing
Set myWord = Nothing
End Sub[/vba]


In VBA click on Tools > References > Click for Microsoft Word Object Library.

Close VBA

Now run the macro

Click on cell A2 > add path to either file or folder with collection of same files (word forms)

Hopefully the magic will work.

Make sure your forms are the same format otherwise the rows will contain different fields.

Hope this helps someone out there searching even though the original post was 6 years ago!

Hi stephen,

I tried with your guide but after inputing path, but it not worked. A "Micorosft Visual Basic" msgbox appeared with 2 option of "OK" and "Help". Can you help me with this. Tks

[macropod]

What you're seeing is an Input box - not a Message box - with a prompt, saying 'Path?

Simply change:
myPath = InputBox("Path?")
to:
myPath = InputBox("What is the File Path for the Word documents that you want to process?")
and see if you can work out the rest.

[gahamchoi]

What you're seeing is an Input box - not a Message box - with a prompt, saying 'Path?

Simply change:
myPath = InputBox("Path?")
to:
myPath = InputBox("What is the File Path for the Word documents that you want to process?")
and see if you can work out the rest.

Yes, the Input box appear first, but after I inputting the path of folder, an erro message box with a promt appear

[macropod]

What error message do you get, what code line is highlighted and, if you move the mouse over the highlighted line, what variable parameters pop up? What document is opened? What kinds of formfields does it have?