MailMerge from Excel 2000 using self as datasource  
Author Message
DanSpiteri





PostPosted: Top

Excel Programming >> MailMerge from Excel 2000 using self as datasource

I am attempting to do use Excel VBA to start a Word MailMerge using data from
the active workbook as the datasource.

This is working fine for Excel 2002/2003, but fails in Excel 2000. By
converting to ODBC connection (was DDE), I have got to the point where it
looks like it should work, but Word complains 5922 'Word was unable to open
the Datasource', presumably because the datasource is already open (??).

Any ideas anyone?

Here is the code, note that Revert:=False is set.

Set appWord = CreateObject("Word.Application")

'Create a word document to hold the report
Set wdDoc = appWord.Documents.Add(strSelectedTemplate)

'Set up the mail merge main doc and data source
wdDoc.MailMerge.MainDocumentType = wdFormLetters
wdDoc.MailMerge.OpenDataSource Name:=ThisWorkbook.FullName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=0, Connection:= _
"DSN=Excel Files;DBQ=" & ThisWorkbook.FullName &
";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `MergeTable`", SQLStatement1:=""
With wdDoc.MailMerge
.SuppressBlankLines = True
.ShowWizard InitialState:=4
End With

Thanks

AndyB

Excel223