Sending Word 2007 data to Excel 2007

[Geoff]

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 sheet in Excel that will
populate a table on another sheet? i.e. one sheet will always have just the
current record and the other sheet 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

 

[Doug Robbins - Word MVP on news.microsoft.com]

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 sheet, 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 tSheet As Excel.Worksheet

'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)
tSheet.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
tSheet.Range("A" & i) = .FormFields("[FormField1Name]").result
tSheet.Range("B" & i) = .FormFields("[FormField2Name]").result
'etc
i = i + 1
End With
Source.Close wdDoNotSaveChanges
fname = Dir$()
Wend
Set tSheet = 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]

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.

Thanks! No, it's as you say. I was just naming some things off the top of my
head. They are mainly text fields and drop-downs.

It assumes that you have created an Excel Workbook (filename.xlsx) in which
in the first row of the first sheet, 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.

I created a test .docm file in Word with three fields: Tom, Dick and Harry.
I added a command button to call the code you gave. The form is protected for
entering form data only. I edited the code for the macro to reflect the name
of the excel file and the t.sheet.Range... statements for the above fields.
See below (all other commands removed here)...

I also commented out the directory selection code and added the full path to
the open excel file statement.
....................................................................................
Set Target = oXL.Workbooks.Open("book1.xlsm")
..
..
..
tSheet.Range("A" & i) = .FormFields("Tom").result
tSheet.Range("B" & i) = .FormFields("Dick").result
tSheet.Range("B" & i) = .FormFields("Harry").result
....................................................................................

I added the reference library Microsoft Excel 12.0 Object Library .

Lastly, I created a test I created a test Excel .xlsm file with the columns
Tom, Dick and Harry.

I've compiled the code and no problem. But the data doesn't get entered. I'm
real close though. I stepped through the code and everything seems to work
fin until I get to the statement:

fname = Dir$(PathToUse & "*.doc*")

At this point, PathToUse is exactly right. But when the statement executes,
fname = "", which will of course end the code when it gets to the while. What
does the fname statement do? Do my Word and Excel files need to be in the
same directory?

Ideally, I'd like to remove all the select directory stuff and just set the
directory hardwired in the code. I will work on that until I hear back from
someone.

Thank you so much!

Ideally, I'd like to skip over all the searching for directory thing and just

 

[Doug Robbins - Word MVP on news.microsoft.com]

The

Dir$(PathToUse & "*.doc*")

in the

fname = Dir$(PathToUse & "*.doc*")

line of code returns a filename from the folder that is stored in the
variable PathToUse.

What type of files do you have in the folder? Word documents or CSV files?
--
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]

The Dir$(PathToUse & "*.doc*")

in the

fname = Dir$(PathToUse & "*.doc*")

line of code returns a filename from the folder that is stored in the
variable PathToUse.

What type of files do you have in the folder? Word documents or CSV files?

All I have is the Word form for entering the data (.docm) and the Excel data
file (.xlsm) in which I want to store the the data from the form.

 

[Doug Robbins - Word MVP on news.microsoft.com]

If you are sure that all the files will be in .docm format, try using

fname = Dir$(PathToUse & "*.docm")

What macros are in the .docm file? If there is an autoopen macro, it could
be interfering with the process.

It would probably be better to have the blank Word form saved as a macro
enabled template (.dotm) and have the other users use File>New and select
the template when they want to fill in a form. Then when they save the
document, it will be saved without any macros in it.

--
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]

If you are sure that all the files will be in .docm format, try using

fname = Dir$(PathToUse & "*.docm")

What macros are in the .docm file? If there is an autoopen macro, it could
be interfering with the process.

It would probably be better to have the blank Word form saved as a macro
enabled template (.dotm) and have the other users use File>New and select
the template when they want to fill in a form. Then when they save the
document, it will be saved without any macros in it.

Isn't this code supposed to be in the form document?

The Word document shouldn't change right? It's only used for entering data.
The data should go "through" it to the Excel file. Am I not looking at this
right? There are tow files:
1. the Word document, with form fields, that is used for entering data.
2. the Excel document which is the data repository.

This is the way I had thought we were going at least.

 

[Doug Robbins - Word MVP on news.microsoft.com]

Sorry, I guess I omitted to say that this code should be in a template that
you save in the Word Startup folder so that it becomes an Add-in.

See http://word.mvps.org/faqs/macrosvba/OrganizeMacros.htm

--
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]

Well thanks for your help but I think this is all beyond me. I should have
just entered the data. Sometimes there are no shortcuts.

 

[Doug Robbins - Word MVP on news.microsoft.com]

Once you get it set up, the process is effortless.

--
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]

I'm not sure how this works then. So the code is in a template in the startup
file. What initiates the code? Sorry. I'm more familiar with Access where
code is typically triggered by events (e.g. button pushed, data entered,
active control changed).

 

[Doug Robbins - Word MVP on news.microsoft.com]

The macro in the template will appear in the list of macros and you trigger
it by selecting it from that list. Or you could modify the Ribbon so that
it includes a button to run the macro.

See the following page of fellow MVP Greg Maxey's website:

http://gregmaxey.mvps.org/Customize_Ribbon.htm

--
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]

Thanks for all the help Doug. I just got it to work!

 

[Geoff]

I spoke too soon. Somehow some of the fields in the Word template were not
named, lost their names, or whatever.

Is there a way to refer to them as Item x as they are in the locals window.

For example, I have a field in locals identified as Source.FormFields.Item 1
and Source.FormFields.Item 1.Name = "InitDate". This one works fine.

There's also a field in locals identified as Source.FormFields.Item 8 and
Source.FormFields.Item 8.Name = "".

I swear to god I got it to work once with the statement:
tSheet.Range("h" & i) = .FormFields("Item 8").Result

 

[Geoff]

I figgered it out all with my own little pea-sized brain.

Source.FormFields(i).Result

 

[Geoff]

FWIW...here's how my code turned out. Most of it was gratefully taken from
Doug's post, but I threw in a few wriggles and took out some things that
weren't necessary for my application.

Dim fname$, PathToUse$, i&, j&
Dim oXL As Excel.Application
Dim Target As Excel.Workbook
Dim Source As Document
Dim tSheet As Excel.Worksheet

On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

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

PathToUse = "[hardwired path here]"
oXL.Visible = True

Set Target = oXL.Workbooks.Open("[hardwired path and filename here]")
Set tSheet = Target.Sheets(1)
tSheet.Activate

fname = Dir$(PathToUse & "*.doc*")
i = 2

While fname <> ""
Set Source = Documents.Open(PathToUse & fname)
With Source
For j = 1 To 30
ActiveSheet.Cells(i, j).Value = .FormFields(j).Result
Next
i = i + 1
End With
Source.Close wdDoNotSaveChanges
fname = Dir$()
Wend

Set tSheet = Nothing
Set Target = Nothing
Set oXL = Nothing

.........................................................

Mille grazie, Doug!