Re: text file for mail merge stumped

when I recorde the macro I go all the way thru to close and save the doc. It
does the merge as you would expect to a new doc. This is why I don't follow
as a macro it will not perform. Would not these action produce the same as
manually.
Hope I responded correctly.
Thank You

"Doug Robbins - Word MVP" wrote:

What happens if you manually create the mail merge main document and
manually attach C:\Parade\ZZZ.txt as the data source and execute the 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:9CDE1766-9F00-415C-BCDE-D873E4D907DE@xxxxxxxxxxxxxxxx
Redid macro many times all ways stops and will not run thru complete.
problem
is headerr record delimiters. Set comma for field (debug shows commas=2)
set
record om enter(no symbol for symbolin text file. Get error message record
1
to many data fields then after click on error record 2 to many data fields
click on it again and the merge completes. How can I get past header
record
delimiters and data fields. I know I am missing something somewhere.
Here is my last attempt at macro recording. Text file all records are now
all same size 5 columns. code only records complete rows into text file.
here is my last try at macro recording.
Thanks

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 5/17/2008 by Curtiss A. Greer
'
ChangeFileOpenDirectory "C:\Parade\"
Documents.Open FileName:="Letter.doc", ConfirmConversions:=False,
ReadOnly _
:=False, AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate _
:="", Revert:=False, WritePasswordDocument:="",
WritePasswordTemplate:="" _
, Format:=wdOpenFormatAuto
Selection.MoveDown Unit:=wdLine, Count:=1
Selection.TypeParagraph
Selection.TypeParagraph
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\ZZZ.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="",
SQLStatement1 _
:=""
ActiveDocument.MailMerge.EditMainDocument
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Name_of_Entry"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Contact_Person"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"Address"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _
"CityState"
Selection.TypeParagraph
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range,
Name:="Zip_"
Selection.TypeParagraph
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * 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 ))" _
& ""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
ActiveWindow.ActivePane.VerticalPercentScrolled = 91
ActiveWindow.ActivePane.VerticalPercentScrolled = 0
CommandBars("Stop Recording").Visible = False
End Sub





"Curt" wrote:

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

.

Relevant Pages

  • Re: text file for mail merge stumped
    ... Do you really need a macro to do it all for you? ... Please reply to the newsgroup unless you wish to avail yourself of my ... Doug Robbins - Word MVP ... "entries" separated by commas, ...
    (microsoft.public.word.vba.beginners)
  • 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: Totaling merged data in new document
    ... but it doesn't contain any commas or dollar signs. ... totals need to show in under the last row. ... Be sure the macro adds the numbers as currency ... Dim TotalDollars As Double ...
    (microsoft.public.word.mailmerge.fields)
  • 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: 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)