Extracting data from forms

M

Mark Lewis

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

Thanks

Mark
 
C

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
 
G

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
C

CyberTaz

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

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

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
 
G

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


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
M

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
 
Top