writing WORD form data to EXCEL using VBA

[Paul]

I have a WORD 2003 template that is used many times a day by multiple users.
This template contains extensive VBA macros. I would like to add code to the
"save document" macro to open an existing EXCEL 2003 spreadsheet, write
formfield data from the WORD template to a new line in the spreadsheet, and
then save and close the spreadsheet. This spreadsheet will serve as a log
that will hold all of the relevant data for the WORD letters that are mailed
out.

I think the steps I will need in my WORD macro are: 1) open an Excel
spreadsheet, 2) go to the proper workbook, 3) find the last record, 4) append
a new record with the WORD form data, 5) save and close the spreadsheet. I
am not familiar enough with EXCEL to know the proper commands. Help!

 

[Doug Robbins - Word MVP]

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

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

If you really want to use Excel, you can probably cobble something together
from the information in the article "Control Excel from Word" at:

http://www.word.mvps.org/FAQs/InterDev/ControlXLFromWord.htm

and the following routine, which was developed to extract information from
all of the Excel Workbooks in a folder into the Workbook in which the macro
was located:

Sub MakeInventory()
Dim fname As String
Dim PathToUse As String
Dim Target As Workbook
Dim wb As Workbook
Dim fd As FileDialog
Dim drange As Range
Dim product As String
Dim spack As String
Dim username As String
Dim i As Long, j As Long
Set Target = ActiveWorkbook
With Target.Sheets(1)
.Range("A1") = "User"
.Range("B1") = "Product"
.Range("C1") = "Service Pack"
End With
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
.Title = "Select the folder containing the MSIA files."
If .Show = -1 Then
PathToUse = .SelectedItems(1) & "\"
Else
End If
End With
Set fd = Nothing
MsgBox PathToUse
If Len(PathToUse) = 0 Then
Exit Sub
End If
fname = Dir$(PathToUse & "*.xls")
i = 1
While fname <> ""
Set wb = Workbooks.Open(PathToUse & fname)
With wb.Sheets(1)
Set drange = .Range("A2")
username = drange
For j = 8 To 20
Set drange = .Range("A" & j)
If drange <> "" Then
i = i + 1
product = drange
Set drange = .Range("C" & j)
spack = drange
With Target.Sheets(1)
.Range("A" & i) = Mid(username, 15)
.Range("B" & i) = product
.Range("C" & i) = spack
End With
Else
Exit For
End If
Next j
End With
wb.Close False
fname = Dir$()
Wend
End Sub
--
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