You /may/ be able to do this by issuing a Word VBA OpenDataSource call
that
opens the file using ODBC. This lets you use some of the VBA functions
available in the Jet dialect of SQL. e.g.
Sub OpenCSVviaODBC()
Dim strPathOnly As String
Dim strFileOnly As String
Dim strConnection As String
Dim strQuery As String
' Set the path to your file
strPathOnly = "C:\mypath\"
strFileOnly = "myfielcsv"
strConnection = "DSN=Delimited Text Files;DBQ=" & _
strPathOnly & _
";DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;"
strQuery = "SELECT *, cdbl(mid(mynumber,2)) AS 'mynewnumber' FROM `" &
strFileOnly & "`"
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdDirectory
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:=strConnection, _
SQLStatement:=strQuery, _
SubType:=wdMergeSubTypeWord2000
End Sub
However, it is sometimes hard to get this stuff to work.
Peter Jamieson
Roy Carlson said:
Erg, it's not working. It's still seeing the commas from the field in
the
datasource, and somehow not adding the numbers correctly. I got one of
the
columns working fine, but it doesn't contain any commas or dollar
signs.
I can't remove the symbols from the the datasource, because the system
we
use to export this data can't be changed easily.
If there's any other suggestions you have please let me know.
Otherwise I
will just keep playing with this. Again, there are 2 columns of
numbers,
column 6 and column 13, that need to be totalled after the merge, and
the
totals need to show in under the last row. I've got the macro doing
both
calculations, but the column 6 calculation isn't working right because,
I
believe, there are commas and dollar signs in the column data.
:
Move the .Start of the Range so that it excludes the $ sign
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
One more question. I did make that macro work. I had to add a
little
more
to it to make it work in 2 different colums, but no problem
The trouble I'm having now is that one of merge fields in the data
source
contains numbers with currancy (including the $ and , and .
symbols).
When I
try to do the calculation with that range of numbers, it come up
with
nothing, or with incorrect numbers.
There is one of two things I see that can be done
1) Be sure the macro adds the numbers as currency
2) Remove the extra symbols in the numbers (save the .). I know the
period
isn't an issue because one of the colums I'm adding has periods and
works.
It's the commas (with numbers longer than 3 digits) and the dollar
sign
that
is screwing it up.
What would I have to do to the macro to either compensate for this
or
to
simply remove the symbols from that column before doing the math?
Thanks
:
Thank You!!!
:
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
message
Yeah I got that. But I'm wanting a way to automate this. You
see, I
work
with people who don't know a lot about formulas and what not.
I'd
like to
set it up to d it automatically. I'm thinking that a macro
would
do
it,
but
I have only written simple macros or edited macros that are
already
written.
I've never started one from scratch like this.
:
I should have mentioned that the row needs to be inserted
after
executing
the mailmerge.
--
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
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?
:
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
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
