Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
Is it possible to extract data from user entered fields into Access or
Excel? Thanks Mark |
#2
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#3
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#4
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#5
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#6
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#7
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
#8
Posted to microsoft.public.word.pagelayout
|
|||
|
|||
Extracting data from forms
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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extracting data from template files | Microsoft Word Help | |||
Extracting specific Excel data into word | Microsoft Word Help | |||
Extracting Data in Word 2003 | Microsoft Word Help | |||
Extracting data from a file | Microsoft Word Help | |||
Extracting data from a file | Formatting Long Documents |