Mail merging from Excel to Word (XP/2002/2003) using OLEDB Programmatically

[Ben Ranson]

This solution is provided for benefit of the community, as I did not
find ALL the answers in usenet (or the MS KB) to this problematic
task.

Basically, the C# code below takes a NAMED RANGE (called
"MailMergeRange") from an Excel file (referred to as a string in my
code as "_xlSource.FullPath") and mail merges it into a new Word
document.

///////////////////////////////////

object oTrue = true ;
object oFalse = false ;

object sXLSource = _xlSource.FullPath ;
object oEmpty = Type.Missing ;
object sDocPath = Config.FS_DIR + _sState + "_Letter.doc" ;
Word.Document docLetter = _services.WordApp.Documents.Open( ref
sDocPath, ref oEmpty, ref oTrue, ref oEmpty, ref oEmpty, ref oEmpty,
ref oEmpty, ref oEmpty, ref oEmpty, ref oEmpty, ref oEmpty, ref
oTrue, ref oEmpty, ref oEmpty, ref oEmpty ) ;

try
{
Word.MailMerge mm = docLetter.MailMerge ;
object oConnection =
@"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=" + _xlSource.FullPath + @";Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBa" ;
object oSQL = "SELECT * FROM `MailMergeRange`" ;
object oSubType = Word.WdMergeSubType.wdMergeSubTypeAccess ;
mm.OpenDataSource( _xlSource.FullPath, ref oEmpty, ref oEmpty, ref
oFalse, ref oTrue, ref oEmpty, ref oEmpty, ref oEmpty, ref oTrue, ref
oEmpty, ref oEmpty, ref oConnection, ref oSQL, ref oEmpty, ref oEmpty,
ref oSubType ) ;
mm.Destination = Word.WdMailMergeDestination.wdSendToPrinter ;
mm.Execute( ref oEmpty ) ;
}
catch{}

docLetter.Close( ref oFalse, ref oEmpty, ref oEmpty ) ;
_services.WordApp = new Word.Application() ;

//////////////////////////

The main issues centre around the OpenDataSource method's parameters,
and are as follows:
1. OLE-DB can only be used in Office 2002/XP and beyond. Office 2000
uses DDE, and this method is rather different.
2. The Word.WdMergeSubType works for both wdMergeSubTypeAccess or
wdMergeSubTypeOLEDBWord.
3. You must specify the named Excel Range source in the SQL argument
as if it's a table. VERY IMPORTANT - You need to use the odd ` single
quote character or "grave accent" (Unicode character hex 0060) to
quote the range. The normal ' quote character doesn't work. If you
don't specify a SQL argument, misname the range, or don't use the `
character to quote the range, you will get the annoying "Select Table"
dialog appearing.

Apologies to any VBA programmers out there for the C#, but you should
be able to translate appropriately.

I hope this is of use - I lost a whole day of work trying to figure
this one out!



Ben Ranson
Technical Director
PlanLab Technologies
(e-mail address removed)
http://www.planlab.com