Mailmerge Excel with a Master Word Template (via VBA)

stevenel · #1 08-09-2018, 01:43 PM

Hey guys,

I currently have two files. One is my excel with data in it. One is my word (which I call my master) where you can find all my mergefields. Instead of opening word every time, choosing the excel location then printing it out, I wanna code it via VBA. Here's my code:

Public Sub Print()

Set wDApp = CreateObject("Word.Application")

wDApp.Documents.Open (blablabla)
wDApp.Visible

With wdApp.MailMerge
.MainDocumentType = wdDirectory
.OpenDataSource_
Name:"blablabla2"_
LinkToSource=True, AddToRecentFiles, _
End With

End Sub

My first question is regarding blablabla2. How do I code it so VBA knows I want him to use the current workbook i am currently on (my workbook has only one worksheet). My second question is how do I make the code working? I know it doesn't end there and it crashes so any help would be appreciated

thank you very much in advance

macropod · #2 08-09-2018, 02:51 PM

See: https://www.msofficeforums.com/mail-...html#post61731

stevenel · #3 08-10-2018, 01:00 PM

Macropod, you are amazing as usual. (You helped me 4 weeks ago for a Word merge and I appreciated it alot)

However, I need help once again.

This is the code I've used based on your link:

Code:

Sub RunMerge()

    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    
    With wdapp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        'Open the mailmerge main document
        Set wddoc = .Documents.Open("H:\Data\Steven\Master Prototype\Garanties et Options\Master - Garanties.docx")
        With wddoc
             .ActiveWindow.View.Type = wdNormalView
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdDirectory
                'Connect to the data source
                .OpenDataSource Name:=strWorkbookName, AddToRecentFiles:=False, _
                Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _
                & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'"
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                End With
                'Define the output
                .Destination = wdSendToNewDocument
                'Excecute the merge
                .Execute
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = wdAlertsAll
        'Display Word and the document
        .Visible = True
    End With
    
End Sub

When I merge (aka run the code), a pop up comes up saying the excel file is already in use which is normal because my code is in the excel file itself which is indeed OPENED. I have to click on "Ok" everytime (meaning it'll open a copy in read only). Is there a way I can skip that step via a code? I've tried many things (legit 4 hours wasting my time) and I'm lost. (please view screenshot attached)

When I press ok, it merges and it's perfect so this is my only issue. I've uploaded the excel file, the master "merge" document word. Thank you so much in advance. In the excel file, I can't send it with the module in it because it's a csv and it deletes it automatically so please paste your code into the chat if you can, thanks a lot. I uploaded the CSV on a different site since yours doesn't support CSV uploads.

https://ufile.io/zfaja

macropod · #4 08-10-2018, 03:49 PM

Try changing:
.OpenDataSource Name:=strWorkbookName, AddToRecentFiles:=False, _
to:
.OpenDataSource Name:=strWorkbookName, ReadOnly:=True, AddToRecentFiles:=False, _

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.

stevenel · #5 08-13-2018, 06:21 AM

Code:

Sub RunMerge()

    Dim strWorkbookName As String
    strWorkbookName = ThisWorkbook.FullName
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    
    With wdapp
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        'Open the mailmerge main document
        Set wddoc = .Documents.Open("H:\Data\Steven\Master Prototype\Garanties et Options\Master - Garanties.docx")
        With wddoc
             .ActiveWindow.View.Type = wdNormalView
            With .MailMerge
                'Define the mailmerge type
                .MainDocumentType = wdDirectory
                'Connect to the data source
                .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, AddToRecentFiles:=False, _
                Revert:=False, Format:=wdOpenFormatAuto, Connection:="Data Source=" _
                & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Sheet1'"
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                End With
                'Define the output
                .Destination = wdSendToNewDocument
                'Excecute the merge
                .Execute
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            'Close the mailmerge main document
            .Close False
        End With
        'Restore the Word alerts
        .DisplayAlerts = wdAlertsAll
        'Display Word and the document
        .Visible = True
    End With
    
End Sub

I've tried this code and I still have the same pop up (see picture in my initial post). I decided to be more patient and an Excel pop up showed up. I attached the image.
Let me know if you have the solution, thank you in advance.

https://files.fm/u/8p63cjxa

Here's my CSV file without the module in it so if you test it out (copy paste my code in a new module in the file). I re-uploaded the CSV because I just checked the link in my past post is a dead link.

macropod · #6 08-13-2018, 08:14 PM

Since the files you're merging are CSV files, why are you involving Excel at all? Word can perform a mailmerge with a CSV file as the data source.

stevenel · #7 08-14-2018, 06:12 AM

Initially, it is an Excel file. Before I merge, I save it as a csv file because there are too many mergefields (more than 255) in my original Merge Excel File. If I save it as xslm, Word cannot merge it because it stops at 255 mergefields. That's why I save it as csv and merge. Is there a solution?

stevenel · #8 08-14-2018, 01:38 PM

Never mind, I solved it easily by naming a variable the workbook name, then closing the workbook, then using that workbook name once again

Thanks for your help, I'm all set.

macropod · #9 08-14-2018, 03:05 PM

Quote:

Originally Posted by stevenel

Initially, it is an Excel file. Before I merge, I save it as a csv file because there are too many mergefields (more than 255) in my original Merge Excel File. If I save it as xslm, Word cannot merge it because it stops at 255 mergefields. That's why I save it as csv and merge. Is there a solution?

In that case, instead of:
strWorkbookName = ThisWorkbook.FullName
you should be populating strWorkbookName with whatever the name of your CSV file is. For example:
strWorkbookName = Split(ThisWorkbook.FullName,".xls")(0) & ".CSV"
Simply closing & reopening the workbook won't give you access to the additional fields.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Suppress blank lines in mailmerge of excel reports into word document	beefcake2000	Mail Merge	1	11-10-2017 12:54 PM
Master template and subtemplate docs	ab-os	Word	1	05-02-2017 01:23 PM
Template From MailMerge Document	LouGibson	Mail Merge	1	06-13-2016 03:14 PM
how to copy addresses in word document to excel/mailmerge list	msnarayanan	Mail Merge	4	10-17-2015 03:17 PM
Excel, transfer data from Master Sheet to sub sheets, using key word from column	anvqui	Excel Programming	9	06-16-2015 01:35 PM