word to excel

O

over_worked_Rob

Can any one help me!!!!!!

Right basically I need to put together a word form that will go out t
staff, they will fill out the form and send it back for muggings her
to compile and produce a statistical report. I am only asking a fe
questions but I am expecting around 5000 replies :eek: , s
understandably I am looking for a way to automate the process.

The word from will consist a of form elements taken from the form
panel. They are named:

Text box 1, Text box 2, Text box 3, Text box one, Dropdown 1, Dropdow
2, Dropdown 3

All the word files will have a unique file name. form1,form2,form3……

They will be mailed back to me and I would save them to a folde
“returns”

I then need Excel to interrogate all word files in this folder, an
pull the information from the form elements into a list:

Text box 1 into A1
Text box 2 into B1
Text box 3 into C1
Dropdown 1 into D1
Dropdown 2 into E1
Dropdown 3 into F1

The data from the next word file would have to go into row 2 and th
next row 3 and so on giving me a list of raw data to work on.

Is this possible? Can it be don?

Yours hopefully :rolleyes:
Over worked ro
 
D

Debra Dalgleish

You can save all the Word files in one folder, as field text only, and
import the data into Excel.

1. Save all the completed Word forms as text (in Word, choose
Tools>Options, and on the Save tab, add a check mark to
'Save data only for forms')
2. Place all the files in one folder ("c:\TestFiles\" in this example)
3. Open the Excel file in which you want to collect the data
("MyImports.xls" in this example, on sheet "FormData")
4. Run a macro similar to the following, which loops through all the
files in the designated folder, and copies the form data to the
master file

Note: To customize it for your text files, turn on the macro recorder in
Excel.
Choose File>Open
Set 'Files of Type' to Text, and select one of the text files.
In the Text Import Wizard, choose Delimited, and click Next
Choose Tab, click Finish.
Turn off the macro recorder.

Use the FieldInfo from the recorded code, to replace the FieldInfo in
the sample code.

'============================
Sub ImportTextFiles()
'code for looping through file in folder
'stolen from post by Dave Peterson
Dim myFiles() As String
Dim i As Integer
Dim myFile As String
Dim myFolder As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim r As Long
'this sheet collects the form data
Set ws = Workbooks("MyImports.xls").Sheets("FormData")

Application.DisplayAlerts = False
Application.ScreenUpdating = False

myFolder = "c:\TestFiles\"
If Right(myFolder, 1) <> "\" Then
myFolder = myFolder & "\"
End If

myFile = Dir(myFolder & "*.txt")

If myFile = "" Then
MsgBox "no text files found"
Exit Sub
End If

Do While myFile <> ""
i = i + 1
ReDim Preserve myFiles(1 To i)
myFiles(i) = myFile
myFile = Dir()
Loop

For i = LBound(myFiles) To UBound(myFiles)
r = ws.Range("A1").End(xlDown).Offset(1, 0).Row
Set rng = ws.Range("A" & r)
Workbooks.OpenText Filename:=myFolder & myFiles(i), _
Origin:=437, StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
Set wb = ActiveWorkbook
ActiveSheet.Rows("1:1").Copy Destination:=rng
wb.Close savechanges:=False
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
'============================
 
Top