Need help with complicated mail merge with Excel spreadsheet

I have a table in a word document I would like to fill with info in a spreadsheet.  The problem is the information is serveral rows long in the spreadsheet and I really don't want to do "Date1" "Date2" etc.  This spreadsheet will be revised every month and I need to mail merge the information into this table.  For example, the spreadsheets says:

Date             Employee Name        Points          Current Points      Code        Description
11/18/05       Employee A               -10             540                     2003a      Audit
11/22/05       Employee A               -50             490                     2005j       Audit
11/28/05       Employee A               -30             460                     2004g      Audit
11/01/05       Employee B               -100            890                    2019        Audit
11/16/05       Employee B               -5                885                   1601a       T-Bred

I can't do a traditional merge as it will print one page per line.  I need three lines to go into the table in my Word document.  Help!

Gail
gail2323Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
If you want the three rows to appear in the Word table as they are in the spreadsheet, you could just copy and paste.

If not, how do you want them laid out in the Word document?
0
GrahamSkanRetiredCommented:
Alternatively you could achieve a similar effect with a mail merge type of  Directory or Catalog.
0
gail2323Author Commented:
We have 200 employees and that would be a total pain to copy and paste all of that info.  Besides, this will continue updating throughout the year so by the end of the year an employee could have three pages full of information.  I thought that since I have to updated the spreadsheet monthly, I could just mail merge the information into this table. (The employees get this sheet to let them know where they stand in this program and my boss wants to use this form rather than sending the employee the spreadsheet.)

Date      Points +/-      CurrentPoints      Code      Description               
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

GrahamSkanRetiredCommented:
If you want each employee have a separate table (on a separate page perhaps), then it will be a little more difficult.
0
GrahamSkanRetiredCommented:
Ah, I see that you do. Mailmerge is not designed to do that, but some VBA could be devised that could split the table after a Directory or Catalog mailmerge has been run. What version of Office are you using?
0
gail2323Author Commented:
Now I know why I can't figure it out.  I'm using 2002.
0
GrahamSkanRetiredCommented:
The macro would look something like the code below.
If you are ggod at VBA, you will probably be able to modify it. If not, I think we'll need to know more precisely what you want.

One document per employee or just page breaks between tables?
Which column will have the employee name?

Are these to be the table column headers?
Date     Points +/-     CurrentPoints     Code     Description


Sub SplitTable()
    Dim strEmployee As String
    Dim tbl As Table
    Dim rw As Row
    Dim rng As Range
    Dim t As Integer
    Dim c As Integer
    Dim bFlag As Boolean
    t = 1
    bFlag = True
    Do While bFlag
        Set tbl = ActiveDocument.Tables(t)
        strEmployee = Left$(tbl.Cell(1, 1).Range.Text, Len(tbl.Cell(1, 1).Range.Text) - 2)
        'Create another row at the beginning of the table
        tbl.Rows.Add tbl.Rows(1)
        'fill the first cell with the employee name
        tbl.Cell(1, 1).Range.Text = strEmployee
        'split it off, delete the trailing columns and make into text
        tbl.Split 2
        Do While tbl.Columns.count > 1
            tbl.Columns.Last.Delete
        Loop
        tbl.ConvertToText " "
       
        bFlag = False
        'redefine the current table object
        Set tbl = ActiveDocument.Tables(t)
        'look for change of ID
        For Each rw In tbl.Rows
            If strEmployee <> Left$(rw.Cells(1).Range.Text, Len(rw.Cells(1).Range.Text) - 2) Then
                Set rng = rw.Range
                rng.Collapse wdCollapseStart
                rng.InsertBreak wdPageBreak
                Debug.Print Len(strEmployee), strEmployee
                bFlag = True 'new ID found, so carry on
                t = t + 1
                Exit For
            End If
        Next rw
        tbl.Columns(1).Delete
    Loop
End Sub
           
0
GrahamSkanRetiredCommented:
In 2002, I think the Mailmerge type is Directory. In 2000 it's called Catalog.
See if you can produce one big table using it.
0
gail2323Author Commented:
Honestly, I've never used VBA.  Yes, those are the column headers and they are side by side so that I can go down one column and then up to the next for more room.  It will be one document per employee.  Can I email the file so you can see what I'm talking about?

Gail
0
eddiejCommented:
This could be easier in Access. An access report can display the information exactly how you want it.

Does the report have to go to word? Meaning it will be changed regularly, or will it be a set thing?

this would be a fairly simple database. If you have access and would like to give it a try let me know and i can walk you through it.
eddiej
0
gail2323Author Commented:
The only problem with Access is that it doesn't do formulas to calculate the running totals.  I guess I could do it in access, but I only know how to do a report using the Wizard; I haven't had time to figure out how to do a complicated one.
0
eddiejCommented:
If you want to give it a try it isn't that complicated of a report, but the wizard won't do it.

Just to get a bit of a background. Have you ever modified a report created with the wizard?, Have you ever created a form in access? What running totals do you want, Totals for the person, or totals for everyone?
0
GrahamSkanRetiredCommented:
You may. My email address is in my profile (use the naturalists one). I'm off to a Christmas party now, so I won't be talking again until tomorrow.
0
GrahamSkanRetiredCommented:
Before I went to a Chistmas party, I tried to invite you to send the file, but I see that I failed to submit it. I am back from the party and have tried to correct this tex t many times, but the answer id Yes. Pleause use the first one (naturalists). I am not dure if you mean the source (Excel)  or the  Word Mailmerge Main document. I would probably need them both.
0
GrahamSkanRetiredCommented:
Well I see I did submit it, but afrter I came back
0
GrahamSkanRetiredCommented:
Gail, I am have received two files form you. There is an Excel spreadsheet that corresponds to the data you list in your question.
The other is a single-page Word document.

At the top of the page (not in the Header) is a one row, two column with a logo and some heading text.
Below that there are five FormFields laid out with tabs in two lines across the page. They are labelled 'Name:' , 'Branch:', 'Hire Date:' 'Starting Points:' and 'Eligible:'
Beneath that is 12 column table which extends to the near the bottom of the page.
There are six different labels in the first row, with columns 7 to 12 repeating those in column 1 to 6, so that it could be viewed as two tables side-by-side. The vertical border between columns 6 and 7 is thicker that the others, thus reinforcing that view.
There is some text after the table.

I am trying to decide whether it is worth doing a mail merge, or whether to just do it directly from code. The double of the table would make it very difficult to use mail-merge. Is that layout mandatory? Also form fields are not really compatible with MailMerge. Do you use them as such then as such, by protecting the document for forms?
0
gail2323Author Commented:
Graham,

The columns don't have to be side by side.  That just gave me more room to write and not have to use so many pages.  Don't worry about the form fields.  I just use them when we get a new employee and have to type their name and hire date real quick.  Those can be deleted, but if we do merge the info, I need to be able to put the employee name and branch in those spots.

Honestly, if you could figure out a way to mailmerge it would be great; I've never dealt with code before.  I have a hard enough time with formulas!  :)

I'll have to admit I am so grateful to finding this website and your help, but on the other hand, it shows me how absolutely stupid I am.  Talk about a false sense of security!  

Gail
0
LakeladyCommented:
I know I am being a total buttinski...but would a pivot table solve this problem. It would let the user create running totals (with just a few simple formulas or choices in the layout) and would allow her to print one report for each employee...just a thought if the coding and merging seem beyond her needs.
0
GrahamSkanRetiredCommented:
Gail, would it? I haven't been able to devote much time since my last comment.
0
GrahamSkanRetiredCommented:
I can't advise you on a pivot table, except that I undertand the is Excel's name for a report.

Keeping to the mail merge side, your Excel has two columns, +Points and -Points, while your mailmerge document has one column +/- points. Do you intend to create a single column in Excel =(+Points - -Points)? That would be easier that trying to do it at the Word end.
0
GrahamSkanRetiredCommented:
OK Forget about the Points columns. It was a bit of a fiddle (requiring careful mouse control), because the fields are sometimes blank and hence non-numeric.

It looks something like this. {} pairs are entered using Ctrl+F9.

{ = ({IF {MERGEFIELD "+Points"} "" 0 {MERGEFIELD "+Points"}} - {IF {MERGEFIELD "-Points"} "" 0 {MERGEFIELD "-Points"}})}
0
gail2323Author Commented:
I could use just the one column in Excel for points.  As far as a pivot table, I've never had much luck using those.  I've tried using the help tool, but it just gets stuck in the hairspray.  :)
0
GrahamSkanRetiredCommented:
You should create a new Main document for the Mailmerge.
Using the MailMerge Wizard,
Step 1, Select document type. Choose Directory
Step 2, Select starting  document. Use the current document.
Step 3, Select recipients. Use existing list, Browse.. for the Excel file and select the Table.
Click on the Date column header to sort it. Then click on the Employee column to do a prime sort on that.
Step 4, Arrange your directory. Insert a one-row five column table. Insert the merge fields in each column as appropriate.

Toggle to the VBA editor with Alt+F11. Locate the new document's project and paste the code below into its ThisDocument code module.

To run it successfully, ensure that it is the only document in the Word application. Also it must be newly opened, i.e. it will not work twice without reloading.

Option Explicit
Private WithEvents oApp As Word.Application
Dim strEmployee As String


Private Sub Document_New()
    If oApp Is Nothing Then
        Set oApp = ThisDocument.Application
    End If
End Sub

Private Sub Document_Open()
    If oApp Is Nothing Then
        Set oApp = ThisDocument.Application
    End If
End Sub


Private Sub oApp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
    InsertHeaderRow DocResult
End Sub

Private Sub oApp_MailMergeBeforeMerge(ByVal Doc As Document, ByVal StartRecord As Long, ByVal EndRecord As Long, Cancel As Boolean)
    strEmployee = ""
End Sub

Private Sub oApp_MailMergeBeforeRecordMerge(ByVal Doc As Document, Cancel As Boolean)
Dim docRes As Document
Dim rng As Range
    For Each docRes In Documents
        If Doc.Name <> docRes.Name Then
            Exit For
        End If
    Next docRes
           
    If ThisDocument.MailMerge.DataSource.DataFields("Employee").Value <> strEmployee Then
        If strEmployee <> "" Then
            Set rng = docRes.Range
            rng.Collapse wdCollapseEnd
            rng.InsertBreak wdSectionBreakNextPage
            InsertHeaderRow docRes
        End If
        strEmployee = ThisDocument.MailMerge.DataSource.DataFields("Employee").Value
        Set rng = docRes.Range
        rng.Collapse wdCollapseEnd
        rng.InsertAfter "Name: " & strEmployee & vbTab & "Branch: " & vbTab & "Hire Date:" & vbCr & vbCr
        rng.InsertAfter "Starting Points: " & vbTab & "Eligible: " & vbCr & vbCr
       
    End If
End Sub

Sub InsertHeaderRow(Doc As Document)

    Dim rw As Row
    Set rw = Doc.Tables(Doc.Tables.Count).Rows.Add(Doc.Tables(Doc.Tables.Count).Rows(1))
    rw.Cells(1).Range.Text = "Date"
    rw.Cells(2).Range.Text = "Points +/-"
    rw.Cells(3).Range.Text = "Current Points"
    rw.Cells(4).Range.Text = "Code"
    rw.Cells(5).Range.Text = "Description"
    rw.Range.Font.Bold = True
   
End Sub



0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joanne M. OrzechManager, Document Services CenterCommented:
Did you ever consider a catalog merge?  Would that work?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.