advance to the NextRecord on Microsoft.Office.Interlop.Word Mailmerge with C#?
Budget: $10 – $40 USD
I have this method that "works", but only gives me one row of data. I used EPPlust to loop through the Excel file and calling the Word document template. The template is an 8.5 X 11 document with 21 labels. My goal is the fill the labels with information from the Excel file. I think I'm just missing a command to advance the next record. I've checked the Microsoft definition and it left me with more questions...and google has gotten me this far but I still need this little nugget of information to finish off this app.
If there is another way to accomplish the goal I'm open to hearing it.
Thanks,
public static void MergeLabels(string labelDoc)
{
FileInfo fi = new FileInfo(labelDoc);
string labelSave = labelDoc.Replace(".xlsx", ".docx");
string labelTemplet = fi.DirectoryName.ToString() + "\\StockLabelsMerge.docx";
using (ExcelPackage excelPackage = new ExcelPackage(fi))
{
var application = new Microsoft.Office.Interop.Word.Application();
var document = new Microsoft.Office.Interop.Word.Document();
document = application.Documents.Add(Template: labelTemplet);
foreach (var worksheetLoop in excelPackage.Workbook.Worksheets)
{
// this.comboBox1.Items.Add(worksheet.Name);
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets[worksheetLoop.Index];
int colCount = workSheet.Dimension.End.Column;
int rowCount = workSheet.Dimension.End.Row;
string specieSize = string.Empty;
string cabinet = string.Empty;
string jobNumber = string.Empty;
for (int row = 1; row <= rowCount; row++)
{
if (workSheet.Cells[row, 1].Value != null)
{
jobNumber = workSheet.Cells[row, 1].Value.ToString();
if (workSheet.Cells[row, 2].Value != null)
{
cabinet = workSheet.Cells[row, 2].Value.ToString();
if (workSheet.Cells[row, 3].Value != null)
{
specieSize = workSheet.Cells[row, 3].Value.ToString();
foreach (Microsoft.Office.Interop.Word.Field label in document.Fields)
{
if (label.Code.Text.Contains("F1"))
{
label.Select();
application.Selection.TypeText(jobNumber);
}
else if (label.Code.Text.Contains("NoName"))
{
label.Select();
application.Selection.TypeText(cabinet);
}
else if (label.Code.Text.Contains("NoName2"))
{
label.Select();
application.Selection.TypeText(specieSize);
}
//next record?
// label.Next.Result;
}
}
}
}
}
}
document.SaveAs2(FileName: labelSave);
}
}
I've posted it on stack, but so far no one has answered.
https://stackoverflow.com/questions/68474779/how-do-i-advance-to-the-nextrecord-on-microsoft-office-interlop-word-mailmerge-w
If there is another way to accomplish the goal I'm open to hearing it.
Thanks,
public static void MergeLabels(string labelDoc)
{
FileInfo fi = new FileInfo(labelDoc);
string labelSave = labelDoc.Replace(".xlsx", ".docx");
string labelTemplet = fi.DirectoryName.ToString() + "\\StockLabelsMerge.docx";
using (ExcelPackage excelPackage = new ExcelPackage(fi))
{
var application = new Microsoft.Office.Interop.Word.Application();
var document = new Microsoft.Office.Interop.Word.Document();
document = application.Documents.Add(Template: labelTemplet);
foreach (var worksheetLoop in excelPackage.Workbook.Worksheets)
{
// this.comboBox1.Items.Add(worksheet.Name);
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets[worksheetLoop.Index];
int colCount = workSheet.Dimension.End.Column;
int rowCount = workSheet.Dimension.End.Row;
string specieSize = string.Empty;
string cabinet = string.Empty;
string jobNumber = string.Empty;
for (int row = 1; row <= rowCount; row++)
{
if (workSheet.Cells[row, 1].Value != null)
{
jobNumber = workSheet.Cells[row, 1].Value.ToString();
if (workSheet.Cells[row, 2].Value != null)
{
cabinet = workSheet.Cells[row, 2].Value.ToString();
if (workSheet.Cells[row, 3].Value != null)
{
specieSize = workSheet.Cells[row, 3].Value.ToString();
foreach (Microsoft.Office.Interop.Word.Field label in document.Fields)
{
if (label.Code.Text.Contains("F1"))
{
label.Select();
application.Selection.TypeText(jobNumber);
}
else if (label.Code.Text.Contains("NoName"))
{
label.Select();
application.Selection.TypeText(cabinet);
}
else if (label.Code.Text.Contains("NoName2"))
{
label.Select();
application.Selection.TypeText(specieSize);
}
//next record?
// label.Next.Result;
}
}
}
}
}
}
document.SaveAs2(FileName: labelSave);
}
}
I've posted it on stack, but so far no one has answered.
https://stackoverflow.com/questions/68474779/how-do-i-advance-to-the-nextrecord-on-microsoft-office-interlop-word-mailmerge-w