Re: text file for mail merge

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I think what is happening as I am new to merge. It is header delimiters. At
end of row i have a paragraph symbol seperator in text file. I cannot find a
similar in header seperator setting. Will insert your code piece and see what
I can do with it.
Thanks for assistance.

"Doug Robbins - Word MVP" wrote:

I guess I don't really understand what you are doing (or why), but if you
have a document in whcih each paragraph contains the same number of
"entries" separated by commas, running the following code will convert that
text into a table

ActiveDocument.Range.ConvertToTable

Or, if the first row contains field names, you can just use it directly as a
mail merge 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:76732D5F-3181-4866-958B-4AF23E8C257B@xxxxxxxxxxxxxxxx
Got the text file straigtened out all same length now. Still have an issue
in
word. When bringing data in it does not reconize comma seperators. When
recorded macro code shows wdSeperateByCommas this means Commas=2 If you
change it to Comma it then = 0 is there a way to set it to one comma that
is
what the text file has

"Curt" wrote:

Thank You your explanation tells me what I am doing wrong it all makes
sense
now. Thank You Again Will have to correct the way text file is built
Thanks

"Doug Robbins - Word MVP" wrote:

If that is really the text file that you are starting with, you have a
problem because all of the records are not of the same length and there
is a
superfluous comma (or two in some cases at the end of the row which
means
that your data has more fields than the header source.

Assuming that when you open the file in Word, and click on Show/Hide ¶,
each
row terminates in ¶, you could use Edit>Replace and search for ,^p and
replace it with ^p to get rid of the superfluous commas where there is
only
one of them.

If the supefluous commas are just mistakes in your email message, you
should
then be able to use the Convert Text to Table ability of Word.

--
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:A3EC81AC-9835-43D6-BCC2-5B0D40C68994@xxxxxxxxxxxxxxxx
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 stumped
    ... Doug Robbins - Word MVP ... Here is my last attempt at macro recording. ... "entries" separated by commas, running the following code will convert ... mail merge data source. ...
    (microsoft.public.word.vba.beginners)
  • Re: text file for mail merge stumped
    ... Redid macro many times all ways stops and will not run thru complete. ... is headerr record delimiters. ... replace it with ^p to get rid of the superfluous commas where there is ... When I run it stops on delimiter screen. ...
    (microsoft.public.word.vba.beginners)
  • Re: text file for mail merge
    ... Doug Robbins - Word MVP ... replace it with ^p to get rid of the superfluous commas where there is ... My recommendation was to open that file in Word and use a macro ... in which format the data can be used as a data source. ...
    (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)
  • Re: send keys macro solution to linking many txt files please...
    ... it works with commas (the default delimiter) but not tabs. ... author "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" ... Can you confirm whether your> test was using TAB or comma delimited files? ...
    (microsoft.public.access.macros)