Extract data from Word Form responses to Excel Spreadsheet
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
Extract data from Word Form responses to Excel Spreadsheet
>> 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
How to use extraction from Word forms data to Excel on Mac Office 2011?
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.
Quote:
Originally Posted by 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]