Re: text file for mail merge

In my knee jerk reaction I miss read the file. There were some lines with 3
commas. These must represent empty fields. When I set the delimiter in word
for comma it does the job fine. I noticed that in code it did say wdseperate
by commas.
There is only one comma between fields. In debug when on the word commas
commas=2 comma=0 This I dont follow. Will enclose my text file also so you
can see what I am working with. I create the text file then import to a word
table then use table for merge. one other thing I havent figured out yet is
it says to many fields or records at times. Is this due to not enough rows in
table?
Heres text file
Thanks

Name of Entry,Contact Person, Address,City&State,Zip
Willamette Leadership Acedemy,SGM Steven Arbuckel,123,123,111,
American Legion & Friends,Richard Casey,234,234,222,
VFW Post 3965 Aux Jr. Girls,Tina Blackmer,345,345,333,
U.S. War Dogs...Remembered,Fred Hamburg,456,456,444,
Women Marines Association, Chapter OR-3,Debbie Barker,,,,
Marine Corps League Emerald Empire Det.,Mike Barker
Cascade Manor,Frank Blain
Eugene Code Pink says I Miss America,Aria Seligmann
Cub Scout Pack 20,David Kemp
Junk Be Gone,Lisa Archambault
Knight's of Columbus,Raymond J. Miller
Lane County Veteran's Bridge for Freedom,Larry A. Hedrick
Oregon VFW Auxillary President,Iona Kline
McKenzie A's ,David L. Stone
VFW #293,Al bino Bazzi
Emerald Empire Young Marines,Kenneth Norwood
Military Officers of America Association (MOAA) Vehicle,Vince Puleo
Old Timer’s Car Club ,Mykal Taylor
Congressman Peter DeFazio,Frank Van Cleave or Janice Kelly
Miss Lane Co. Scholarship Program,Nicole Akins, Director
THE AMERICAN ROADSTER,LARRY AND MEMORY NELSON
Girl Scouts of Western Rivers Council ,Wendy Elkins/Sheila Logan
Boy Scouts of America Troop 60, Pack 289 and 514,Martin Thompson
Nick Nichols
9 Dogs,fda,dfa,654,333
WER,WEE,654,FJH,222





"Doug Robbins - Word MVP" wrote:

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: text file for mail merge
    ... commas separating each record. ... My recommendation was to open that file in Word and use a macro (just ... Dim RowCount As Long ... TotalFile = TotalFile & Cells ...
    (microsoft.public.word.vba.beginners)
  • Re: text file for mail merge
    ... commas by a carriage return and the replace the individual commas by ... Is there a way to set row seperation as I create the ... Dim RowCount As Long ... TotalFile = TotalFile & Cells ...
    (microsoft.public.word.vba.beginners)
  • Re: Cannot find datasource
    ... by tab characters or commas, and data records separated by ... commas as field delimiters. ... Make sure to use the same field delimiter in ... To leave a data field blank for a specific record, ...
    (microsoft.public.word.docmanagement)