How to assign worksheet as data source??

[REC]

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

 

[Doug Robbins]

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

--
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

 

[REC]

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.

 

[Doug Robbins]

Use:

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

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

--
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

 

[REC]

Cool. Thanks for your help!

Use:

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

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

--
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