[Mark Lewis]

Is it possible to extract data from user entered fields into Access or
Excel?

Thanks

Mark

[Graham Mayor]

See http://gregmaxey.mvps.org/Extract_Form_Data.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



Mark Lewis wrote:
Is it possible to extract data from user entered fields into Access or
Excel?

Thanks

Mark

[CyberTaz]

Graham's reply provides an excellent method - especially if this is
something you need to do on a repetitive basis. Just on the off chance that
it may be a "one time thing" here's another option that might be useful but
less entailed since you don't have to deal with code.

In Word 2003 or prior Tools Options - Save provides a setting to Save Data
Only for Forms. In 2007 it's in Office Button Advanced Preserve Fidelity
when Sharing this Document - Save Form Data as Delimited Text File.

The resultant save can then be imported into Excel or Access.

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/7/08 5:58 PM, in article , "Mark
Lewis" wrote:

Is it possible to extract data from user entered fields into Access or
Excel?

Thanks

Mark

[Graham Mayor]

It shouldn't be too hard to produce the results of this method in a single
file for a batch of documents ... I feel another web page coming on

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



CyberTaz wrote:
Graham's reply provides an excellent method - especially if this is
something you need to do on a repetitive basis. Just on the off
chance that it may be a "one time thing" here's another option that
might be useful but less entailed since you don't have to deal with
code.

In Word 2003 or prior Tools Options - Save provides a setting to
Save Data Only for Forms. In 2007 it's in Office Button Advanced
Preserve Fidelity when Sharing this Document - Save Form Data as
Delimited Text File.

The resultant save can then be imported into Excel or Access.

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/7/08 5:58 PM, in article ,
"Mark Lewis" wrote:

Is it possible to extract data from user entered fields into Access
or Excel?

Thanks

Mark

[CyberTaz]

Have at it my friend - **far** better you than I!

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/8/08 9:53 AM, in article , "Graham
Mayor" wrote:

It shouldn't be too hard to produce the results of this method in a single
file for a batch of documents ... I feel another web page coming on

[Mark Lewis]

Graham & Bob,

Thanks for your help, I a novice at Marco bits but will give it a try, I
like the process where the user just presses the button and the jobs done!

Will let you know how I get on.

Regards

Mark
"CyberTaz" wrote in message
.. .
Have at it my friend - **far** better you than I!

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/8/08 9:53 AM, in article ,
"Graham
Mayor" wrote:

It shouldn't be too hard to produce the results of this method in a
single
file for a batch of documents ... I feel another web page coming on

[Graham Mayor]

It's a bit Heath Robinson, but the following macro appears to do the job of
creating a comma delimited text file TargetDoc.txt from a folder full of
similar completed form documents. I would have posted it earlier, but there
was a persistent minor error that I couldn't get my head around, so I went
for the lure of a sunny day, deserted roads and a fast car instead, before
it gets too hot to enjoy such pleasures. The web page may take a little
longer

Sub ExtraData from forms()
Dim DocList As String
Dim DocDir As String
Dim DataDoc As Document
Dim TargetDoc As Document
Dim fDialog As FileDialog

Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
On Error GoTo err_FolderContents
With fDialog
.Title = "Select Folder containing the completed form documents and
click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show -1 Then
MsgBox "Cancelled By User"
Exit Sub
End If
DocDir = fDialog.SelectedItems.Item(1)
If Right(DocDir, 1) "\" Then DocDir = DocDir + "\"
End With

If Documents.Count 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If
Application.ScreenUpdating = False
DocList = Dir$(DocDir & "*.doc")
ChangeFileOpenDirectory DocDir
Do While DocList ""
Documents.Open DocList
With ActiveDocument
.SaveFormsData = True
.SaveAs FileName:="DataDoc.txt", _
FileFormat:=wdFormatText, _
SaveFormsData:=True
.Close SaveChanges:=wdDoNotSaveChanges
End With
Set DataDoc = Documents.Open("DataDoc.txt", False)
With Selection
.WholeStory
.Copy
End With
DataDoc.Close SaveChanges:=wdDoNotSaveChanges
Set TargetDoc = Documents.Open("TargetDoc.txt", False)
With Selection
.EndKey Unit:=wdStory
.Paste
End With
TargetDoc.Close SaveChanges:=wdSaveChanges
DocList = Dir$()
Loop
Application.ScreenUpdating = True
Documents.Open "TargetDoc.txt", False
Exit Sub
err_FolderContents:
MsgBox Err.Description
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



CyberTaz wrote:
Have at it my friend - **far** better you than I!

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/8/08 9:53 AM, in article ,
"Graham Mayor" wrote:

It shouldn't be too hard to produce the results of this method in a
single file for a batch of documents ... I feel another web page
coming on

[Mark Lewis]

Well, going fine at the moment just duplicating some of the code as it will
have 129 items on the form!

Found a little error on the web page
vRecordSet!Name = .FormFields("Text1").Result
should it be
vRecordSet("Name") = .FormFields("Text1").Result
Simple for some who doesn't know about macros to solve though, that's me!.

I thought it would be a simple 5 minute job!

Thanks ALL


"Graham Mayor" wrote in message
...
It's a bit Heath Robinson, but the following macro appears to do the job
of creating a comma delimited text file TargetDoc.txt from a folder full
of similar completed form documents. I would have posted it earlier, but
there was a persistent minor error that I couldn't get my head around, so
I went for the lure of a sunny day, deserted roads and a fast car instead,
before it gets too hot to enjoy such pleasures. The web page may take a
little longer

Sub ExtraData from forms()
Dim DocList As String
Dim DocDir As String
Dim DataDoc As Document
Dim TargetDoc As Document
Dim fDialog As FileDialog

Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
On Error GoTo err_FolderContents
With fDialog
.Title = "Select Folder containing the completed form documents and
click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show -1 Then
MsgBox "Cancelled By User"
Exit Sub
End If
DocDir = fDialog.SelectedItems.Item(1)
If Right(DocDir, 1) "\" Then DocDir = DocDir + "\"
End With

If Documents.Count 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If
Application.ScreenUpdating = False
DocList = Dir$(DocDir & "*.doc")
ChangeFileOpenDirectory DocDir
Do While DocList ""
Documents.Open DocList
With ActiveDocument
.SaveFormsData = True
.SaveAs FileName:="DataDoc.txt", _
FileFormat:=wdFormatText, _
SaveFormsData:=True
.Close SaveChanges:=wdDoNotSaveChanges
End With
Set DataDoc = Documents.Open("DataDoc.txt", False)
With Selection
.WholeStory
.Copy
End With
DataDoc.Close SaveChanges:=wdDoNotSaveChanges
Set TargetDoc = Documents.Open("TargetDoc.txt", False)
With Selection
.EndKey Unit:=wdStory
.Paste
End With
TargetDoc.Close SaveChanges:=wdSaveChanges
DocList = Dir$()
Loop
Application.ScreenUpdating = True
Documents.Open "TargetDoc.txt", False
Exit Sub
err_FolderContents:
MsgBox Err.Description
End Sub


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org



CyberTaz wrote:
Have at it my friend - **far** better you than I!

Regards |:)
Bob Jones
[MVP] Office:Mac



On 6/8/08 9:53 AM, in article ,
"Graham Mayor" wrote:

It shouldn't be too hard to produce the results of this method in a
single file for a batch of documents ... I feel another web page
coming on