How to assign worksheet as data source??

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

REC - 12 Aug 2005 18:56 GMT

Hello,
I've got code to perform a mail merge using an Excel spreadsheet as the data
source. The spreadsheet will only have one worksheet, but the name of the
worksheet is variable. Is there a way to let the mail merge automatically
select the one and only worksheet? Thanks for your help. Here is my code:

'Configure mail merge data source parameters:
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=strDataSource, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="", _
SQLStatement:="SELECT * FROM `myWorksheetNameHere`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

'Start mail merge:
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Reply to this Message

Doug Robbins - 12 Aug 2005 20:41 GMT

The SQLStatement is optional. I believe that without it, the first sheet in
the Excel file will be used by default.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Hello,
> I've got code to perform a mail merge using an Excel spreadsheet as the
[quoted text clipped - 32 lines]
> .Execute Pause:=False
> End With

Reply to this Message

REC - 12 Aug 2005 21:01 GMT

Unfortunately, it doesn't automatically take the first sheet. Instead a
window pops up with a "Select Table" option. I'd like to avoid this if I
could. Thanks.

> The SQLStatement is optional. I believe that without it, the first sheet in
> the Excel file will be used by default.
[quoted text clipped - 35 lines]
> > .Execute Pause:=False
> > End With

Reply to this Message

Doug Robbins - 13 Aug 2005 06:06 GMT

Use:

SendKeys "~"
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Access\VSD.xls"

and the first sheet in the file will be attached as the datasource.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Unfortunately, it doesn't automatically take the first sheet. Instead a
> window pops up with a "Select Table" option. I'd like to avoid this if I
[quoted text clipped - 42 lines]
>> > .Execute Pause:=False
>> > End With

Reply to this Message

REC - 15 Aug 2005 14:32 GMT

Cool. Thanks for your help!

> Use:
>
[quoted text clipped - 49 lines]
> >> > .Execute Pause:=False
> >> > End With

Reply to this Message