Help with mail merge using DATABASE command with excel file.

dcdonnelly · #1 05-31-2024, 10:03 AM

Hello,
I'm the volunteer registration/scheduling flack for our local soccer team's annual tournament. I'm trying to use a mail merge to print a card with team rosters for each individual game in the tournament. The main mail merge sheet has the game number and both team names. The data sheet has the team names and the players' names and numbers.

I've attempted to use the solution detailed here https://www.msofficeforums.com/mail-...-multiple.html but I keep getting an "cannot open data source" error message.

I've attached the test excel file and word document that I've been working on. I'm wondering if I've gone down the wrong solution path (I've looked at a bunch of add-ins and other posts) or if I have a bad setting or two. Any help would be greatly appreciated.

Guessed · #2 06-02-2024, 04:58 PM

I would forget about doing this in Word and just stick with Excel and use a macro to build a series of worksheets per game.

I modified your Excel file by adding Table Formatting (aka List Objects) to the two input sheets and added a macro that builds the game lists.

Code:

Sub BuildGameRosters()
  Dim loDraw As ListObject, loRoster As ListObject
  Dim lrGame As ListRow, rngPlayer As Range
  Dim aWb As Workbook, aSht As Worksheet, i As Integer
  Dim sGame As String, sAwayTeam As String, sHomeTeam As String
  
  Set aWb = ActiveWorkbook
  Set loDraw = aWb.Sheets("TestRosterCardData").ListObjects(1)
  Set loRoster = aWb.Sheets("Rosters").ListObjects(1)
  
  'If the workbook contains more sheets than two, remove extras before recreating
  Application.DisplayAlerts = False
  Do While aWb.Sheets.Count > 2
    aWb.Sheets(aWb.Sheets.Count).Delete
  Loop
  
  For Each lrGame In loDraw.ListRows
    sGame = lrGame.Range.Cells(1, 1).Value
    sAwayTeam = lrGame.Range.Cells(1, 2).Value
    sHomeTeam = lrGame.Range.Cells(1, 3).Value
    
    Set aSht = aWb.Sheets.Add(After:=aWb.Worksheets(aWb.Worksheets.Count))
    aSht.Name = "Game " & sGame
    aSht.Cells(1, 1).Value = aSht.Name
    aSht.Cells(3, 1).Value = "Home Team"
    aSht.Cells(3, 2).Value = "Away Team"
    aSht.Cells(4, 1).Value = sHomeTeam
    aSht.Cells(4, 2).Value = sAwayTeam
    aSht.Range("A1:B4").Font.Bold = True
    
    loRoster.Range.AutoFilter Field:=1       'clear filter
    loRoster.Range.AutoFilter Field:=1, Criteria1:=sHomeTeam
    i = 0
    For Each rngPlayer In loRoster.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
      i = i + 1
      aSht.Cells(4, 1).Offset(i, 0) = rngPlayer.Cells(1, 2) & ", " & rngPlayer.Cells(1, 3)
    Next rngPlayer
    
    loRoster.Range.AutoFilter Field:=1       'clear filter
    loRoster.Range.AutoFilter Field:=1, Criteria1:=sAwayTeam
    i = 0
    For Each rngPlayer In loRoster.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows
      i = i + 1
      aSht.Cells(4, 2).Offset(i, 0) = rngPlayer.Cells(1, 2) & ", " & rngPlayer.Cells(1, 3)
    Next rngPlayer
    aSht.Columns.AutoFit
    loRoster.Range.AutoFilter Field:=1       'clear filter
  Next lrGame
End Sub

dcdonnelly · #3 06-03-2024, 11:52 AM

Andrew,

Wow, I didn't expect you to do all of that. It looks perfect for my needs. Thank you. Cutting and pasting both teams' rosters for 225 games was in my future and now it's not. Thanks again.

David

Guessed · #4 06-03-2024, 03:26 PM

If you are going to use the macro, make sure you read through it carefully to understand what it is doing first - and save a copy of your workbook in case you find a problem. Note that it includes a 'delete all sheets loop' so that you can rerun it after tweaking the code.

This code is a working draft and could do with some refinements. For instance, you should probably add some formatting flourishes to make the result look better and maybe you also want the shirt numbers, location/court/field?, date, time etc. I would probably want to include some page setup info in the code too so all the sheets can be printed without having to do a lot of repetitive page setup fiddles.

I believe that Excel doesn't have a hard number limit to how many sheets it can hold but that could be an issue. I haven't ever used a workbook with 200+ sheets so maybe that will be a bit hard to manage but I expect it will work.

dcdonnelly · #5 06-06-2024, 11:01 AM

Good suggestions. I already had broken the games down into blocks of 50 or so (just to make changes/reprints easier) and everything worked well. I did add the shirt numbers (I'm using that term from now on, everyone here uses "jersey number" and "shirt number" is way cooler) but I didn't mess with any formatting. We have a fancy mail-merge score card with time/location/teams/score data. This sheet is used just to confirm the players are registered on the team. For our purposes, it's perfect. Thanks again for all your help, it is greatly appreciated.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Mail Merge - Create seperate Mail merge pdf for each sheets of an excel file	sureshbvs	Word VBA	1	03-12-2021 05:42 AM
Use only blue cells in database in mail merge	coopfab	Mail Merge	2	10-02-2019 06:18 AM
Mail Merge with integrated Database	CarlCarl2	Mail Merge	1	01-24-2017 05:53 AM
Mail merge only sends some email from Access database	rsakai2	Mail Merge	4	10-30-2013 10:32 PM
mail merge - database server	rec	Mail Merge	1	09-29-2013 08:03 PM