Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Mailmerge or Pivot Table?
Hi everyone
I'm not sure where to post this, so I've started here! I have a simple table in Excel 2003 and am working with Word 2003. The first column of data has a number of countries (which is repeated) and in the relevant rows, there is information relating to the country (such as year, region and population at the end of that year). I need to collate the data to labels - at the centre top of each label the name of the country and then columns of data. In this example, the first column would be "Year" and the second would be "Region" and the third would be "Population". I know that I can use mailmerge but I've only been able to do this and have the country in the first column, year in the second, region in the third and population in the fourth. The repeating country name is not necessary as I need this as a title on the label. I also know that I could use a Pivot Table to collate the data but I've only been able to get it to count (or do other numerical analysis of the data), rather than give the actual data. I may be "barking up the wrong tree" with these approaches so I'd be grateful for thoughts about how this could be done. Finally, I know that I could set an AutoFilter to display the data by individual countries but this wouldn't allow me to print the labels as I'd like (or could it in some way?). Thanks for your time and patience! |
#2
|
|||
|
|||
Word does not really have the ability to perform a "multiple items per
condition (=key field)" mailmerge. See the "Multiple items per condition" item under the "Special merges" section of fellow MVP Cindy Meister's website at http://homepage.swissonline.ch/cindy...r/MergFram.htm Or, if you create a Catalog (on in Word XP and later, it's called Directory) type mailmerge main document with the mergefields in the cells of a one row table in the mailmerge main document with the keyfield in the first cell in the row and then execute that merge to a new document and then run the following macro, it will create separate tables with the records for each key field in them. With a bit of further development, you may be able to get it to do what you want. ' Macro to create multiple items per condition in separate tables from a directory type mailmerge Dim source As Document, target As Document, scat As Range, tcat As Range Dim data As Range, stab As Table, ttab As Table Dim i As Long, j As Long, k As Long, n As Long Set source = ActiveDocument Set target = Documents.Add Set stab = source.Tables(1) k = stab.Columns.Count Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1, numcolumns:=k - 1) Set scat = stab.Cell(1, 1).Range scat.End = scat.End - 1 ttab.Cell(1, 1).Range = scat j = ttab.Rows.Count For i = 1 To stab.Rows.Count Set tcat = ttab.Cell(j, 1).Range tcat.End = tcat.End - 1 Set scat = stab.Cell(i, 1).Range scat.End = scat.End - 1 If scat tcat Then ttab.Rows.Add j = ttab.Rows.Count ttab.Cell(j, 1).Range = scat ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True ttab.Rows.Add ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n Else ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n End If Next i -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Tosca" wrote in message ... Hi everyone I'm not sure where to post this, so I've started here! I have a simple table in Excel 2003 and am working with Word 2003. The first column of data has a number of countries (which is repeated) and in the relevant rows, there is information relating to the country (such as year, region and population at the end of that year). I need to collate the data to labels - at the centre top of each label the name of the country and then columns of data. In this example, the first column would be "Year" and the second would be "Region" and the third would be "Population". I know that I can use mailmerge but I've only been able to do this and have the country in the first column, year in the second, region in the third and population in the fourth. The repeating country name is not necessary as I need this as a title on the label. I also know that I could use a Pivot Table to collate the data but I've only been able to get it to count (or do other numerical analysis of the data), rather than give the actual data. I may be "barking up the wrong tree" with these approaches so I'd be grateful for thoughts about how this could be done. Finally, I know that I could set an AutoFilter to display the data by individual countries but this wouldn't allow me to print the labels as I'd like (or could it in some way?). Thanks for your time and patience! |
#3
|
|||
|
|||
Thank you Doug for the information. I'll certainly investigate the options
that you've suggested. I'll post back with my results. "Doug Robbins" wrote in message ... Word does not really have the ability to perform a "multiple items per condition (=key field)" mailmerge. See the "Multiple items per condition" item under the "Special merges" section of fellow MVP Cindy Meister's website at http://homepage.swissonline.ch/cindy...r/MergFram.htm Or, if you create a Catalog (on in Word XP and later, it's called Directory) type mailmerge main document with the mergefields in the cells of a one row table in the mailmerge main document with the keyfield in the first cell in the row and then execute that merge to a new document and then run the following macro, it will create separate tables with the records for each key field in them. With a bit of further development, you may be able to get it to do what you want. ' Macro to create multiple items per condition in separate tables from a directory type mailmerge Dim source As Document, target As Document, scat As Range, tcat As Range Dim data As Range, stab As Table, ttab As Table Dim i As Long, j As Long, k As Long, n As Long Set source = ActiveDocument Set target = Documents.Add Set stab = source.Tables(1) k = stab.Columns.Count Set ttab = target.Tables.Add(Range:=Selection.Range, numrows:=1, numcolumns:=k - 1) Set scat = stab.Cell(1, 1).Range scat.End = scat.End - 1 ttab.Cell(1, 1).Range = scat j = ttab.Rows.Count For i = 1 To stab.Rows.Count Set tcat = ttab.Cell(j, 1).Range tcat.End = tcat.End - 1 Set scat = stab.Cell(i, 1).Range scat.End = scat.End - 1 If scat tcat Then ttab.Rows.Add j = ttab.Rows.Count ttab.Cell(j, 1).Range = scat ttab.Cell(j, 1).Range.Paragraphs(1).PageBreakBefore = True ttab.Rows.Add ttab.Cell(j + 1, 1).Range.Paragraphs(1).PageBreakBefore = False For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n Else ttab.Rows.Add For n = 2 To k Set data = stab.Cell(i, n).Range data.End = data.End - 1 ttab.Cell(ttab.Rows.Count, n - 1).Range = data Next n End If Next i -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP |
#4
|
|||
|
|||
Hi Doug (again!)
I managed to tweak the macro and the way that I generated the mail merge document. It works a treat now. Thank you again for your input. I hope that this will be of help to someone else who finds themself requiring such capability. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table ODBC | Tables | |||
Table headers/footers and layout | Page Layout | |||
How to Avoid Word 2003 Table Style Problems | Tables | |||
Table AutoFormats vs. Table Styles confusion | Tables | |||
Macro for a Table in a MailMerge | Mailmerge |