Re: Sending Word 2007 data to Excel 2007

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Here is how you could do it IF the forms are documents that are Protected
for Filling In Forms. Your reference radio buttons however may indicate
that they are some other type of form. None the less, the following should
give you some ideas.

It assumes that you have created an Excel Workbook (filename.xlsx) in which
in the first row of the first ***, you have inserted the names of the
fields into which you want to insert the data and that in the code you
access the fields in your form in the same order. You will have to replace
the square brackets and the FormField#Name within them with the actual names
of your formfields.

The code requires that a reference be set in the Visual Basic Editor to the
Microsoft Excel 12.0 Object Library

Dim fname As String
Dim PathToUse As String
Dim oXL As Excel.Application
Dim Target As Excel.Workbook
Dim Source As Document
Dim fd As FileDialog
Dim i As Long
Dim t*** As Excel.Work***

'If Excel is running, get a handle to it; otherwise start a new instance of
Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err Then
Set oXL = CreateObject("Excel.Application")
End If

Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select the folder containing the files."
If .Show = -1 Then
PathToUse = .SelectedItems(1) & "\"
Else
End If
End With
Set fd = Nothing
oXL.Visible = True
'Open the workbook
Set Target = oXL.Workbooks.Open("filename.xlsx")
Set tSheet = Target.Sheets(1)
t***.Activate
If Len(PathToUse) = 0 Then
Exit Sub
End If
fname = Dir$(PathToUse & "*.doc*")
i = 2
While fname <> ""
Set Source = Documents.Open(PathToUse & fname)
With Source
t***.Range("A" & i) = .FormFields("[FormField1Name]").result
t***.Range("B" & i) = .FormFields("[FormField2Name]").result
'etc
i = i + 1
End With
Source.Close wdDoNotSaveChanges
fname = Dir$()
Wend
Set t*** = Nothing
Set Target = Nothing
Set oXL = Nothing



--
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, originally posted via msnews.microsoft.com

"Geoff" <gebobs@xxxxxxxxxxx> wrote in message
news:D8BFEBC1-D86E-4546-8DC9-9B428DD7A6D2@xxxxxxxxxxxxxxxx
Just looking for some kind advice and perhaps some references for details.

I made a document template with form fields that we have been using to
record data. There are lots of lovely drop-downs, radio buttons, check
boxes,
etc. to make it easier for the folks to enter data. Typically though, the
data has been recorded on hard copy. Such is the way of dinosaurs.

Anyhoo...now I've been given a pile of these forms and they want the data
available electronically. This seems like a perfect application for access
where I could just mimic the form I made in Word and load it all up in a
table. Unfortunately, I am the only one with Access, so if I go that
route,
I'll be doing all the work and it will take until the end of my natural
life.
;-)

What would be the best way to go about this? I've looked at Data Form
entry
in Excel, but it doesn't look like you can have drop-downs, etc and
everything would need to be typed in manually.

I've read hear how you can save data only for a form, but what I've read
refers to the menu for Word 2003. Where can I set this up in Word 2007?
This
doesn't look to appealing either since there will then be thousands of
these
CSV files that will have to be individually imported or combined before
importing.

Is there any references for developing a data entry *** in Excel that
will
populate a table on another ***? i.e. one *** will always have just
the
current record and the other *** will have all the entered records.

Alternatively, are there any references on how to somehow "publish" the
data
from the Word form to Excel? I'm sure both these last strategies would
require VBA, but that's not a big deal. I'm more familiar with Access VBA,
but I can muddle through with some references.

Lastly, I saw one person respond "Have you heard of XML?" Would it be
worth
it to get the add-in and broaden my skills? Would XML be the way to go?

TIA...Geoff


.

Quantcast