[Peter Jamieson]

Oh by the way what is the significance of the $?

It's just that you can't use the sheet name as you see it in the sheet.
As far as the OLE DB provider is concerned, the tablename it sees is the
sheetname + "$". If you were trying to connect to a named range, you
don't need to add the $. I am not sure there is any more significance
than that.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
Peter
Top man, that worked as sweet as a nut. Many thanks.

Oh by the way what is the significance of the $?

Regards
Richard

"Peter Jamieson" wrote:

In this case you need to specify a query. If the sheet name is Sheet1,
and assuming you want all the columns and rows, try

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
SQLStatement:="SELECT * FROM [Sheet1$]"

(notice the $ on the end of the sheet name)

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

rberry wrote:
I have programmatically set up a VBA routine from within Access 2003 to load
a Word 2003 mailmerge document (OLE DB) the datasource is a Excel 2003
spreadsheet.

This is all done in the background so the user doesn't see the gubbins of
Word doing its thing!

Problem, whilst it loads into the mailmerge document as expected it hangs up
because in the Select Table Dialogue Box it ask for the worksheet you want to
load as the Range of data selected.

Question is there a way to autiomate this so that is selcets a defined
worksheet "qryLetterMerge" and complete the rest of the automation process
which print the merged letters and then returns the user to access.

Below I show the VBA code I use to launch this process:

' Create Word Instance trapping routine (Early Binding).
Set objWord = GetObject(, "Word.Application")
If Err.Number 0 Then
Set objWord = CreateObject("Word.Application")
End If

'Launch Office Automation and run mailmerge letters.
' Make Word visible.
objWord.Visible = False
DoEvents
objWord.Activate

' Set the mail merge data source as the WEF (Front End) database.
objWord.Documents.Open FileName:=StrDoc, ConfirmConversions:=False,
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""

objWord.ActiveDocument.MailMerge.OpenDataSource
Name:="E:\Development Databases\WEF\qryLetterMerge.xls",
Connection:="qryLetterMerge"

' Manipulating the Word Mailmerge object
With objWord.ActiveDocument.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With

' Execute the mail merge.
For intCounter = 1 To RptCopies
.Execute
Next intCounter
End With

' Close Active Word Document
objWord.ActiveDocument.Close wdDoNotSaveChanges

' Close word object instance
objWord.Quit
Set objWord = Nothing