Need help with complicated mail merge with Excel spreadsheet

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?

Alternatively you could achieve a similar effect with a mail merge type of  Directory or Catalog.

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               
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      
                                                      

If you want each employee have a separate table (on a separate page perhaps), then it will be a little more difficult.

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?

Now I know why I can't figure it out.  I'm using 2002.

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
           

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.

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

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

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.

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?

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.

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.

Well I see I did submit it, but afrter I came back

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?

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

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.

Gail, would it? I haven't been able to devote much time since my last comment.

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.

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"}})}

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.  :)

Did you ever consider a catalog merge?  Would that work?