Mail merge with Excel file as datasource

[-kve-]

I am writing a program (C#) that performs a mail merge in Word, using an
Excel file as datasource.
This works fine, except for 1 thing: when I open the datasource in my code,
Word opens a popup form "Select table" where I have to select the table for
the mail merge.
Is there any way this can be passed automatically?

(I tried setting the mailMerge.DataSource.Table property, but it's read
only...)

Thanks

 

[Peter Jamieson]

You need to specify the sheet name or range name in a piece of SQL in the
SQLStatement parameter.

To use a Sheet name, append a "$" sign, e.g. for Sheet1, use

"SELECT * FROM [Sheet1$]"

If you want to select specific columns or more complex queries, bear in mind
that when Word gets data from Excel using OLE DB (the default method) , it
uses the Jet provider (or ACE provider in Word 2007). however, a quirk is
that you have to alias the table name, e.g.

"SELECT col1,col2 FROM [Sheet1$]"

will not work (you'll still see all the columns. You have to use

"SELECT s.col1,s.col2 FROM [Sheet1$] s"

even though it is not syntactially required by JET SQL (or any other diaect
of SQL I know).

Before you commit a lot of resource to this approach, I also suggest you
read

http://tips.pjmsn.me.uk/t0003.htm