 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / May 2008text file for mail merge
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 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.  SignatureHope 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 > 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 [quoted text clipped - 21 lines] > Close #FF > End Sub 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 > 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 [quoted text clipped - 27 lines] > > Close #FF > > End Sub Show us the code of the macro that you have. SignatureHope 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 > got around most of the problems. created a recorded macro in word just > fine. [quoted text clipped - 40 lines] >> > Close #FF >> > End Sub 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 > Show us the code of the macro that you have. > [quoted text clipped - 42 lines] > >> > Close #FF > >> > End Sub 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. SignatureHope 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 > Selection rows.convert when in debug Commas=2 Comma=0 how do you get > comma=1 [quoted text clipped - 81 lines] >> >> > Close #FF >> >> > End Sub 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 > I think that we have gone off the track here. > [quoted text clipped - 103 lines] > >> >> > Close #FF > >> >> > End Sub 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. SignatureHope 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 > In my knee jerk reaction I miss read the file. There were some lines with > 3 [quoted text clipped - 157 lines] >> >> >> > Close #FF >> >> >> > End Sub 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 > 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 [quoted text clipped - 170 lines] > >> >> >> > Close #FF > >> >> >> > End Sub 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 > 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 [quoted text clipped - 174 lines] > > >> >> >> > Close #FF > > >> >> >> > End Sub 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. SignatureHope 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 > Got the text file straigtened out all same length now. Still have an issue > in [quoted text clipped - 212 lines] >> > >> >> >> > Close #FF >> > >> >> >> > End Sub 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. > 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 [quoted text clipped - 222 lines] > >> > >> >> >> > Close #FF > >> > >> >> >> > End Sub 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 > 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 [quoted text clipped - 226 lines] > > >> > >> >> >> > Next > > >> > >> >> >> > Print #FF, TotalFile 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. SignatureHope 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 > Redid macro many times all ways stops and will not run thru complete. > problem [quoted text clipped - 352 lines] >> > >> > >> >> >> > Next >> > >> > >> >> >> > Print #FF, TotalFile 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 > 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. [quoted text clipped - 236 lines] > >> > >> > >> > >> > >> > >> in which format the data can be used as a data source. Not always. Do you really need a macro to do it all for you? SignatureHope 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 > when I recorde the macro I go all the way thru to close and save the doc. > It [quoted text clipped - 271 lines] >> >> > >> > >> >> >> > >> > >> in which format the data can be used as a data source. 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 > Not always. Do you really need a macro to do it all for you? > [quoted text clipped - 239 lines] > >> >> > >> > > Logan > >> >> > >> > > Boy Scouts of America Troop 60, Pack 289 and 514,Martin 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. SignatureHope 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 > Yes as the ones I am building this for are not as computer literate as I > am. [quoted text clipped - 286 lines] >> >> >> > >> > > Logan >> >> >> > >> > > Boy Scouts of America Troop 60, Pack 289 and 514,Martin 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 > 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 [quoted text clipped - 225 lines] > >> >> >> > >> > > >> >> >> > >> > > In my knee jerk reaction I miss read the file. There were 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. > 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 [quoted text clipped - 225 lines] > >> >> >> > >> > > >> >> >> > >> > > In my knee jerk reaction I miss read the file. There were Glad that you got there in the end. SignatureHope 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 > Just to let you know with a lot of determination. I was finnaly able to > fully [quoted text clipped - 260 lines] >> >> >> >> > >> > > In my knee jerk reaction I miss read the file. There >> >> >> >> > >> > > were
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.