#1
|
|||
|
|||
Code for mail merge to reference saved excel file
I am trying to get a code that will open a data source for mail merge and let you choose which source and path you want at that time. I can get the macro to work referencing a specific path such as Name="C:mydocuments\personal\etc...." but I need one that will let me select from different saved files. Here is what I have currently:
Sub LOTO_PRINT_TAGS() ' ' LOTO_PRINT_TAGS Macro ' ' Dim sPath As String ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource sPath ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False End With End Sub |
#2
|
||||
|
||||
To be able to choose a datasource, your document need to be a 'normal' one, not a mailmerge one; otherwise the datasource is already chosen. Only after that can the rest of your code come into play. Accordingly, try:
Code:
Sub RunMailmerge() Dim sPath As String 'Kill off any previous datasource connections Application.DisplayAlerts = wdAlertsNone 'Let the user select a datasource With Application.Dialogs(wdDialogMailMergeOpenDataSource) If .Show = -1 Then sPath = .Name Else GoTo NoMerge End If End With If sPath = "" Then GoTo NoMerge 'Run the merge With ActiveDocument With .Mailmerge .MainDocumentType = wdFormLetters .OpenDataSource sPath .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 'Disconnect from the datasource .MainDocumentType = wdNotAMergeDocument End With .Saved = True End With NoMerge: Application.DisplayAlerts = wdAlertsAll End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
macropod,
I ran it and it lets me choose my datasource, but I want it to remain with the current word document that has all the merge fields set. The code you gave me opens up a new document with no merge fields. I need the merge fields to remain in my label because the users will not know how to set all this up. Do I change the .Destination = wdSendToNewDocument to something else? |
#4
|
|||
|
|||
I deleted the .Destination and it works.
Another issue is that it is asking me twice to choose my list from excel before moving on. What do I need to modify below in order for it to stop doing this? I also have created a form with a button attached to the code below. How do I get the form to pop up with the Word Document so that I can run the code below? Code:
Private Sub Print_LOTO_Tags_Click() Dim sPath As String 'Kill off any previous datasource connections Application.DisplayAlerts = wdAlertsNone 'Let the user select a datasource With Application.Dialogs(wdDialogMailMergeOpenDataSource) If .Show = -1 Then sPath = .Name Else GoTo NoMerge End If End With If sPath = "" Then GoTo NoMerge 'Run the merge With ActiveDocument With .MailMerge .MainDocumentType = wdFormLetters .OpenDataSource sPath .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=False 'Disconnect from the datasource .MainDocumentType = wdNotAMergeDocument End With .Saved = True End With NoMerge: Application.DisplayAlerts = wdAlertsAll End Sub |
#5
|
||||
|
||||
Quote:
Quote:
Quote:
.OpenDataSource sPath Working through the dialog already connects once, so there's no need to do it again. My bad. Quote:
Private Sub Document_Open()
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#6
|
|||
|
|||
I fixed the issue of it asking me twice to select my list.
I want the rest of the Macro to run automatically after I select my file. I need it to select "OLE DB Database File" and then always select the same list from the excel file say "My_List". Then have it finished ready to be able to select the Print options. Here is what comes up when I record the macro to do this, but where do I insert this into the code you provided me with? and what do I need to add/delete to make it function properly? Code:
SQLStatement:="SELECT * FROM `My_List`", SQLStatement1:="", SubType:= _ wdMergeSubTypeAccess |
#7
|
||||
|
||||
For that I believe you could insert:
.DataSource.QueryString = "SELECT * FROM `My_List`" after: .MainDocumentType = wdFormLetters
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
It doesn't do anything different when I input the above. I tried moving it to a couple different places but nothing changes. I still have to select my data source and excel list. Any other suggestions??
|
#9
|
||||
|
||||
Selecting the datasource is unavoidable if you want to use the same mailmerge document with different datasources.
Might I suggest a different approach: configure the Word document to use a single datasource and simply copy & rename the Excel workbooks accordingly.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#10
|
|||
|
|||
I'm not quite following what you mean?? However, can I have the code choose data from the current workbook instead of choosing the data source? This way it would merge with the excel book that contains the updated information instead of the template that is used?
What you currently helped me out with works, but I need it as simplistic as can be. The people using this would not understand how to choose data and then the list. I was trying to get it all under one click of a button, but maybe I'm trying to do something not possible. Thanks |
#11
|
||||
|
||||
Simple:
1. Save a copy of the workbook with the name 'DataSource.xlsm'; 2. Set up a mailmerge whose datasource is your 'DataSource.xlsm' file; 3. Whenever you want to run a mailmerge, save a copy of the workbook as 'DataSource.xlsm', then run the mailmerge. That way, Word need never be concerned with changing datasources - for all it 'knows', 'DataSource.xlsm' is the same file it's always worked with. You talk of doing this with 'one click of a button'. For that, you could have a button in each of the Excel workbooks to call a common macro that saves the copy then runs the mailmerge. The user need never touch Word for the process.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
Tags |
mail merge, vbs |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VB code: populate combobox from columns in Excel file | billybeach | Outlook | 2 | 04-27-2013 04:38 AM |
Mail Merge Code (Default Display) | ochiha_ita | Mail Merge | 3 | 04-22-2013 04:04 AM |
Code to stop as "save as" if the mail merge has not been done | tonywatsonmail | Mail Merge | 4 | 04-27-2012 01:57 AM |
Mail Merge saved to single .pdf | treetop | Mail Merge | 1 | 08-10-2011 03:28 PM |
Mail merge Field Code Manipulation | macjnr | Mail Merge | 0 | 09-10-2009 11:37 AM |