Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#7
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#8
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#9
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#10
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#12
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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 |
#14
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Totaling merged data in new document
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: |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Come? | Mailmerge | |||
SQL Prompt When opening merged documents | Mailmerge | |||
Macro to add data to merged document | Mailmerge | |||
View Merged Data Button not Working | Mailmerge |