Reply
 
Thread Tools Display Modes
  #1   Report Post  
bbbitt
 
Posts: n/a
Default extract data from multiple Word forms into Excel

I have forms that have been filled out in Microsoft Word 2000. I would like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?
  #2   Report Post  
Doug Robbins
 
Posts: n/a
Default

If you put all of the documents into a folder by themselves and then run the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?



  #3   Report Post  
wjgaddis
 
Posts: n/a
Default

I'm a novice programmer as well and can really use the code provided . . . in
fact, I already have. However, I do get an error because the documents I am
opening have tables instead of FormFields (not even sure what these are).
How do I extract data from a table?

"Doug Robbins" wrote:

If you put all of the documents into a folder by themselves and then run the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?




  #4   Report Post  
Doug Robbins
 
Posts: n/a
Default

Take a look at the thread "Extracting Text from Word Document" in the
microsoft.public.word.vba.general newsgroup:




--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"wjgaddis" wrote in message
...
I'm a novice programmer as well and can really use the code provided . . .
in
fact, I already have. However, I do get an error because the documents I
am
opening have tables instead of FormFields (not even sure what these are).
How do I extract data from a table?

"Doug Robbins" wrote:

If you put all of the documents into a folder by themselves and then run
the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents
containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a
single
Excel spreadsheet. Can it be done?






  #5   Report Post  
bbbitt
 
Posts: n/a
Default

Thanks, Doug. The macro worked perfectly. -Brenda

"Doug Robbins" wrote:

If you put all of the documents into a folder by themselves and then run the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?






  #6   Report Post  
Posted to microsoft.public.word.docmanagement
Julie Julie is offline
external usenet poster
 
Posts: 57
Default extract data from multiple Word forms into Excel

Doug:
Thanks for the post. I ran your macro, but saved the result as a text file,
then imported it into an Access table. Works great!
--
Julie


"Doug Robbins" wrote:

If you put all of the documents into a folder by themselves and then run the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?




  #7   Report Post  
Posted to microsoft.public.word.docmanagement
CherylH CherylH is offline
external usenet poster
 
Posts: 3
Default extract data from multiple Word forms into Excel

Hi Doug -
I am not very familiar with macros, however I am in desperate need of doing
this also!! Can you tell me where I place the macro?
Thanks in advance!
Cheryl

"Doug Robbins" wrote:

If you put all of the documents into a folder by themselves and then run the
following macro, selecting that folder when prompted to do so, it should
create a new document with a paragraph for each of the documents containing
the data from the formfields with each piece of data separated by a tab.

You can then copy and paste that information into Excel.

Dim mydoc As Document
Dim target As Document
Dim i As Long

'let user select a path
With Dialogs(wdDialogCopyFile)
If .Display() -1 Then Exit Sub
MyPath = .Directory
End With

'strip quotation marks from path
Set target = Documents.Add
If Len(MyPath) = 0 Then Exit Sub

If Asc(MyPath) = 34 Then
MyPath = Mid$(MyPath, 2, Len(MyPath) - 2)
End If

'get files from the selected path
'and insert them into the doc
MyName = Dir$(MyPath & "*.*")
Do While MyName ""
Set mydoc = Documents.Open(MyPath & MyName)
For i = 1 To mydoc.FormFields.Count - 1
target.Range.InsertAfter mydoc.FormFields(i).result & vbTab
Next i
target.Range.InsertAfter mydoc.FormFields(i).result & vbCr
mydoc.Close wdDoNotSaveChanges
MyName = Dir$
Loop

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"bbbitt" wrote in message
...
I have forms that have been filled out in Microsoft Word 2000. I would
like
to extract the data from all the forms in a folder and put it in a single
Excel spreadsheet. Can it be done?




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Please give us REVEAL CODES like WORD PERFECT not reveal codes in. Rachel King Microsoft Word Help 38 August 21st 09 09:54 PM
Making Word do something that Wordperfect can do NarniaUK New Users 4 May 1st 05 10:44 PM
Envelope Address GR New Users 5 April 24th 05 09:48 PM
In Word, how can I see all files (*.*) in "save as"? citizen53 New Users 8 April 4th 05 04:56 PM
creating forms Fluffypink Microsoft Word Help 4 March 9th 05 05:17 PM


All times are GMT +1. The time now is 07:57 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"