Removing Select Table Dialog from MailMerge Macro

[Nirmal Singh]

I am running a macro to do a mail merge from an excel spreadsheet.

The code for the mail merge is as follows:

ActiveDocument.MailMerge.OpenDataSource Name:= _
excelsheet, ConfirmConversions:=False, ReadOnly:= _
False, LinkToSource:=True, AddToRecentFiles:=False, _
Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Sheet1", SQLStatement:="", SQLStatement1:=""

This pops up a dialog box to select the table for the merge. I want to use
the data in Sheet1.

How can I get rid of this dialog within my code?


Nirmal Singh

 

[Peter Jamieson]

Assuming you're using Word 2002/2003 and you want to connect using the
default method (OLEDB), the following should do the trick:

ActiveDocument.MailMerge.OpenDataSource _
Name:=excelsheet, _
Connection:="", _
SQLStatement:="SELECT * FROM `Sheet1$`"

(If you macro record the data source setup, you will see that Word actually
inserts a truncated Connection string, but you shouldn't need it)

Peter Jamieson

 

[Nirmal Singh]

Peter,

Sorry for the late response, I've been on leave.

I have tried your suggestion, but it now comes up with a confirm datasource
dialog. This has, amongst other options, OLEDB.

Can I get rid of this too?

 

[Peter Jamieson]

Some possibilities:
a. ensure the sheet is not already open in Excel. If it needs to be, you'll
have to connect using the old DDE method instead (see below)
b. Did you use backquotes around the sheet name (`Sheet1$`, not 'Sheet1$')
?
c. You may need the connection string, e.g. something like

ActiveDocument.MailMerge.OpenDataSource _
Name:=excelsheet, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& excelsheet _
&";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:Engine Type=35;", _
SQLStatement:="SELECT * FROM `Sheet1$`"

You may also need a "subtype" (these are not very well documented), e.g.

ActiveDocument.MailMerge.OpenDataSource _
Name:=excelsheet, _
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& excelsheet _
&";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:Engine Type=35;", _
SQLStatement:="SELECT * FROM `Sheet1$`", _
Subtype:=wdMergeSubTypeOther

(or maybe wdMergeSubTypeAccess)

If you need to connect using DDE, you can try

ActiveDocument.MailMerge.OpenDataSource _
Name:=excelsheet, _
Connection:="Sheet1$", _
SQLStatement:="", _
Subtype:=wdMergeSubTypeWord2000

but you will probably only be able to access the first sheet in the
workbook, or the sheet that was open when the workbook was last saved

If none of the above achieve what you want, can you
a. macro record you you're doing and post the OpenDataSource code here.
Beware - Word doesn't always record an OpenDataSource that will work how you
expect when you execute the recorded macro.
b. ensure that you can open the data source manually using OLEDB (or
whatever it is you want to use).

(NB, you may not be able to do (a) in Word 2002)

Peter Jamieson

 

[Nirmal Singh]

Peter,

Thanks for your help. Suggestion b worked!

Nirmal

 

[Peter Jamieson]

Good - thanks for the feedback.

Peter Jamieson

Peter,

Thanks for your help. Suggestion b worked!

Nirmal