C# MailMerge from Excel
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
"chris" wrote in message
...
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$`";
",re f 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
"Peter Jamieson" wrote:
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
quotesHDR=YES;IMEX=1;double quotes;Jet OLEDB:Engine Type=35;";
Peter Jamieson
"chris" wrote in message
...
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 OLEDB atabase 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
",re f 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" wrote:
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" wrote in message
...
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
{
",
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
|