How can I programatically supress "Select Table" dialog?

[RHNewbie]

I'm writing a program that will open a new Word document, set up and execute a mail merge. I want to be able to do this without any user intervention. The problem I'm running into is this. When I execute the OpenDataSource method of the MailMerge object, it always pops up a "Select Table" dialog box asking the user to confirm the table which is followed by another dialog box allowing the user to filter the list. I don't mind the filter dialog but I don't want the "Select Table" confirmation to appear

Is there any way to keep that dialog from appearing

Thanks for your help

Word 2002 SP1 on Windows XP

 

[Peter Jamieson]

Can you post the code you are using in the OpenDataSource here please and
let us know what the data source is?

What we really need to know is what strings are in the following parameters:
Name
Connection
SQLStatement1
SQLStatement2 (probably empty)
and what, if anything, you are setting the Subtype parameter to?

--
Peter Jamieson

I'm writing a program that will open a new Word document, set up and

execute a mail merge. I want to be able to do this without any user
intervention. The problem I'm running into is this. When I execute the
OpenDataSource method of the MailMerge object, it always pops up a "Select
Table" dialog box asking the user to confirm the table which is followed by
another dialog box allowing the user to filter the list. I don't mind the
filter dialog but I don't want the "Select Table" confirmation to appear.

 

[RHNewbie]

My code is in Visual FoxPro but you should get the idea of what I'm doing

oWord = createobject("Word.Application"
oWord.Documents.Add(
oWord.Visible = .T
oWord.ActiveDocument.MailMerge.OpenDataSource("c:\temp\datasource.xls"

I also tried the following (before oWord.Visible = .T.): oWord.DisplayAlerts = .F

From the code, you can see that my datasource is an Excel file on my local drive

I would've tried to use a few more parms with OpenDataSource but I haven't for the life of me been able to find out what I'm required to pass. The intellisense tells me that the second parm is Format type Variant, but I have no idea what "Format" is supposed to be. The next parm after Format is ConfirmConversions. That may help in suppressing the window but OpenDataSource doesn't like that second parm being blank

Thanks for your help!

 

[Peter Jamieson]

I think you can ignore all the parameters except Name (which you have) and
SQLStatement, which needs to be set to a string containing a SQL SELECT
statement, e.g.

"SELECT * FROM `Sheet1$`"

I would've tried to use a few more parms with OpenDataSource but I haven't for the life
of me been able to find out what I'm required to pass.

Yes, it's difficult to find out what is needed to make these connections.

If you record a Word macro then make the connection, you may be able to see
the (VBA) macro code that Word generates. However, that may not work in Word
2002 as there are some problems, and you may also find that you cannot
display the values of

ActiveDocument.MailMerge.DataSource.ConnectionString and .QueryString

However, if you set up the connection, then save the document as HTML, then
open it in Notepad, you should be able to see the values of the various
parameters fairly near the top of the file.

The other parameters you may need to set are

Connection (but you can probably set this to "")

SubType - here it is set to wdMergeSubTypeAccess, which has value 1. You
might think that is odd because you are opening an Excel file, but it
probably reflects the fact that the Jet OLEDB provider is being used to read
the spreadsheet.

In my experience, none of the other parameters (other than SQLStatement1
which you only need if your SQL needs to be quite long) has any noticeable
effect

--
Peter Jamieson

My code is in Visual FoxPro but you should get the idea of what I'm doing.

oWord = createobject("Word.Application")
oWord.Documents.Add()
oWord.Visible = .T.
oWord.ActiveDocument.MailMerge.OpenDataSource("c:\temp\datasource.xls")

I also tried the following (before oWord.Visible = .T.): oWord.DisplayAlerts = .F.

From the code, you can see that my datasource is an Excel file on my local drive.

I would've tried to use a few more parms with OpenDataSource but I haven't

for the life of me been able to find out what I'm required to pass. The
intellisense tells me that the second parm is Format type Variant, but I
have no idea what "Format" is supposed to be. The next parm after Format is
ConfirmConversions. That may help in suppressing the window but
OpenDataSource doesn't like that second parm being blank.