Tech Support Guy banner
Status
Not open for further replies.

Solved: Exporting Repeated Forms In One Word doc To Excel

2K views 10 replies 2 participants last post by  Phillipe 
#1 ·
Hello,

Relating to a previous post:
"Exporting Word Form Data to Excel"
http://forums.techguy.org/business-applications/257309-exporting-word-form-data-excel.html
regarding the same form in many word docs esported into an excel sheet

My question is whether its possible to export repeated forms in ONE word doc so they collate into 5 columns.
(Would like the first 2 columns and 2 rows left empty :rolleyes:)

My form look in word is thus:

Story:
Image: 01
Display-image:
Courtesy:
Caption:

Story:
Image: 02
Display-image:
Courtesy:
Caption:

Story:
Image: 03
Display-image:
Courtesy:
Caption:

etc

Sample.doc is below


This macro obtained from:
"Solved: Export Word Form to Excel"
http://forums.techguy.org/business-applications/1062655-solved-export-word-form-excel.html
works well :up: for the same form in many word docs exported to excel:

Code:
Dim vField As FormField
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer
 
Sub AddFormFields()
 
If ActiveSheet.UsedRange.Count = 1 Then
vLastRow = 1
Else
vLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
End If
vColumn = 1
 
Set fso = New Scripting.FileSystemObject
 
Set fsDir = fso.GetFolder _
("C:\Documents and Settings\User\Desktop\CIKA 2004\Members\UnProcessed")
 
Set wdApp = New Word.Application
wdApp.Visible = True
 
For Each fsFile In fsDir.Files
 
wdApp.Documents.Open (fsFile)
 
Set myDoc = wdApp.ActiveDocument
 
For Each vField In wdApp.Documents(myDoc).FormFields
 
vField.Select
 
vValue = vField.Result
 
Workbooks("Members.xls").Activate
Cells(vLastRow, vColumn).Select
 
If vField.Type = 71 Then
 
Select Case vField.Name
 
Case "Check1"
vColumn = vColumn - 1
If vField.Result = "1" Then
ActiveCell.Value = "YES"
End If
 
Case "Check2"
If vField.Result = "1" Then
ActiveCell.Value = "NO"
End If
 
End Select
 
Else
 
ActiveCell.Value = vValue
End If
 
vColumn = vColumn + 1
 
Next
 
vColumn = 1
vLastRow = vLastRow + 1
 
vFileName = wdApp.ActiveDocument.Name
 
wdApp.ActiveDocument.Close
 
Name fsFile As _
"C:\Documents and Settings\User\Desktop\CIKA 2004\Members\Processed\" & vFileName
 
Next
 
wdApp.Quit
End Sub
Is it possible? :eek:.
.
 

Attachments

See less See more
2
#9 ·
.
Hi Rollin,
Thanks for your thought, time and response.
I think you might be sorry you asked the question.

The word doc has some general text and then has a form. Let's say that within the form area we have a 'formlet' that is repeated (unchanged) down the page any number of times. Each formlet has the same 5 input boxes. I 'word protect' the document so that the structure cannot be altered.

The recipient of the document can then enter an item number (any item number) into one of the designated boxes of a formlet. The recipient can answer Yes, No or Undecided to a question in another box of the formlet. The recipient can then enter any amount of text into any of the other three boxes of the formlet. Typing into any of the boxes is optional. There may be some, if not all of the boxes in a formlet left unanswered or untouched. The recipient repeats the process in other formlets. There will probably be some formlets left untouched.

The recipient returns the document to me. (See <sample.doc> below). I unprotect the document and pass it through a spreadsheet macro. Once it passes through the macro, it is hoped that the answers to each formlet appear on a separate row down the spreadsheet (that is, contained within 5 columns). There may be some cells appear with no data, indeed some rows appear with no data as well. (See <sample.xls> below).

The macro does not have to pass a second word form to add to the list already in the spreadsheet from a first pass. The intended action is merely a one off - one word form to an empty work book.

Then what I have done is created a function copy of the data on sheet 1 into sheet 2 with cell and row spacing of my choosing. I can simply do a 'delete empty rows' macro there too.

Is there a setting in the macro that can save the data in a sheet other than sheet 1 by say the name given to the sheet?

Thanks for your patience. I now realize that you are not a mind reader!
.
 

Attachments

#10 ·
I'm still interested in a solution to this if possible.

Reading around this amazing forum, I notice new members would say 'hello' which I didn't. So a mighty big hello to everyone. I love your work. Maybe one day I can help but I am very much the 'student' in here.

Cheers for now.
.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top