Mail merge conversion from Word to .NET code.

[mutton]

What a very difficult automation library Word mail merge has been to use from
..NET... Before we get to the code, here are the functional specification
details:

- There are existing Word documents, which have mail merge fields set up
that will be used in this process.
- The user is to see no prompts. The merge will occur, open up an instance
of Word with the user looking at the completed merge.
- The print size for the new merged document will have to be modified for
certain documents (i.e. some documents will use the 'executive' print size).
This is only needed if the printing properties aren't brought over from the
original template.
- The completed merge will be a new document, with the original merge
document unchanged.
- Along with merge fields, there are a couple of fields that will be
replaced (such as today’s date).
- The data will be pulled from a stored procedure residing in a SQL Server
database. I would like it if this proc could have parameters.
- The .NET language we have been using for all of our applications is C#.

As you might guess, so far I have been unsuccessful. A further nuisance has
been the conversion of VB examples to C# and the automation library error
messages have been less than helpful. For testing purposes, I have switched
to using VB.NET as the testing language. From my painstaking research across
the internet, I found out after much toil that one of the best ways to get
this code right is to record a macro in Word and do the merge. Then copy
this code over to my VB.NET app (recommended by Cindy Meister).

While I would like all the requirements met above, I have been trying to get
just a real basic example working. Here is the source code for a click event
in my test app:

Dim wrdApp As Word.Application
Dim wrdDoc As Word._Document

wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True

wrdDoc = wrdApp.Documents.Open("C:\temp\test.doc")

wrdDoc.MailMerge.OpenDataSource(Name:= _
"C:\Documents and Settings\x\My Documents\My Data Sources\xGeo.odc" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=Word.WdOpenFormat.wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Password=baseball;Persist Security Info=True;User
ID=userx;Initial Catalog=MyDB;Data Source=x;Use Procedure for Prepare=1;Auto
Translate=True;Packet Size=4096;Workstation ID=x;Use Encryption for
Data=False;Tag w" _
, SQLStatement:="", SQLStatement1:="Select * from ""Geo""", SubType:= _
Word.WdMergeSubType.wdMergeSubTypeOther)

wrdDoc.MailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
wrdDoc.MailMerge.Execute()

This is the closest thing I have to something that works as a basic sample.
The only issue I have with this working, is when the merge is about to occur,
the columns that come back from SQL Server are not named properly. In this
example, I have a Geo table with a GeoID, Name, Address columns. What
happens when Word opens and tries to do the merge, is Word prompting me to
replace the merge fields because they don’t equal. What comes back for SQL
Server is fields called M__, M__1, M__2, etc…. Note that I have no problems
whatsoever doing the same merge from within Word.

Some notes I would like to mention with regard to this sample code:
- The OpenDataSource line was copied right from Word VBA code.
- I would like to not use if possible the .odc file that is referenced in
the Name parameter of the OpenDataSource method.
- So far I have been unable to get any sort of stored proc call happening in
the SQLStatement parameters of the OpenDataSource method.

This is just a basic sample and I would like to meet the requirements of my
initial functional requirements. If anyone has a process similar to this
working, some sample code would be great.

Right now I am considering two options moving forward if I can’t get this
working:
1. Convert the merge files to Crystal Reports
2. Make the users do a ‘typical’ merge right from Word.


Cheers,
JF

 

[robin]

I've experienced similar agonies trying to automate Word using C#, and
finally got a basic solution that reliably works.

My approach opens the document from .NET, but then calls a function in
word to run the merge. I don't think that part should matter and your
approach may be better (except you can't omit parameters):

// object for missing (or optional) arguments.
object oMissing = System.Reflection.Missing.Value;
object oMainFile = templateName;

// create an instance of Word
Word.ApplicationClass oWord = new Word.ApplicationClass();
// don't make it visible yet
oWord.Visible = false;
// open template as document
Word._Document oDoc = oWord.Documents.Add(ref oMainFile, ref
oMissing, ref oMissing, ref oMissing);

Anyway, when I get to the line OpenDataSource, I'm using a very simple
call with only 2 parameters supplied. (I found that that with this
fucntion the less you supply the less chance that parameters may
conflict. Eg, when I've specified the Connection parameter, as you do
in your sample, it just stops working for me. If you are running this
from the .NET side you should use System.Reflection.Missing.Value for
omitted optional parameters, not "".)

OpenDataSource Name:=strConnect, _
SQLStatement:=strSQL

Here, strConnect is the path and name of an ODC file as you have, and
strSQL references a stored procedure, with parameters. Word is very
picky about the format:

strSQL = "exec dbo.rpt_ReviewLetter " & strParams

(This is VBA inside Word, which is why it's using the &.)

Your problem with misnamed returned values doesn't make sense to me.
If you name the table fields to be returned in your SELECT statement,
that's what they should be named when Word sees them. I assume you've
made sure the table field names and mergeField names are matched.