text file for mail merge

[Curt]

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

 

[Doug Robbins - Word MVP]

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]

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]

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]

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]

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]

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]

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]

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

 

[Curt]

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

 

[Doug Robbins - Word MVP]

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]

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.

 

[Curt]

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

 

[Doug Robbins - Word MVP]

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]

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]

Not always. Do you really need a macro to do it all for you?

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

Yes as the ones I am building this for are not as computer literate as I am.
Most are of a passing gewneration as I am becomeing a Part of. This is part
of what I put together to make it easier to put on the Veteran's Day Parade.
My limited knowledge doesn't follow what you mean by manual application. I
can make the merge work but as I said overcoming the stops would frasel many.
I went the route of the text file so as to speed up the action. Having word
search entire excel sheet was to slow. The text file eliminates any rows with
missing data. and then creates file. Can it be done. I sure hope so We have
a few women Marines who help with the mailing that is what this is for.
Thanks

 

[Doug Robbins - Word MVP]

If you are going to create a macro to create a mail merge main document, you
would be better off just to create that main document manually and save it
with the data source attached and get your users to use that. Rather than
going the text file route, you would be far better off opening the Excel
file and sorting it in descending order on the field that may contain blanks
so that they are at the bottom of the sheet and can be eliminated from the
merge operation.

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

Thanks for the direction. I do better in excel than word. Not that both are
not needed. Will see if I can solve this way.
Thanks Again

 

[Curt]

Just to let you know with a lot of determination. I was finnaly able to fully
automate mail merge with no errors. It took a few macros in both excel and
word but is sucessful. My focus has allways been if you can think of it you
can do it.
Thanks so much for your assistance.