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

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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

--
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

"Roy Carlson" wrote in message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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


--
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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in message
...
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

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in
message
...
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

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.

"Doug Robbins - Word MVP" wrote:

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in
message
...
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

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;PageTime out=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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in
message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in
message
...
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

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.

"Peter Jamieson" wrote:

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;PageTime out=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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in
message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote in
message
...
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

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


--
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

"Roy Carlson" wrote in message
...
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.

"Peter Jamieson" wrote:

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;PageTime out=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

"Roy Carlson" wrote in message
news
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.

"Doug Robbins - Word MVP" wrote:

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in
message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote
in
message
...
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

That did it! Thanks

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in message
...
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.

"Peter Jamieson" wrote:

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;PageTime out=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

"Roy Carlson" wrote in message
news 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.

"Doug Robbins - Word MVP" wrote:

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

currange.Start = currange.Start + 1



--
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

"Roy Carlson" wrote in message
...
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

"Roy Carlson" wrote:

Thank You!!!

"Doug Robbins - Word MVP" wrote:

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


--
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

"Roy Carlson" wrote in
message
news 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.

"Doug Robbins - Word MVP" wrote:

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

--
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

"Roy Carlson" wrote in
message
...
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?

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Roy Carlson" Roy wrote
in
message
...
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: