Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2013, 09:23 AM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 12-04-2013, 02:57 PM
macropod's Avatar
macropod macropod is offline Code for mail merge to reference saved excel file Windows 7 32bit Code for mail merge to reference saved excel file Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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
PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-05-2013, 09:02 AM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 12-05-2013, 09:52 AM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 12-05-2013, 02:22 PM
macropod's Avatar
macropod macropod is offline Code for mail merge to reference saved excel file Windows 7 32bit Code for mail merge to reference saved excel file Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

Quote:
Originally Posted by jtemp57 View Post
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
No, that's not what's happening. The macro I provided retains all of the mergefields and other content of the document from which it is run. It doesn't simply open a new document. Rather, using the document containing the code, it executes the mailmerge using that document and its mergefields and that process generates a new document containing the mailmerge output.
Quote:
I deleted the .Destination and it works.
Not really; all you've done is killed off the output, which Word will now ask you to specify.
Quote:
Another issue is that it is asking me twice to choose my list from excel before moving on.
To fix that, delete:
.OpenDataSource sPath
Working through the dialog already connects once, so there's no need to do it again. My bad.
Quote:
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?
Without knowing what kind of button, or how you propose to hide it when the output is generated, I can't really say. However, for a more automated approach, move the code to the document's 'ThisDocument' code module and rename it:
Private Sub Document_Open()
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #6  
Old 12-16-2013, 09:09 AM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default

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
Reply With Quote
  #7  
Old 12-16-2013, 02:27 PM
macropod's Avatar
macropod macropod is offline Code for mail merge to reference saved excel file Windows 7 32bit Code for mail merge to reference saved excel file Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

For that I believe you could insert:
.DataSource.QueryString = "SELECT * FROM `My_List`"
after:
.MainDocumentType = wdFormLetters
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #8  
Old 12-16-2013, 03:22 PM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
For that I believe you could insert:
.DataSource.QueryString = "SELECT * FROM `My_List`"
after:
.MainDocumentType = wdFormLetters
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??
Reply With Quote
  #9  
Old 12-16-2013, 03:27 PM
macropod's Avatar
macropod macropod is offline Code for mail merge to reference saved excel file Windows 7 32bit Code for mail merge to reference saved excel file Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #10  
Old 12-16-2013, 06:14 PM
jtemp57 jtemp57 is offline Code for mail merge to reference saved excel file Windows Vista Code for mail merge to reference saved excel file Office 2007
Novice
Code for mail merge to reference saved excel file
 
Join Date: Dec 2013
Posts: 6
jtemp57 is on a distinguished road
Default

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
Reply With Quote
  #11  
Old 12-16-2013, 11:02 PM
macropod's Avatar
macropod macropod is offline Code for mail merge to reference saved excel file Windows 7 32bit Code for mail merge to reference saved excel file Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,224
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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

Tags
mail merge, vbs

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code for mail merge to reference saved excel file 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 for mail merge to reference saved excel file 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

Other Forums: Access Forums

All times are GMT -7. The time now is 08:23 PM.


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