A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Microsoft Word Help
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

extract data from multiple Word forms into Excel



 
 
Thread Tools Display Modes
  #1  
Old September 1st 05, 06:53 AM
bbbitt
external usenet poster
 
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?
Ads
  #2  
Old September 1st 05, 07:16 PM
Doug Robbins
external usenet poster
 
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  
Old September 1st 05, 09:46 PM
wjgaddis
external usenet poster
 
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  
Old September 1st 05, 10:33 PM
bbbitt
external usenet poster
 
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?




  #5  
Old September 2nd 05, 08:33 PM
Doug Robbins
external usenet poster
 
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?






  #6  
Old November 21st 06, 08:06 PM posted to microsoft.public.word.docmanagement
Julie
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  
Old October 18th 07, 01:08 PM posted to microsoft.public.word.docmanagement
CherylH
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?




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
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 04:17 PM


All times are GMT +1. The time now is 02:48 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.