public String ProcessCPDocument(string strCPDocument = null, string strCPIndicativeFile = null, string strApprovalDateSMPC = null, string strApprovalDatePIL = null, string strCPIndicativeSheet = null, string strProductName = null, string strCountryList = null, string strPicklist = null, string strCountryName = null, string strLanguage = null, string strSavedate = null, string strcleanfilepath = null) { bool strbool = IntialiseVariables(strCPIndicativeFile); if (!strbool) return "intialisation of global variables failed"; String strcleanversion = ""; String strCleanSourceFlag = ""; String strCleanSourceFile = ""; if (strProductName.Contains(".")) { strProductName = strProductName.Substring(0, strProductName.IndexOf(".")); strcleanversion = "true"; } /// ///Initiating outputresults for storing multiple results /// StringBuilder outputResults = new StringBuilder(); int intfound = 0; string strextension = null; if (strCPDocument.Contains(strDocx)) { strextension = strDocx; } else { strextension = strDoc; } return "CP Document does not exist"; /// ///Initiating Word Application in wdApplication Object /// MsWord.Application wdApplication = new MsWord.Application(); /// ///Initiating Word Document in wdDocument Object and also disable alerts if any /// wdApplication.Options.SavePropertiesPrompt = false; wdApplication.Options.SaveNormalPrompt = false; MsWord.Document wdDocument = new MsWord.Document(); System.Threading.Thread.Sleep(3000); wdApplication.Visible = true; //wdApplication.ScreenUpdating = false; wdApplication.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable; wdApplication.DisplayAlerts = WdAlertLevel.wdAlertsNone; string strFormulations = null; try { /// ///Initiating All variables to store values from CPIndicativeText File /// int intTotalrows = 1; int intTotalProducts = 0; string strOutputResultofDeleteContact = "co"; String strAction = null; strSMPCPDFFlag = null;//many such variables /// ///Initiating Connection, Dataset and Command Objects /// System.Data.OleDb.OleDbConnection Myconnect; System.Data.DataSet Dtset; System.Data.OleDb.OleDbDataAdapter Mycommand; /// ///Connection string for CP Indicative Text /// if (System.IO.File.Exists(strCPIndicativeFile)) Myconnect = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strCPIndicativeFile + ";Extended Properties=Excel 8.0"); else return "CP Indicative Text file is not found"; Myconnect.Open(); /// ///Select Query for fetching data from CP Indicative Text /// Mycommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Flag$] where [Country]='" + strCountryName + "'AND [Language]='" + strLanguage + "'", Myconnect); Mycommand.TableMappings.Add("Table", "TestTable"); /// ///Pulling data from Query result to Dataset /// Dtset = new System.Data.DataSet(); Mycommand.Fill(Dtset); if (Dtset.Tables[0].Rows.Count > 0) { /// ///Assigning values from CP Indicative Text to respective variables /// strSMPCPDFFlag = Dtset.Tables[0].Rows[0][3].ToString(); } Myconnect.Close(); if (strcleanversion.Contains("true")) { strTrackBasisSplitFlag = "No"; } /// /// ///Initiating Myconnect object with connection string for PickList data /// if (System.IO.File.Exists(strPicklist)) Myconnect = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPicklist + ";Extended Properties=Excel 8.0"); else return "PickList file does not exist"; Myconnect.Open(); /// ///Initiating Query to fetch results from Locations & Contacts sheet /// Mycommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Locations & contacts$] where [Country]='" + strCountryName + "'AND [Language]='" + strLanguage + "'", Myconnect); Mycommand.TableMappings.Add("Table", "TestTable"); Dtset = new System.Data.DataSet(); /// ///Assigning data from query to dataset /// Mycommand.Fill(Dtset); if (Dtset.Tables[0].Rows.Count > 0) { /// ///Assigning values from query results to respective variables /// strTrackWord = Dtset.Tables[0].Rows[0][3].ToString();//many such variables } Myconnect.Close(); /// ///Connection string for CP Indicative Text file /// Myconnect = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strCPIndicativeFile + ";Extended Properties=Excel 8.0"); Myconnect.Open(); /// ///Fetching Total rows query from CP Indicative Sheet /// Mycommand = new System.Data.OleDb.OleDbDataAdapter("select count([Action]) from [" + strCPIndicativeSheet + "$]", Myconnect); Mycommand.TableMappings.Add("Table", "TestTable"); Dtset = new System.Data.DataSet(); Mycommand.Fill(Dtset); /// ///Assigning total rows to TotalRows variable /// string DTvalue = Dtset.Tables[0].Rows[0][0].ToString(); int.TryParse(DTvalue, out intTotalrows); /// ///Assining query for fetching data from CP Indicative Sheet /// Mycommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + strCPIndicativeSheet + "$]", Myconnect); Mycommand.TableMappings.Add("Table", "TestTable"); Dtset = new System.Data.DataSet(); /// ///Feeding values from query to data set /// Mycommand.Fill(Dtset); /// ///Open the CP Document for CP Processing /// System.IO.FileInfo fi = new System.IO.FileInfo(strCPDocument); bool blfileopen = IsFileLocked(fi); fi = null; if (blfileopen == true) { return "CP file is already opened by someone and cannot be processed"; } wdDocument = wdApplication.Documents.Open(strCPDocument); System.Threading.Thread.Sleep(300); wdDocument.TrackRevisions = true; wdDocument.ActiveWindow.View.RevisionsFilter.Markup = WdRevisionsMarkup.wdRevisionsMarkupAll; wdDocument.ActiveWindow.View.MarkupMode = WdRevisionsMode.wdBalloonRevisions; Style style = wdDocument.Styles["Hyperlink"]; style.Font.Name = "Times New Roman"; style.Font.Size = 11; wdDocument.Save(); System.Threading.Thread.Sleep(5000); try { for (int i = 0; i <= intTotalrows - 1; i++) { /// ///Assining values from Dataset to respective variables /// strAction = Dtset.Tables[0].Rows[i][0].ToString();//many such variables /// ///Performing Change Type based on which respective functions should be called for specific actions /// if (strChangeType.Contains("Delete")) { DeleteSpecificText(wdDocument, strDataInput, 10); } if (strChangeType.Contains("Insert")) { InsertSpecificTextV2(wdDocument, strBeforeText, intfound, strCountryName, strDataInput, strThirdtext, strTogetherFlag, strFourthtext); outputResults.Append(strresult); } if (strChangeType.Contains("Hyperlink")) { /// ///Hyperlink specific text /// wdDocument.TrackRevisions = true; string strresult = AddHyperlink(wdDocument, strBeforeText, strAfterText, strCountryName); style.Font.Name = "Times New Roman"; style.Font.Size = 11; outputResults.Append(strresult); }//many such functions } catch (Exception Ex) { outputResults.Append(Ex.Message); } Myconnect.Close(); wdDocument.Save(); System.Threading.Thread.Sleep(5000); } catch (Exception ex) { outputResults.Append(ex.Message); } finally { try { wdDocument.TrackRevisions = true; if (System.IO.File.Exists(strDuplicatefile)) System.IO.File.Delete(strDuplicatefile); System.Threading.Thread.Sleep(5000); if (wdDocument != null) { wdDocument.Close(MsWord.WdSaveOptions.wdDoNotSaveChanges); System.Threading.Thread.Sleep(5000); System.Runtime.InteropServices.Marshal.ReleaseComObject(wdDocument); } if (strcleanversion.Contains("true") || strCleanSourceFlag.Contains("Yes")) { ConvertMarkedCPFile(strCPDocument, strProductName, strCleanSourceFile); } if (wdApplication != null) { wdApplication.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(wdApplication); } wdApplication = null; wdDocument = null; } catch (Exception) { } GC.Collect(); GC.WaitForPendingFinalizers(); } return outputResults.ToString(); }