Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-09-2018, 01:43 PM
stevenel stevenel is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2018
Posts: 17
stevenel is on a distinguished road
Default Mailmerge Excel with a Master Word Template (via VBA)


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
Reply With Quote
  #2  
Old 08-09-2018, 02:51 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

See: http://www.msofficeforums.com/mail-m...html#post61731
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 08-10-2018, 01:00 PM
stevenel stevenel is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2018
Posts: 17
stevenel is on a distinguished road
Default

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
Attached Images
File Type: png prob 1.png (20.8 KB, 9 views)
Attached Files
File Type: docx Master - Garanties.docx (83.5 KB, 2 views)

Last edited by macropod; 08-10-2018 at 03:45 PM. Reason: Added code tags
Reply With Quote
  #4  
Old 08-10-2018, 03:49 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 08-13-2018, 06:21 AM
stevenel stevenel is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2018
Posts: 17
stevenel is on a distinguished road
Default

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.
Attached Images
File Type: png excel.png (19.1 KB, 6 views)
Reply With Quote
  #6  
Old 08-13-2018, 08:14 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 08-14-2018, 06:12 AM
stevenel stevenel is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2018
Posts: 17
stevenel is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 08-14-2018, 01:38 PM
stevenel stevenel is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Jul 2018
Posts: 17
stevenel is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 08-14-2018, 03:05 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by stevenel View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Tags
mail merge, vba

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 04:54 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft