#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 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 Last edited by macropod; 08-10-2018 at 03:45 PM. Reason: Added code tags |
#4
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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. |
#6
|
||||
|
||||
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 [Fmr MS MVP - Word] |
#7
|
|||
|
|||
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?
|
#8
|
|||
|
|||
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.
|
#9
|
||||
|
||||
Quote:
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 [Fmr MS MVP - Word] |
Tags |
mail merge, vba |
|
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 |