×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

Best of Excel

Mail Merge in Excel by SkipVought
Posted: 24 Sep 03 (Edited 21 Dec 10)

Mail Merge in Word has some limitations.

Here's a way to perform a Mail Merge in Excel.

A. Setting up the data.

 1. Make a separate sheet for your data table
 2. If your data is in another application like Access, use Data/Get External data to query the columns and rows that you want.
    If your data is already in Excel, if you have formatted your table properly (one table per sheet, headings in row 1 starting in column A, table is contiguous) then you can use the technique in step 2 as well.

B. Setting up the "form"

   The objective is to MAP data from selected columns in your Source Data Table to the Form, using Named Ranges.  If you do not know what a Named Range is, please refer to Excel HELP.

 1. Format the page(s) the way that you want your form to look
 2. Each Merge Cell should have no other data in it.
 3. Each Merge Cell should be named with the same name as the column heading on the data sheet.  
    NOTE: if any data column heading contains a space, then substitute an UNDERSCORE character for each space. For instance, a data heading like First Name should have a corresponding Merge Field name of First_Name.

C. Running Mail Merge

 1. Select the rows that you want from your data using the AutoFilter or Advanced Filter if you filter in place.
 2. Run MergePrint from Tools/Macro/Macros or a button.  

D. The code that runs the Mail Merge

 There is a procedure (macro) called MergePrint and a function called RangeName.  Paste both of these into a MODULE in the Visual BASIC Editor.  If you wish, you insert a Button on your sheet Form to run MergePrint.

CODE

Function RangeName(sName As String) As String
    RangeName = Application.Substitute(sName, " ", "_")
End Function
Sub MergePrint()
'set up your merge form by naming the merge fields _
 with the same name as the data fields you are importing.
'if any data field contains spaces, then substitute an _
 UNDERSCORE character for each space in the name.
    Dim wsForm As Worksheet, wsData As Worksheet
    Dim sRngName As String, r As Long, c As Integer
    Set wsForm = Worksheets("My Form") 'change to your sheet name
    Set wsData = Worksheets("My Data") 'change to your sheet name
    With wsData.Cells(1, 1).CurrentRegion
        For r = 2 To .Rows.Count
            If Not wsData.Cells(r, 1).EntireRow.Hidden Then
                For c = 1 To .Columns.Count
                    sRngName = wsData.Cells(1, c).Value
                    Range(RangeName(sRngName)).Value = wsData.Cells(r, c)
                Next
                wsForm.PrintOut
            End If
        Next
    End With
End Sub

 

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close