Re: text file for mail merge

I think that we have gone off the track here.

I understood that what you had to start with was a text file containing

R1F1,R1F2,...R1Fn,,,R2F1,R2F2,...R2Fn,,,.

where R1F1 is the first field in the first record and the are n fields in
each record, with the fields being separated by commas and there are 3
commas separating each record.

My recommendation was to open that file in Word and use a macro (just
Edit>Replace) to replace three commas with a carriage return (¶)

Then you would have

R1F1,R1F2,...R1Fn¶
R2F1,R2F2,...R2Fn¶

etc.

in which format the data can be used as a data source.

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

"Curt" <Curt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2D48DEEB-8D9A-4757-B2F3-1FAF3F5AA774@xxxxxxxxxxxxxxxx
Selection rows.convert when in debug Commas=2 Comma=0 how do you get
comma=1
this may be sticking point not sure
Thanks for assistance
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 5/10/2008 by Curtiss A. Greer
'
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2,
NumColumns:= _
5, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitContent
Selection.Rows.ConvertToText Separator:=wdSeparateByCommas,
NestedTables:= _
True
Selection.Range.InsertDatabase Format:=0, Style:=0,
LinkToSource:=False, _
Connection:="", SQLStatement:= _
"SELECT Name_of_Entry, Contact_Person, Address, CityState, Zip_
FROM
C:\Parade\ZZZ.txt WHERE ((Name_of_Entry IS NOT NULL ) AND (Contact_Person
IS
NOT NULL ) AND (Address IS NOT NULL ) AND (CityState IS NOT NULL ) AND
(Zip_
IS NOT NULL ))" _
& "", PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument _
:="", WritePasswordTemplate:="", DataSource:="C:\Parade\ZZZ.txt",
From:= _
-1, To:=-1, IncludeFields:=True
CommandBars("Stop Recording").Visible = False
End Sub

"Doug Robbins - Word MVP" wrote:

Show us the code of the macro that you have.

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

"Curt" <Curt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FC1164E0-ED93-481E-99F9-1706CE2CCF9E@xxxxxxxxxxxxxxxx
got around most of the problems. created a recorded macro in word just
fine.
When I run it stops on delimiter screen. Is there code that will set
the
delimiter selection for macros?
Thanks

"Doug Robbins - Word MVP" wrote:

I would open the text file in Word and then use a macro to replace the
three
commas by a carriage return (vbCr) and the replace the individual
commas
by
tabs, though the last step is probably not necessary as with each
record
in
a separate paragraphs as a result of the first replacement, the file
should
then be able to be used as a data source for mail merge.

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

"Curt" <Curt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:38756D0A-CF4A-485E-BF04-2F186EBB0EEB@xxxxxxxxxxxxxxxx
Have text file for merge purpose. cells are seperated by a comma
rows
are
seperated by there commas. Mail merge selection does not as I can
see
reconize
3 comma seperation. Is there a way to set row seperation as I create
the
text file.
The following works fine to create the text file.
Thanks

Sub SaveData2()
Dim FF As Long
Dim RowCount As Long
Dim ColCount As Long
Dim TotalFile As String
FF = FreeFile
Open "C:\Parade\ZZZ.txt" For Output As #FF
For RowCount = 1 To Cells(Rows.Count, "A").End(xlUp).row
If RowCount > 1 Then TotalFile = TotalFile & vbCrLf
For ColCount = 1 To Cells(RowCount,
Columns.Count).End(xlToLeft).Column
If ColCount > 1 Then TotalFile = TotalFile & ","
TotalFile = TotalFile & Cells(RowCount, ColCount).Value
Next
Next
Print #FF, TotalFile
Close #FF
End Sub








.

Relevant Pages

  • Re: text file for mail merge
    ... Please reply to the newsgroup unless you wish to avail yourself of my ... commas by a carriage return and the replace the individual commas ... Dim RowCount As Long ... TotalFile = TotalFile & Cells ...
    (microsoft.public.word.vba.beginners)
  • Re: text file for mail merge
    ... created a recorded macro in word just fine. ... commas by a carriage return and the replace the individual commas by ... Dim RowCount As Long ... TotalFile = TotalFile & Cells ...
    (microsoft.public.word.vba.beginners)
  • Re: Excel Quotation Marks!
    ... If there are commas in your data then you will get quotes with a macro, ... When a macro runs Excel is thrown ... Dim CurrRow As Range ...
    (microsoft.public.excel.misc)
  • Re: text file for mail merge
    ... Sub Macro2() ... ' Macro recorded 5/10/2008 by Curtiss A. Greer ... Dim RowCount As Long ... TotalFile = TotalFile & Cells ...
    (microsoft.public.word.vba.beginners)
  • RE: De-concatenate but maintain relationship/record structure
    ... You could try a macro. ... Dim rngData As Range ... but some of these are also multiple values separated by commas. ... I need to separate all cells that have multiple values separated by commas ...
    (microsoft.public.excel.programming)
Loading