Hi,
I have a mail merge template which is similar to Bank Name in the header and a list of all branches in the details, in columnar format = branch name, address, manager, contact no. etc
I need to select the list of branches from a branch master table (ms access) for the selected bank and fill them to respective cols (col headings are already in the template).. Number of branches may vary from bank to bank.
In am using Microsoft.Office.Interop.Word and C# coding... Can I use TableStart and TableEnd model to achieve this? How?
Rgds
Chandru
What I have tried:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Word;
using System.Data.OleDb;
using System.Data;
namespace WordAuto
{
public class Program
{
//private static string MF_Name;
public static void Main(string[] args)
{
//OBJECT OF MISSING "NULL VALUE"
OleDbConnection AudDbConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:/DBFOLDER/Database.accdb;Persist Security Info=True;Jet OLEDB:Database Password=ACS1234");
DataSet ds = new DataSet();
DataTable MFConfigDT = new DataTable();
DataTable ColNameDT = new DataTable();
Object oMissing = System.Reflection.Missing.Value;
Object oTemplatePath = "F:/From SCS PC D Drive/GalaxyKM/Projects/Shiv/MergePrintNew9.dotx";
string mfdatacol = "";
string mftablename = "";
Boolean mfgrid = false;
string mfgrdstend = "";
String Col_SocID = "";
String Col_FinYear = "";
string filterexp = "";
DataRow[] mfdatanam;
DataRow[] ColNames;
Application wordApp = new Application();
Document wordDoc = new Document();
OleDbCommand cmd = new OleDbCommand("select * FROM MF_Config", AudDbConn);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
da.Fill(MFConfigDT);
OleDbCommand cmd1 = new OleDbCommand("select * FROM Table_ColNames", AudDbConn);
OleDbDataAdapter da1 = new OleDbDataAdapter();
da.SelectCommand = cmd1;
da.Fill(ColNameDT);
int GridView = 0; // flag to indicate grid value to mailmerge
int SocId = 1; // ID of the selected society - this should be populated based on society selected by user
int FyrID = 0; // Financial Year ID from Year Master based on user selection
wordDoc = wordApp.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);
foreach (Field myMergeField in wordDoc.Fields)
{
int i = myMergeField.Index;
Range rngFieldCode = myMergeField.Code;
String fieldText = rngFieldCode.Text.Trim();
Int32 endMerge = fieldText.IndexOf("\\");
Int32 fieldNameLength = endMerge - 11;
String fieldName = fieldText.Substring(11, fieldNameLength);
// GIVES THE FIELDNAMES AS THE USER HAD ENTERED IN .dot FILE
fieldName = fieldName.Trim();
// **** FIELD REPLACEMENT IMPLEMENTATION GOES HERE ****//
// THE PROGRAMMER CAN HAVE HIS OWN IMPLEMENTATIONS HERE
if (fieldName.StartsWith("TableStart"))
{
GridView = 1;
continue; // next mergefield name is the actual field to print in the table
}
if (fieldName.StartsWith("TableEnd"))
{
GridView = 0;
continue; // table process ends
}
if (GridView == 1)
{
// process table data here
// process table data here
GridView = 0;
continue;
}
filterexp = "";
filterexp = "MF_Name = '" + fieldName.ToString() + "'";
mfdatanam = MFConfigDT.Select(filterexp);
foreach (DataRow Row in mfdatanam)
{
mfdatacol = Row["MF_DataCol"].ToString();
mftablename = Row["MF_TableName_1"].ToString();
mfgrid = Convert.ToBoolean(Row["MF_Grid"]);
}
filterexp = "";
filterexp = "Col_Table_Name = '" + mftablename + "'";
ColNames = ColNameDT.Select(filterexp);
foreach (DataRow Row in ColNames)
{
Col_SocID = Row["Col_SocID"].ToString(); // picks up col names from table inidcated above
Col_FinYear = Row["Col_FinYear"].ToString();
}
String WhereClause = "";
if (Col_FinYear == "")
WhereClause = " WHERE " + Col_SocID + " = " + SocId;
else
WhereClause = " WHERE '" + Col_SocID + " = '" + SocId + "' AND '" + Col_FinYear + " = '" + FyrID + "'";
OleDbCommand cmd5 = new OleDbCommand("select " + mfdatacol + " from " + mftablename + WhereClause + "", AudDbConn);
cmd5.Connection.Open();
string result = cmd5.ExecuteScalar().ToString();
cmd5.Connection.Close();
if (fieldName == "FinYear") // for testing purposes only
result = "2019-20";
myMergeField.Select();
wordApp.Selection.TypeText(result);
}
wordDoc.SaveAs("F:/mergeout/MergeOutput.doc");
var doc_close = (Microsoft.Office.Interop.Word._Document)wordDoc;
doc_close.Close();
var applicationclose = (Microsoft.Office.Interop.Word._Application)wordApp;
applicationclose.Quit();
=======
I am able to do the merge for all non=tabular data. My table is delimited by TableStart and TableEnd with variable no of row in the table... Code for table data merging should happen between TableStart and TableEnd when GridView == 1. I am not sure how to proceed for processing this