#1
|
|||
|
|||
Excel Macro with MSword Mail Merge using 2d Array
Not sure where to post this but here goes
First, thank you in advance for the feedback, second i am not programmer but putting my best foot forward. I have created a VBA macro that reads an excel table and creates a few 2D arrays out of the initial table. (This is in excel). I had the macro working perfectly using an excel table as the datasource for a MSword Mail Merge - partial code below. I have a MS word document properly formatted containing all the merge fields (thought this may make this easier) and want to use one of the created arrays as the mail merge Data source, actually only need the last record to be merged. is there a method of using the Array as a data source? or should a different method be used? Any assistance is greatly appreciated, thank you in advance. Code:
objDoc.MailMerge.MainDocumentType = wdFormLetters objDoc.MailMerge.OpenDataSource Name:= _ "C:\Program Files\Excel\Data\ExcelDataFile.xlsm", _ ConfirmConversions:=False, _ ReadOnly:=True, _ LinkToSource:=True, _ AddToRecentFiles:=False, _ PasswordDocument:="", _ PasswordTemplate:="", _ WritePasswordDocument:="", _ WritePasswordTemplate:="", _ Revert:=False, _ Format:=wdOpenFormatAuto, _ Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Program Files\Excel\Data\ExcelDataSource.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:", _ SQLStatement:="SELECT * FROM `ProductionRequest$`", SQLStatement1:="", _ SubType:=wdMergeSubTypeAccess 'objDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord objDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord objDoc.MailMerge.ViewMailMergeFieldCodes = wdToggle 'objDoc.MailMerge.Execute |
#2
|
||||
|
||||
As you only want the last record in the array, I would be inclined to work from an Excel macro. I would also replace the merge fields in the document with Content Controls, then you should be able to populate the controls directly from the array e.g. something like
Code:
Option Explicit Sub GetLastInArray(Arr() As Variant) Dim iRow As Long Dim wdApp As Object Dim wdDoc As Object Dim oCC As Object On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = CreateObject("Word.Application") End If On Error GoTo 0 wdApp.Visible = True Set wdDoc = wdApp.Documents.Add(Template:="C:\Path\Mergedoc.docx") 'the merge document iRow = UBound(Arr, 2) 'The last row of the array For Each oCC In wdDoc.contentcontrols Select Case oCC.Title Case "Title1" oCC.Range.Text = Arr(1, iRow) 'column 1 Case "Title2" oCC.Range.Text = Arr(2, iRow) 'Column 2 'etc End Select Next oCC lbl_Exit: Set oCC = Nothing Set wdDoc = Nothing Set wdApp = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
gmayor
All the macros generating the mail merge and creating the forms are in Excel – so check there! The criteria have changed a bit, now I have to read the initial array and select all matching records based on the selected record. I have already separated all matching records into a new 2D array, still need the last record of the new array for now (I think this may change - waiting on confirmation) allowing the user to select any record of the new array and regenerate the form. I started exporting the new array to a CSV file to be able to use the macros as they currently are. I think your suggestion of content controls will eliminate the merge fields in the main document and the need to save the new array to a CSV, it should work nicely! Let me see if a can get into practice. Thank you for the suggestion. |
Tags |
array msword excel |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file | sureshbvs | Word VBA | 1 | 03-12-2021 05:42 AM |
Need macro to fill the values in msword based on the excel sheet info | ganesang | Word VBA | 50 | 08-29-2018 12:53 AM |
Update & Unlink Specific Merge Field in Word Doc from Mail Merge - Excel VBA | RMerckling | Mail Merge | 16 | 05-17-2018 05:19 PM |
Mail Merge Macro | ch1325 | Word VBA | 2 | 06-08-2015 06:18 AM |
msword email merge doc as pdf | pablo Isl | Mail Merge | 3 | 06-09-2010 07:38 AM |