Removing Select Table Dialog from MailMerge Macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nirmal Singh - 08 Dec 2006 13:05 GMT

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

Reply to this Message

Peter Jamieson - 09 Dec 2006 10:05 GMT

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

>I am running a macro to do a mail merge from an excel spreadsheet.
>
[quoted text clipped - 12 lines]
>
> Nirmal Singh

Reply to this Message

Nirmal Singh - 13 Dec 2006 10:11 GMT

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?

> Assuming you're using Word 2002/2003 and you want to connect using the
> default method (OLEDB), the following should do the trick:
[quoted text clipped - 25 lines]
>>
>> Nirmal Singh

Reply to this Message

Peter Jamieson - 13 Dec 2006 11:00 GMT

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

> Peter,
>
[quoted text clipped - 35 lines]
>>>
>>> Nirmal Singh

Reply to this Message

Nirmal Singh - 15 Dec 2006 14:43 GMT

Peter,

Thanks for your help. Suggestion b worked!

Nirmal

> Some possibilities:
> a. ensure the sheet is not already open in Excel. If it needs to be,
[quoted text clipped - 85 lines]
>>>>
>>>> Nirmal Singh

Reply to this Message

Peter Jamieson - 16 Dec 2006 16:13 GMT

Good - thanks for the feedback.

Peter Jamieson

> Peter,
>
[quoted text clipped - 91 lines]
>>>>>
>>>>> Nirmal Singh

Reply to this Message