Sum values in table column in merge document(2003)

Topic

New Reply

(Edited by jscher2000 on 26-Apr-05 12:11. Subject made more descriptive.)

I have a Mail Merge doc that uses an hdr file as it’s source. I am stuck with this method and can not change how the merge works. The doc will list all outstanding charges an individual owes. I am using a table to list the info for each charge in the document. I am trying to come up with a way to total the values in each numeric column of the table of these charges so that I can include a total amount owed in the document. Is there anyway to do this?

Thanks

Reply | Quote

Replies

After you have merged to a new document, you can add a row at the end of the table, and use Table | Formula to insert formulas =SUM(ABOVE) in the cells below numeric columns.

Reply | Quote

Thanks for the info but unfortunately I have to use a method that does not require editing the document during or after the merge. My companies program calls the mail merge programmatically and saves the resulting merge doc to a network share so that the process is automated once the intial doc design is done. Do you know if the method you list will work if I place the formula in the last row of the table and can guarantee that the last row will not contain any merge data?

Thanks
Ed

Reply | Quote

If you put the formula in the merge document (the one with the merge fields), it will NOT work. The formula will be repeated for each record. If you have a say in the code that performs the mail merge, the extra row with the formula could be added in that code. If the document is not to be edited at all after the merge, even in code, I don’t know of a way to do what you want.

Reply | Quote

Hans, If the formual runs for each record that is not a problem ebcause all the charges for a individual will be in a single record. The way the process works is it takes 0 to N number of charges and flattens them out so that there is a single record with many columns and each column contains info about one of the unpaid charges. Now we do have to setup a max number of items in teh rpeort so that now record will contain more then 10 charges and that’s OK. If a person has less then 10 charges then the extra columns for unused charge fields are blank or 0. Now that you know that all the hcarges are in a single record should I be able to perform the SUM operation you originally mentioned?

Thanks
Ed

Reply | Quote

Yes, that should work. I misunderstood your original question, I thought you wanted to sum the numbers in a column. To sum the numbers in a row (record), add an 11th column to the table, and use Table | Formula to insert the formula =SUM(LEFT) into the new cell. This formula will be copied correctly to each row in the merged document. You may want to right-align the text.

Reply | Quote

Is it possible run some VB/VBA code between the end of the merge and the time the document is saved? If so, at least in theory, your VB/VBA code could visit each table in the document and add a bottom row with the necessary field codes. However, I only use merge on rare occasions, so this is just speculation on my part.

Reply | Quote