Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to extract data from Word Form into Excel
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. -- arunjoshi ------------------------------------------------------------------------ arunjoshi's Profile: http://www.excelforum.com/member.php...fo&userid=8846 View this thread: http://www.excelforum.com/showthread...hreadid=526715 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to extract data from Word Form into Excel
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) "arunjoshi" wrote: 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. -- arunjoshi ------------------------------------------------------------------------ arunjoshi's Profile: http://www.excelforum.com/member.php...fo&userid=8846 View this thread: http://www.excelforum.com/showthread...hreadid=526715 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
best way to put list data from Excel to a Word form | Excel Discussion (Misc queries) | |||
Extract data from form | Excel Discussion (Misc queries) | |||
Extract MS Excel Data embedded in MS Word | Excel Discussion (Misc queries) | |||
Extract data (not in table) from Word to Excel | Excel Discussion (Misc queries) | |||
Extract MS word data to MS Excel | Excel Programming |