Creating monthly reports in Word using data from Excel

Look Doug I found the answer!  No need for VBA at all, this is what I was looking for.  Thanks for trying.

This article describes how to use the Mail Merge feature in Word to create a list of data that is sorted and separated by a category. This article contains instructions and a sample that you can use to create such a list.

Sort your data file, so that all records with the same value for the key field category (the field upon which you base the sort) appear together, as shown in the sample data file. The following sample list is sorted by the CITY field (CITY is the key field in this example).

Note ¶ designates The words "a paragraph mark" in brackets designate a paragraph mark in the following example. To type a paragraph mark, press ENTER.

For this procedure to work, you must select the "Directory" document type when you set up your main document. To set up your main document as a directory, follow these steps:

1. In a new blank document, start the mail merge. To do this, use one of the following procedures, as appropriate for the version of Word that you are running:
   
   Microsoft Word 2002
   
   On the Tools menu, click Letters and Mailings, and then click Mail Merge Wizard.
   
   Microsoft Office Word 2003
   
   On the Tools menu, click Letters and Mailings, and then click Mail Merge.
   
   Microsoft Office Word 2007 and Microsoft Office Word 2010
   
   On the Mailings tab, click Start Mail Merge, and then click Step by Step Mail Merge Wizard.
2. In the Mail Merge task pane under Select document type, click to select Directory.
3. Click Next: Starting document.
4. Under Select starting document, click to select Use the current document.
5. Click Next: Select recipients.
6. Under Use an existing list, click Browse.
7. In the Select Data Source dialog box, select the sample file you created in the Set Up the Data File section.
8. If the Confirm Data Source dialog box appears, select a data type that is appropriate for the type of file that you created in the Set up the data file section. For example, if you created the file in Microsoft Excel, select either MS Excel Worksheets via DDE (*.xls) or Excel Files via ODBC (*.xls).
9. In the Mail Merge Recipients dialog box, click OK.
10. Using the following example as a guide, insert fields to compare the key field in each row with the key field in the previous row, and to insert one string of text if they are different and another string of text if they are the same.

Example

This example uses the sample data from the Set up the data file section, and it uses { MERGEFIELD CITY} as the key field.

Note To insert the field braces, press CTRL+F9.

{ IF { MERGESEQ } = "1" "{ MERGEFIELD CITY }¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
{ MERGEFIELD CITY }¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶

The fields laid out in this example produce a sorted list as follows:

As in the previous example, the key field in this example is { MERGEFIELD City }. When the value of CITY changes in the data file to a different city, a new page is added to the merged results, and the merge is continued at the top of the next page. To insert the field braces, press CTRL+F9.

{ If { MERGESEQ } = "1" "{ MERGEFIELD CITY }¶
" ""}{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
----------------------------Page Break--------------------------------
{ MERGEFIELD CITY }¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶

Note To insert a page break, either press CTRL+ENTER, or click Break on the Insert menu, select Page Break, and then click OK.

The fields laid out in this example produce a listing on separate pages as follows:

The key field in this example is {MERGEFIELD CITY}. To format the results of the {MERGEFIELD CITY} as all capital letters, you can insert the formatting switch of \* Upper. To insert the field braces, press CTRL+F9.

{ If { MERGESEQ } = "1" "{ MERGEFIELD CITY \* Upper }¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ IF { Place2 } <> { Place1 }"¶
{ MERGEFIELD CITY \* Upper }¶
¶
{ MERGEFIELD EMPLOYEE }{ MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE }{ MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶

The fields laid out in this example produce a listing with the city appearing in all uppercase letters, as follows:

Note You can apply different formatting to the key field { MERGEFIELD City } by selecting the whole field (including the field braces { }), and formatting the field to your preferences. To format the field, click Font on the Format menu.

If you want additional text to appear in the merge document, you must include this text inside the sets of quotation marks in the IF fields. This way, the text appears only if the information in the data source meets the conditions that you define in the IF fields (for example, when the CITY name in the current row of the data source differs from the CITY name in the previous row).

If you place text outside the IF fields in your main document, this text appears in the merge document one time for each new row in the data source, rather than one time for each new key field. In the example that you created in the Set up the data file section, it appears one time for each employee, instead of one time for each city.

This example introduces each city in the list with a short introductory sentence:

{ IF { MERGESEQ } = "1" "These are the sales totals for { MERGEFIELD CITY }:¶
" "" }{ SET Place1 { MERGEFIELD CITY }}¶
{ If { Place2 } <> { Place1 }"¶
These are the sales totals for { MERGEFIELD CITY }:¶
¶
{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" "{ MERGEFIELD EMPLOYEE } { MERGEFIELD SALES }" }{ SET Place2 { MERGEFIELD CITY }}¶

The fields laid out in this example produce a listing with introductory text for each city as follows:

The first IF field in these examples inserts the city name and a paragraph mark for only the first record in the mail merge. For all subsequent records, this IF field inserts nothing (""). It recognizes the first mail-merge record by comparing the MERGESEQ field (which returns the sequence number for the current record) to the numeral "1."

The second IF field inserts a return character (and a page break if you are forcing each city to a new page), the city name, two more return characters, the employee name, and the sales amount (in that order), only if the IF field determines that the current record includes a city name that is different from the previous record. If Word determines that the current record includes a city name that is the same as the previous record, Word inserts only the next employee name and sales amount.

This IF field recognizes the new city name by comparing the text of the bookmarks specified in the two SET fields. The text of the bookmark "Place1" is always equal to the current record's City field, and the text of the bookmark "Place2" is equal to the previous record's City field.