MailMerge: From Excel to Word C#

Question

I'm currently facing a problem regarding the MailMerge functionality of MS Word.

I had to rewrite an old VBA Application into C#. I'm practically done with that. New Application works fine. Except for one PopUp that I cannot get rid of.

So I have been looking around on the web for the past 2 days because our clients don't want that pop up as it hasn't been there in the old application. However I couldn't find a proper solution for this. Except a few people mentioning that probably the Connection string is incorrect. But I found no resources telling me how it should look in the C# code

This it how it looks in the old application:

Word.ActiveDocument.MailMerge.OpenDataSource Name:=strSourceDoc, ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:="", _
        Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & strSourceDoc & ";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";" _
        , SQLStatement:="SELECT * FROM `Tabelle1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess

I obviously tried already to take that connection key and use it in my code. But it does not prevent that pop up. I also tried playing around with the subtype. But it either doesn't change anything or throws a format exception.

This is whats working in C#:

mailApp.ActiveDocument.MailMerge.OpenDataSource(processedPath + file, true, false, true, 
    true, ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing, 
   "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" + 
    processedPath + file + ";Mode=Read;", 
   "SELECT * FROM 'Tabelle1$'", 
   ref oMissing, ref oMissing, ref oMissing,
   Word.WdMergeSubType.wdMergeSubTypeAccess);

How can I change the connection string to prevent that popup from showing?

Answer 1

So I found a solution, that is somehow working.

The actual problem (at least from what I tested) is the file extension not the connection string. I was using .xlsx files, as my source documents. But as soon as I tested with some xls Files the popup disapeared.

I just took a "google session" to find out the differences between xls and xlsx.

So I could change my code to work with xls Files only. Issue solved. But still an unpleasing solution for me tbh.

If you'd like to test around a little to maybe get it working with xlsx. Here is some code to test with (just bind it on a button click in winforms or something)

 class PerformMailMerge
    {
        Word.Application mailApp;
        Object oMissing = System.Reflection.Missing.Value;
        Object oFalse = false;
        string _path = @"Your Path to Excel File";
        string savePath = @"Your Path to Word Document";

        public void mailMerge2()
        {
            mailApp = new Word.Application();
            mailApp.Visible = false;

            mailApp.Documents.Add();

            mailApp.ActiveDocument.MailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdMailingLabels;

            //OpenDataSource: 
            mailApp.ActiveDocument.MailMerge.OpenDataSource(_path,
                Word.WdOpenFormat.wdOpenFormatAllWordTemplates, true, true, true,
                ref oMissing, ref oMissing, ref oMissing,
                ref oMissing, ref oMissing, ref oMissing, "TABLE Tabelle1$", "SELECT * FROM `Tabelle1$",
                ref oMissing, ref oMissing,
                Word.WdMergeSubType.wdMergeSubTypeWord2000);

            mailApp.ActiveDocument.SaveAs2(savePath);
            mailApp.ActiveDocument.Close();
            mailApp.Quit();
        }    
    }

EDIT: So in case anyone will stumble upon this again. I found a solution to the problem. The solution is NOT specifying a WdMergeSubType. This allows reading from xlsx files and still doesn't show the popup!