Microsoft Office Forums - View Single Post - Excel Macro with MSword Mail Merge using 2d Array

ylafont · 07-28-2021, 02:17 PM

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