Word/Excel VBA mail merge fails with filtered results

I have a VBA macro that copies uses an Excel file to do a mail merge
in Word. Everything works properly, except if the Excel file has
filtering, in which case I get

Run-time error '5640':
Word could not re-establish a DDE connection to Microsoft Excel to
complete the current task

The code I'm using is as follows:

With wordDoc.MailMerge
.OpenDataSource Name:=strSourcePath, Connection:="Entire
Spreadsheet", SQLStatement:=strSQL, SubType:=wdMergeSubTypeWord2000 '
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


Is there another argument I could use for "Connection:", eg., "Visible
Range"

Any tips would be appreciated.

TIA, John

You can try

"_FilterDatabase"

instead of

"Entire Spreadsheet"

However, you will then see blank rows where Excel has hidden the rows it
has filtered out, so you would need to change the query. How to do that
may depend on the version of Office - I'm not sure you can set
SQLStatement in the opendatasource, so you might need to leave the
SQLStatement parameter out of your opendatasource statement and put
something like the following code after the opendatasource

..DataSource.Querystring = "SELECT * FROM " & strSourcePath & "WHERE
(mycolumn is not NULL)

But, if you need further assistance...
a. do you really need to use DDE?
b. which version of Word/Excel?

Peter Jamieson

http://tips.pjmsn.me.uk

jm_dunning wrote:
I have a VBA macro that copies uses an Excel file to do a mail merge
in Word. Everything works properly, except if the Excel file has
filtering, in which case I get

Run-time error '5640':
Word could not re-establish a DDE connection to Microsoft Excel to
complete the current task

The code I'm using is as follows:

With wordDoc.MailMerge
.OpenDataSource Name:=strSourcePath, Connection:="Entire
Spreadsheet", SQLStatement:=strSQL, SubType:=wdMergeSubTypeWord2000 '
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


Is there another argument I could use for "Connection:", eg., "Visible
Range"

Any tips would be appreciated.

TIA, John