Totaling merged data in new document

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roy Carlson - 15 Jun 2006 16:33 GMT

How do I total several dollar amounts in a mail merge directory and show that
total at the end of the mail merge, under the column that contains the merge
data? For example:
[merged data from csv file]
PersonA DollarAmount
PersonB DollarAmount
PersonC DollarAmount
[after merge is done]
TotalDollarAmount

Reply to this Message

Doug Robbins - Word MVP - 15 Jun 2006 18:54 GMT

Simplest to set up the mergefields in a table and then add a row at the
bottom of the table and insert a formula from the tools menu.

Signature

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

> How do I total several dollar amounts in a mail merge directory and show
> that
[quoted text clipped - 7 lines]
> [after merge is done]
> TotalDollarAmount

Reply to this Message

Roy Carlson - 15 Jun 2006 22:19 GMT

I've tried that. When I complete the merge, however, I get a total for each
row, and not a total for all rows at the end of the document. Am I missing
something?

> Simplest to set up the mergefields in a table and then add a row at the
> bottom of the table and insert a formula from the tools menu.
[quoted text clipped - 10 lines]
> > [after merge is done]
> > TotalDollarAmount

Reply to this Message

Doug Robbins - Word MVP - 16 Jun 2006 04:40 GMT

I should have mentioned that the row needs to be inserted after executing
the mailmerge.

Signature

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

> I've tried that. When I complete the merge, however, I get a total for
> each
[quoted text clipped - 17 lines]
>> > [after merge is done]
>> > TotalDollarAmount

Reply to this Message

Roy Carlson - 16 Jun 2006 14:34 GMT

Yeah I got that. But I'm wanting a way to automate this. You see, I work
with people who don't know a lot about formulas and what not. I'd like to
set it up to d it automatically. I'm thinking that a macro would do it, but
I have only written simple macros or edited macros that are already written.
I've never started one from scratch like this.

> I should have mentioned that the row needs to be inserted after executing
> the mailmerge.
[quoted text clipped - 20 lines]
> >> > [after merge is done]
> >> > TotalDollarAmount

Reply to this Message

Doug Robbins - Word MVP - 16 Jun 2006 18:31 GMT

The following macro will add a row at the bottom of the table and in the
first cell of that row, it will insert the word "Total" and in the second
cell, it will insert the total of the amounts in column 2 of the table.

Dim i As Long
Dim TotalDollars As Double
Dim atable As Table
Dim currange As Range
Dim newrow As Row
Set atable = ActiveDocument.Tables(1)
TotalDollars = 0
For i = 1 To atable.Rows.Count
Set currange = atable.Cell(i, 2).Range
currange.End = currange.End - 1
TotalDollars = TotalDollars + Val(currange)
Next i
Set newrow = atable.Rows.Add
newrow.Cells(1).Range.InsertAfter "Total"
newrow.Cells(2).Range.InsertAfter Format(TotalDollars, "$#,###.00")

Signature

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

> Yeah I got that. But I'm wanting a way to automate this. You see, I work
> with people who don't know a lot about formulas and what not. I'd like to
[quoted text clipped - 30 lines]
>> >> > [after merge is done]
>> >> > TotalDollarAmount

Reply to this Message

Roy Carlson - 16 Jun 2006 19:44 GMT

Thank You!!!

> The following macro will add a row at the bottom of the table and in the
> first cell of that row, it will insert the word "Total" and in the second
[quoted text clipped - 50 lines]
> >> >> > [after merge is done]
> >> >> > TotalDollarAmount

Reply to this Message

Roy Carlson - 28 Jun 2006 19:50 GMT

One more question. I did make that macro work. I had to add a little more
to it to make it work in 2 different colums, but no problem

The trouble I'm having now is that one of merge fields in the data source
contains numbers with currancy (including the $ and , and . symbols). When I
try to do the calculation with that range of numbers, it come up with
nothing, or with incorrect numbers.

There is one of two things I see that can be done
1) Be sure the macro adds the numbers as currency
2) Remove the extra symbols in the numbers (save the .). I know the period
isn't an issue because one of the colums I'm adding has periods and works.
It's the commas (with numbers longer than 3 digits) and the dollar sign that
is screwing it up.

What would I have to do to the macro to either compensate for this or to
simply remove the symbols from that column before doing the math?

Thanks

> Thank You!!!
>
[quoted text clipped - 52 lines]
> > >> >> > [after merge is done]
> > >> >> > TotalDollarAmount

Reply to this Message

Doug Robbins - Word MVP - 29 Jun 2006 12:11 GMT

Move the .Start of the Range so that it excludes the $ sign

currange.Start = currange.Start + 1

Signature

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

> One more question. I did make that macro work. I had to add a little
> more
[quoted text clipped - 87 lines]
>> > >> >> > [after merge is done]
>> > >> >> > TotalDollarAmount

Reply to this Message

Roy Carlson - 30 Jun 2006 15:16 GMT

Erg, it's not working. It's still seeing the commas from the field in the
datasource, and somehow not adding the numbers correctly. I got one of the
columns working fine, but it doesn't contain any commas or dollar signs.

I can't remove the symbols from the the datasource, because the system we
use to export this data can't be changed easily.

If there's any other suggestions you have please let me know. Otherwise I
will just keep playing with this. Again, there are 2 columns of numbers,
column 6 and column 13, that need to be totalled after the merge, and the
totals need to show in under the last row. I've got the macro doing both
calculations, but the column 6 calculation isn't working right because, I
believe, there are commas and dollar signs in the column data.

> Move the .Start of the Range so that it excludes the $ sign
>
[quoted text clipped - 91 lines]
> >> > >> >> > [after merge is done]
> >> > >> >> > TotalDollarAmount

Reply to this Message

Peter Jamieson - 30 Jun 2006 16:11 GMT

You /may/ be able to do this by issuing a Word VBA OpenDataSource call that
opens the file using ODBC. This lets you use some of the VBA functions
available in the Jet dialect of SQL. e.g.

Sub OpenCSVviaODBC()
Dim strPathOnly As String
Dim strFileOnly As String
Dim strConnection As String
Dim strQuery As String
' Set the path to your file
strPathOnly = "C:\mypath\"
strFileOnly = "myfielcsv"
strConnection = "DSN=Delimited Text Files;DBQ=" & _
strPathOnly & _
";DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;"
strQuery = "SELECT *, cdbl(mid(mynumber,2)) AS 'mynewnumber' FROM `" &
strFileOnly & "`"
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdDirectory
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:=strConnection, _
SQLStatement:=strQuery, _
SubType:=wdMergeSubTypeWord2000
End Sub

However, it is sometimes hard to get this stuff to work.

Peter Jamieson

> Erg, it's not working. It's still seeing the commas from the field in the
> datasource, and somehow not adding the numbers correctly. I got one of
[quoted text clipped - 116 lines]
>> >> > >> >> > [after merge is done]
>> >> > >> >> > TotalDollarAmount

Reply to this Message

Roy Carlson - 30 Jun 2006 17:02 GMT

Isn't there a way to tell this macro that the format of the data its adding
is currency?

Sub TotalGifts()

Dim i As Long
Dim TotalDollars As Double
Dim atable As Table
Dim currange As Range
Dim newrow As Row

Set atable = ActiveDocument.Tables(1)

TotalDollars = FormatCurrency(0)
For i = 1 To atable.Rows.Count
Set currange = atable.Cell(i, 6).Range
currange.End = currange.End - 1
TotalDollars = TotalDollars + Val(currange)
Set newrow = atable.Rows.Add
newrow.Cells(4).Range.InsertAfter "Total"
newrow.Cells(6).Range.InsertAfter Format(TotalDollars, "$#,###.00")
End Sub

I'm assuming that something could be done to:

Set currange = atable.Cell(i, 6).Range
currange.End = currange.End - 1
TotalDollars = TotalDollars + Val(currange)

to setup a format in the calculation, but I don't know what that would be.

> You /may/ be able to do this by issuing a Word VBA OpenDataSource call that
> opens the file using ODBC. This lets you use some of the VBA functions
[quoted text clipped - 146 lines]
> >> >> > >> >> > [after merge is done]
> >> >> > >> >> > TotalDollarAmount

Reply to this Message

Doug Robbins - Word MVP - 30 Jun 2006 18:59 GMT

The following uses the Replace() function to delete the commas from the
data. I have an idea though that it was only introduced with Word XP so you
may need to be using that version or later:

Dim i As Long
Dim TotalDollars As Double
Dim atable As Table
Dim currange As Range
Dim newrow As Row
Set atable = ActiveDocument.Tables(1)
TotalDollars = 0
For i = 1 To atable.Rows.Count
Set currange = atable.Cell(i, 2).Range
currange.End = currange.End - 1
currange.Start = currange.Start + 1
TotalDollars = TotalDollars + Val(Replace(currange.Text, ",", ""))
Next i
Set newrow = atable.Rows.Add
newrow.Cells(1).Range.InsertAfter "Total"
newrow.Cells(2).Range.InsertAfter Format(TotalDollars, "$#,###.00")

Signature

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

> Isn't there a way to tell this macro that the format of the data its
> adding
[quoted text clipped - 202 lines]
>> >> >> > >> >> > [after merge is done]
>> >> >> > >> >> > TotalDollarAmount

Reply to this Message

Roy Carlson - 30 Jun 2006 20:18 GMT

That did it! Thanks

> The following uses the Replace() function to delete the commas from the
> data. I have an idea though that it was only introduced with Word XP so you
[quoted text clipped - 217 lines]
> >> >> >> > >> >> > merge
> >> >> >> > >> >> > data? For example:

Reply to this Message

Rate this thread: