Excel -> Word Macro

[Kalechibki]

I'm writing a VB Macro in Excel that opens up a word
document (A form letter), adds the file that the user is
working on as the data source, and then prints the merged
document. I'm running into a few snags.

The first is that when I try to do this, it tries to
reopen the excel spreadsheet I'm working on, and that
creates all sorts of problems. The second is that I can't
figure out how to use the connection property of
OpenDataSource (The code I'm currently using is below).
I've tried several things to get the area of the Excel
Spreadsheet I want as the table, and when it compiles, it
doesn't like what it sees.

The part of the Excel File I'm using to transfer values is
A2:AA2 on sheet1, with the headings for these values in
A1:AA1. I'm using the Office 2000 suite, with all three
service packs installed.

Any help anyone can provide on my two questions would be
wonderful!

Brian

Sub WordMailMergeTest()

Dim wdapp As Object

Set wdapp = CreateObject("Word.Application&quot
wdapp.Application.Documents.Open _
Filename:="C:\Documents and _
Settings\bcavanau\Desktop\testmerge2.doc"
wdapp.Visible = True
wdapp.Application.Documents(1).Activate
MsgBox "'" & wdapp.Application.ActiveDocument.Name _
& "' was opened."

Set myDoc = wdapp.Application.ActiveDocument
Set myMerge = _
wdapp.Application.ActiveDocument.MailMerge
With myDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:="C:\Documents and _
Settings\bcavanau\Desktop\LumpSumBenefit.xls", _
LinkToSource:=True, AddtoRecentFiles:=False _
'Connection := Sheet1!A1:AA1 <-- won't compile _
with this line
End With
With myMerge.DataSource
.firstrecord = 1
.lastrecord = 1
End With
With myMerge
.Destination = wdSendtoNewDocument
.Execute
End With
MsgBox &quot;'&quot; & wdapp.Application.ActiveDocument.Name _
& &quot;' is done merging.&quot;
Set wdapp = Nothing
End Sub

 

[Dreamboat]

Instead of trying to use mail merge, you may just want to populate bookmarks in Word from Excel? Anne Troy

http://www.TheOfficeExperts.com