Data Source

[chrisyfaulkner]

Hi,

What Visual Basic code do I need to link an Excel data source to my mail merge .doc?

[awais.hafeez]

Hi Chris,

Thanks for your inquiry. You can connect an Excel spreadsheet as an external data source to your input Word template document via the Dynamic Data Exchange (DDE) system by using the code something like below:

Dim
doc As New Document(“C:\test\f1.docx”)

Dim
excelDS As String = "c:\test\Book1.xlsx"

Dim
mms As MailMergeSettings = doc.MailMergeSettings

mms.DataSource
= excelDS

mms.MainDocumentType
= MailMergeMainDocumentType.FormLetters

mms.DataType
= MailMergeDataType.Spreadsheet

mms.LinkToQuery
= True

mms.ViewMergedData
= True

mms.Query
= "SELECT * FROM " +
mms.DataSource

doc.Save(“c:\test\out.docx”)

Moreover, I have attached sample Word and Spreadsheet files here for your reference.

I hope, this will help.

Best Regards,

[chrisyfaulkner]

Thanks for the reply I will be looking to add it to the code below can you help me?

// Open an existing document.
Document doc = new Document(“C:\Documents and Settings\chris.faulkner\My Documents\MDT00295.doc”);

// Fill the fields in the document with user data.
doc.MailMerge.Execute(
new string[] { “FIRE_OFFICER”, “TODAYS_DATE”, “PLAN_NO”, “COMMENT_1”, “PREM_ADD1”, “PREM_ADD2”, “APPL_NAME”, “DATE_RECEIVED” },
new object[] { “131”, “27/01/2012”,“21”, “Very good”, “Milbank”, “London”, “Mr Smith”, “23/01/2012” });

// Send the document in Word format to the client browser with an option to save to disk or open inside the current browser.
doc.Save(“C:\Documents and Settings\chris.faulkner\My Documents\TestFile Out.odt”); //ContentDisposition.Inline, null);

[imran.rafique]

Hi Chris,

Thanks for your details. Please follow up the code snippet:

var resp = System.Web.HttpContext.Current.Response;<o:p></o:p>

// Open an existing document.

Document doc = new Document("C:\\Documents and Settings\\chris.faulkner\\My Documents\\MDT00295.doc");

// Fill the fields in the document with user data.

doc.MailMerge.Execute(new string[] { "FIRE_OFFICER", "TODAYS_DATE", "PLAN_NO", "COMMENT_1", "PREM_ADD1", "PREM_ADD2", "APPL_NAME", "DATE_RECEIVED" }, new object[] { "131", "27/01/2012", "21", "Very good", "Milbank", "London", "Mr Smith", "23/01/2012" });

// Send the document in Word format to the client browser with an option to save to disk or open inside the current browser.

doc.Save(resp, "Aspose.Words.Demo.doc", ContentDisposition.Inline, SaveOptions.CreateSaveOptions(SaveFormat.Doc));

resp.End();

Note: Code snippet is valid for .NET 2.0 Client Profile.

Hope this will help.

[chrisyfaulkner]

Thanks for the reply. Another question I have is:
Where does the data source be added into this code?

[imran.rafique]

Hi Chris,

Thanks for your inquiry. Please follow up the code snippet:

var resp = System.Web.HttpContext.Current.Response;

String[] fieldNames = { "FIRE_OFFICER", "TODAYS_DATE", "PLAN_NO" };

Object[] fieldValues = new Object[3];

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook("d:/MergeFieldExcel/Book1.xlsx");

Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];

fieldValues[0] = worksheet.Cells[1, 0].Value;

fieldValues[1] = worksheet.Cells[1, 1].Value;

fieldValues[2] = worksheet.Cells[1, 2].Value;

// Open an existing document.

Document doc = new Document("d:/MergeFieldExcel/print.docx");

doc.MailMerge.Execute(fieldNames, fieldValues);

doc.Save("d:/MergeFieldExcel/printOut2.docx");

// Send the document in Word format to the client browser with an option to save to disk or open inside the current browser.

doc.Save(resp, "Aspose.Words.Demo.doc", ContentDisposition.Inline, SaveOptions.CreateSaveOptions(SaveFormat.Doc));

resp.End();

Note: Code snippet is valid for .NET 2.0 Client Profile.

Hope this will help.