Select Table window interrupts macro

[Straws]

Hi,
I am automating a mailmerge from a datasheet in excel to a word document.
The user enters data into the excel spreadsheet and then uses a userform to
activate the mailmerge process.

my code is interrupted when the Word main document is opened and the
datasource is being defined. Word pops up a window called "Select Table"
which (surprise surprise) allows you to select which worksheet the data is on
in the excel workbook.

I can't figure out how to automate this selection... I want to be selecting
"sheet1"... and it's the only sheet in the workbook so I don't understand why
it doesn't just select it automatically.

I won't post all the code at the moment cause I don't think it's entirely
relevant to the problem, but if anyone wants it then I would be happy to.

Here is the part that seems to be relevant.
With appWD.ActiveDocument.MailMerge
.OpenDataSource Name:=(Workbooks("whatson data").Path & "\Guts - do_
not tamper\Data Source.xls")
.DataSource.FirstRecord = 1
.DataSource.LastRecord = r - 3
.Destination = wdSendToNewDocument
.Execute
End With

I have searched the message board here and at mrexcel but can find no-one
who has had a similar problem... which leads me to suspect I'm doing
something wrong. However, time (and your responses) will tell.

Thanks SO much to anyone who can help me with this one.

Straws.

 

[Straws]

Errr, So is this too hard?
Or am I asking the wrong question somehow?

Someone *please* help

 

[Doug Robbins - Word MVP]

Recording the action of attaching Sheet 1 of an Excel file as the data
source for a mailmerge produces the following code:

ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\Book1.xls" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data
Source=C:\Documents and Settings\Doug Robbins\My Documents\My Data
Sources\Book1.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Eng" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="",
SubType:= _
wdMergeSubTypeAccess

As that was recorded in Word 2007, it may not be 100 percent appropriate for
the version that you are using so I would suggest that you record the same
action for yourself.

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