C# MailMerge from Excel

[chris]

Hi all,

I am trying to do a mailmerge from a web app in C#. I am trying to use an
excel file as a datasource. My code is not running properly. I would
appreciate a little help. This is my code:

Word.ApplicationClass oWordApp = new Word.ApplicationClass();
object missing = System.Reflection.Missing.Value;
object oTemplate = @"c:\test\test.doc";

// some object are not used
Object oFalse = false;
Object oTrue = true;
Object oName = Environment.CurrentDirectory + @"\template.dot";
Object oFileName = Environment.CurrentDirectory + @"\saved.doc";
Object oFileFormat = Word.WdSaveFormat.wdFormatDocument;
// end new

Word.MailMerge wrdMailMerge;
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate,ref
missing,ref missing, ref missing);

wrdMailMerge = oWordDoc.MailMerge;

try
{
wrdMailMerge.OpenDataSource(@"C:\test\list.xls",
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref oFalse);

// Unload objects from the memory
wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;
}
catch(Exception e)
{
Response.Write(e.ToString());
}

Thank you,

Chris

 

[Peter Jamieson]

1. you probably need to take account of the following KB article:

http://support.microsoft.com/kb/825765/en-us

2. You may need to provide a bit of SQL in one of your OpenDataSource
parameters, and possibly a Connection string as well. If you search this
group on Google Groups for OpenDataSource and Sheet1$ you will probably find
examples of what is needed, but e.g. the 13th parameter in the call probably
needs to be

@"SELECT * FROM `Sheet1$`"

Peter Jamieson

 

[chris]

Hi,
Thanks for the reply. I still can't make it work. I recorded a macro and
copied the parameters to pass to wrdMailMerge.OpenDataSource(), I got a type
mismatch for a while until I got the correct parameters in the correct order.
Now, I don't get the type mismatch, the code just hangs when I call
wrdMailMerge.OpenDataSource().

What I am trying to accomplish is the following:

- Open a word document with information and merge fields already established.
- Merge an excel file with columns named as the mergefields in the word
document.
- Save the merged document to a new file.


I changed the registry as stated in
http://support.microsoft.com/kb/825765/en-us

I don't understang why it is not working.

Here's my code.

------------------

Word.ApplicationClass oWordApp = new Word.ApplicationClass();
object missing = System.Reflection.Missing.Value;
object oTemplate = @"C:\test\test.doc";
object oFalse = false;
object oTrue = true;
object oQuery = "SELECT * FROM 'Sheet1$'";

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User
ID=Admin;Data Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=HDR=YES;IMEX=1;'';Jet OLEDB:System database='';Jet OLEDB:Registry
Path='';Jet OLEDBatabase Password='';Jet OLEDB:Engine Type=35;Jet OLE";

object oFormat = Word.WdOpenFormat.wdOpenFormatAuto;
object oSubType = Word.WdMergeSubType.wdMergeSubTypeAccess;

Word.MailMerge wrdMailMerge;
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate,ref
missing,ref missing, ref missing);

wrdMailMerge = oWordDoc.MailMerge;

// code hangs here
wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse, ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery,ref missing, ref missing,ref
oSubType);

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);
// Unload objects from the memory
wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;

----------------


Can you guys please give me a hand? Thanks!

Chris

 

[Peter Jamieson]

Some things to try:

The quotes around Sheet1$ need to be backquotes `` not straight quotes ''.
square brackets [] will probably do instead.

I would try it woithout the connection string, but if you need the string,
it has been truncated in the middle of a jet keyword (this is because Word
actually stores a much longer connection string internally than you can get
at using the 255-character .Connectstring value.

The following is probably enough (in fact it will probably work with even
less info.), but typically you would have double quotes around

HDR=YES;IMEX=1;

and I don't know how you do that in C# so you need to replace my <double
quotes> with the right thing.


object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended Properties=<double
quotes>HDR=YES;IMEX=1;<double quotes>;Jet OLEDB:Engine Type=35;";

Peter Jamieson

 

[chris]

OK, my code executes with no errors, and it doesn't hang, but nothing really
happens.

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;";

object oQuery = "SELECT * FROM `Sheet1$`";

wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse, ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery, ref missing, ref missing,ref
oSubType);

the code runs with the connection you told me, but I am not sure it is
reading from the file.

after I call OpenDataSource() I do this:

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);

the enum WdMailMergeDestination.wdSendToNewDocument sends the merge doc to a
new file. where is that doc generated? in the same directory?

Since nothing happened, I tried saving it:

object oTemplateDone = @"C:\test\done.doc";

oWordDoc.SaveAs(ref oTemplateDone,ref missing, ref missing, ref missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing);

The doc is saved as done.doc with the merge fields not populated.

Am I missing something absolutely obvious?

Thanks,

Chris

Some things to try:

The quotes around Sheet1$ need to be backquotes `` not straight quotes ''.
square brackets [] will probably do instead.

I would try it woithout the connection string, but if you need the string,
it has been truncated in the middle of a jet keyword (this is because Word
actually stores a much longer connection string internally than you can get
at using the 255-character .Connectstring value.

The following is probably enough (in fact it will probably work with even
less info.), but typically you would have double quotes around

HDR=YES;IMEX=1;

and I don't know how you do that in C# so you need to replace my <double
quotes> with the right thing.


object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended Properties=<double
quotes>HDR=YES;IMEX=1;<double quotes>;Jet OLEDB:Engine Type=35;";

Peter Jamieson

Hi,
Thanks for the reply. I still can't make it work. I recorded a macro and
copied the parameters to pass to wrdMailMerge.OpenDataSource(), I got a
type
mismatch for a while until I got the correct parameters in the correct
order.
Now, I don't get the type mismatch, the code just hangs when I call
wrdMailMerge.OpenDataSource().

What I am trying to accomplish is the following:

- Open a word document with information and merge fields already
established.
- Merge an excel file with columns named as the mergefields in the word
document.
- Save the merged document to a new file.


I changed the registry as stated in
http://support.microsoft.com/kb/825765/en-us

I don't understang why it is not working.

Here's my code.

------------------

Word.ApplicationClass oWordApp = new Word.ApplicationClass();
object missing = System.Reflection.Missing.Value;
object oTemplate = @"C:\test\test.doc";
object oFalse = false;
object oTrue = true;
object oQuery = "SELECT * FROM 'Sheet1$'";

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User
ID=Admin;Data Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=HDR=YES;IMEX=1;'';Jet OLEDB:System database='';Jet
OLEDB:Registry
Path='';Jet OLEDBatabase Password='';Jet OLEDB:Engine Type=35;Jet OLE";

object oFormat = Word.WdOpenFormat.wdOpenFormatAuto;
object oSubType = Word.WdMergeSubType.wdMergeSubTypeAccess;

Word.MailMerge wrdMailMerge;
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate,ref
missing,ref missing, ref missing);

wrdMailMerge = oWordDoc.MailMerge;

// code hangs here
wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse,
ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery,ref missing, ref missing,ref
oSubType);

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);
// Unload objects from the memory
wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;

----------------


Can you guys please give me a hand? Thanks!

Chris

 

[Peter Jamieson]

Am I missing something absolutely obvious?

I don't think so.

the enum WdMailMergeDestination.wdSendToNewDocument sends the merge doc to
a
new file. where is that doc generated? in the same directory?

It is just in Word, i.e. unsaved.

object oTemplateDone = @"C:\test\done.doc";

oWordDoc.SaveAs(ref oTemplateDone,ref missing, ref missing, ref
missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref
missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing);

When you merge to a new document, the new document becomes the
ActiveDocument, so you need to save that rather than oWordDoc. (And I don't
believe /that/ is "obvious" )

However, there is a proviso. If you set the Pause parameter to the Execute
method to False, you may also end up with an Error Document, and in that
case, the Error document is the Activedocument and (as far as I know) there
is no keyword (such as ActiveDocument) that gives you a reference to the
output document. If you need to set Pause:=False and that creates a problem
for you, I think the only way to deal with it is
a. count the documents open before and after you merge and store their
names
b. if you have one extra document after the merge, it's the merge output
and it's the Activedocument
c. if you have two extra documents, the activedocument is the Error
document and whichever other document is not listed in the names you stored
pre-merge is the output document.

(That assumes you're merging to a new document).

Peter Jamieson

OK, my code executes with no errors, and it doesn't hang, but nothing
really
happens.

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;";

object oQuery = "SELECT * FROM `Sheet1$`";

wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse,
ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery, ref missing, ref missing,ref
oSubType);

the code runs with the connection you told me, but I am not sure it is
reading from the file.

after I call OpenDataSource() I do this:

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);

the enum WdMailMergeDestination.wdSendToNewDocument sends the merge doc to
a
new file. where is that doc generated? in the same directory?

Since nothing happened, I tried saving it:

object oTemplateDone = @"C:\test\done.doc";

oWordDoc.SaveAs(ref oTemplateDone,ref missing, ref missing, ref
missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref
missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing);

The doc is saved as done.doc with the merge fields not populated.

Am I missing something absolutely obvious?

Thanks,

Chris

Some things to try:

The quotes around Sheet1$ need to be backquotes `` not straight quotes
''.
square brackets [] will probably do instead.

I would try it woithout the connection string, but if you need the
string,
it has been truncated in the middle of a jet keyword (this is because
Word
actually stores a much longer connection string internally than you can
get
at using the 255-character .Connectstring value.

The following is probably enough (in fact it will probably work with even
less info.), but typically you would have double quotes around

HDR=YES;IMEX=1;

and I don't know how you do that in C# so you need to replace my <double
quotes> with the right thing.


object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended Properties=<double
quotes>HDR=YES;IMEX=1;<double quotes>;Jet OLEDB:Engine Type=35;";

Peter Jamieson

Hi,
Thanks for the reply. I still can't make it work. I recorded a macro
and
copied the parameters to pass to wrdMailMerge.OpenDataSource(), I got a
type
mismatch for a while until I got the correct parameters in the correct
order.
Now, I don't get the type mismatch, the code just hangs when I call
wrdMailMerge.OpenDataSource().

What I am trying to accomplish is the following:

- Open a word document with information and merge fields already
established.
- Merge an excel file with columns named as the mergefields in the word
document.
- Save the merged document to a new file.


I changed the registry as stated in
http://support.microsoft.com/kb/825765/en-us

I don't understang why it is not working.

Here's my code.

------------------

Word.ApplicationClass oWordApp = new Word.ApplicationClass();
object missing = System.Reflection.Missing.Value;
object oTemplate = @"C:\test\test.doc";
object oFalse = false;
object oTrue = true;
object oQuery = "SELECT * FROM 'Sheet1$'";

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User
ID=Admin;Data Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=HDR=YES;IMEX=1;'';Jet OLEDB:System database='';Jet
OLEDB:Registry
Path='';Jet OLEDBatabase Password='';Jet OLEDB:Engine Type=35;Jet
OLE";

object oFormat = Word.WdOpenFormat.wdOpenFormatAuto;
object oSubType = Word.WdMergeSubType.wdMergeSubTypeAccess;

Word.MailMerge wrdMailMerge;
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate,ref
missing,ref missing, ref missing);

wrdMailMerge = oWordDoc.MailMerge;

// code hangs here
wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse,
ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery,ref missing, ref
missing,ref
oSubType);

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);
// Unload objects from the memory
wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;

----------------


Can you guys please give me a hand? Thanks!

Chris


:

1. you probably need to take account of the following KB article:

http://support.microsoft.com/kb/825765/en-us

2. You may need to provide a bit of SQL in one of your OpenDataSource
parameters, and possibly a Connection string as well. If you search
this
group on Google Groups for OpenDataSource and Sheet1$ you will
probably
find
examples of what is needed, but e.g. the 13th parameter in the call
probably
needs to be

@"SELECT * FROM `Sheet1$`"

Peter Jamieson
Hi all,

I am trying to do a mailmerge from a web app in C#. I am trying to
use
an
excel file as a datasource. My code is not running properly. I would
appreciate a little help. This is my code:

Word.ApplicationClass oWordApp = new Word.ApplicationClass();
object missing = System.Reflection.Missing.Value;
object oTemplate = @"c:\test\test.doc";

// some object are not used
Object oFalse = false;
Object oTrue = true;
Object oName = Environment.CurrentDirectory + @"\template.dot";
Object oFileName = Environment.CurrentDirectory + @"\saved.doc";
Object oFileFormat = Word.WdSaveFormat.wdFormatDocument;
// end new

Word.MailMerge wrdMailMerge;
Word.Document oWordDoc = oWordApp.Documents.Add(ref oTemplate,ref
missing,ref missing, ref missing);

wrdMailMerge = oWordDoc.MailMerge;

try
{
wrdMailMerge.OpenDataSource(@"C:\test\list.xls",
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing,
ref missing, ref missing, ref missing);

wrdMailMerge.Destination =
Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref oFalse);

// Unload objects from the memory
wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;
}
catch(Exception e)
{
Response.Write(e.ToString());
}

Thank you,

Chris

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Y2hyaXM=?=,

OK, my code executes with no errors, and it doesn't hang, but nothing really
happens.

object oConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test\\list.xls;Mode=Read;Extended
Properties=\"HDR=YES;IMEX=1;\";Jet OLEDB:Engine Type=35;";

object oQuery = "SELECT * FROM `Sheet1$`";

wrdMailMerge.OpenDataSource(@"C:\test\list.xls",ref oFormat,ref oFalse, ref
oFalse, ref oTrue,ref oFalse, ref missing, ref missing,ref oFalse, ref
missing, ref missing,ref oConn, ref oQuery, ref missing, ref missing,ref
oSubType);

the code runs with the connection you told me, but I am not sure it is
reading from the file.

Comment everything after this point, make sure the Word app is visible and
activated. Display the mail merge toolbar and see if any besides the first two
buttons are available. Are you able to see the list of fields, for example? Or
to preview the data (the <<ABC>> button)?

To answer your question: the new document is generated only in memory and
should have become the ActiveDocument. It's not saved to disk. If you aren't
seeing it, then Execute is apparently not running. Try passing an object that's
set to false (object ofalse = false; wrdMailmerge.Execute(ref ofalse) and see
if that makes any difference.

after I call OpenDataSource() I do this:

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);

the enum WdMailMergeDestination.wdSendToNewDocument sends the merge doc to a
new file. where is that doc generated? in the same directory?

Since nothing happened, I tried saving it:

object oTemplateDone = @"C:\test\done.doc";

oWordDoc.SaveAs(ref oTemplateDone,ref missing, ref missing, ref missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing,ref
missing, ref missing, ref missing,ref missing, ref missing, ref missing);

The doc is saved as done.doc with the merge fields not populated.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[chris]

Thank you! I got it to work like this:

wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument;
wrdMailMerge.Execute(ref missing);

object oTemplateDone = @"C:\test\done.doc";

oWordApp.ActiveDocument.SaveAs(ref oTemplateDone,ref missing, ref missing,
ref missing,ref missing, ref missing, ref missing,ref missing, ref missing,
ref missing,ref missing, ref missing, ref missing,ref missing, ref missing,
ref missing);

After the merge is done I still have WINWORD.EXE running under aspnet. I
know that GC is supposed to take care of that, but I have it in memory for a
long period of time. I tried to release them like this:

// Unload objects from the memory
System.Runtime.InteropServices.Marshal.ReleaseComObject (wrdMailMerge);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWordApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWordDoc);

or like this:

wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;

but none of those ways worked. any tricks?

Thanks!

 

[Peter Jamieson]

When you've finsihed with the Word object and released oWordDoc etc, you'll
need something like

oWordApp.Quit

Peter Jamieson

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Y2hyaXM=?=,

After the merge is done I still have WINWORD.EXE running under aspnet. I
know that GC is supposed to take care of that, but I have it in memory for a
long period of time. I tried to release them like this:

// Unload objects from the memory
System.Runtime.InteropServices.Marshal.ReleaseComObject (wrdMailMerge);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWordApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWordDoc);

or like this:

wrdMailMerge = null;
oWordDoc = null;
oWordApp = null;

but none of those ways worked. any tricks?

Most "deterministic" method: Release the mailmerge and doc objects, as you do
above, by setting them to null. Now force GarbageCollection:
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
//Yes, you do it twice

Now, quit the application as Peter suggests (note that in C# you must pass a
ref obj parameter; you can pass "missing"), then perform the garbage
collection again.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail