Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-28-2021, 02:17 PM
ylafont ylafont is offline Excel Macro with MSword Mail Merge using 2d Array Windows 10 Excel Macro with MSword Mail Merge using 2d Array Office 2016
Novice
Excel Macro with MSword Mail Merge using 2d Array
 
Join Date: Jul 2021
Posts: 2
ylafont is on a distinguished road
Default 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

Reply With Quote
  #2  
Old 07-29-2021, 08:40 PM
gmayor's Avatar
gmayor gmayor is offline Excel Macro with MSword Mail Merge using 2d Array Windows 10 Excel Macro with MSword Mail Merge using 2d Array Office 2019
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Instead of using an array, you could populate the controls directly from the appropriate worksheet cells.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 07-30-2021, 04:11 AM
ylafont ylafont is offline Excel Macro with MSword Mail Merge using 2d Array Windows 10 Excel Macro with MSword Mail Merge using 2d Array Office 2016
Novice
Excel Macro with MSword Mail Merge using 2d Array
 
Join Date: Jul 2021
Posts: 2
ylafont is on a distinguished road
Default

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.
Reply With Quote
Reply

Tags
array msword excel

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Macro with MSword Mail Merge using 2d Array Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file sureshbvs Word VBA 1 03-12-2021 05:42 AM
Excel Macro with MSword Mail Merge using 2d Array 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
Excel Macro with MSword Mail Merge using 2d Array 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft