How to extract data from Word Form into Excel

[arunjoshi]

I have created a Form named abc.dot in Word. A respondent fills in the
form, and saves the file as abc-01.doc and sends it to me. And
likewise, from other respondents I get files named abc-02.doc,
abc-03.doc, etc.

All these files abc-01.doc, abc-02.doc, abc-03.doc, etc. have been
saved in one folder.

I would like to have an Excel file contain the responses given in each
of these files.

Can anyone please suggest a macro to do this?

Many thanks.

 

[Martin]

The following should do it - copy it into an Excel module and then put your
cursor into cell A2 of a blank sheet before running it (it will ask you for
the full path to the folder in which your Word documents are). You will also
need to tick Microsoft Word Object Library in Tools, References in the VB
Editor:

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

(if you've got any tick boxes, ticks get changed to 1, non-ticks to 0)