Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Splitter Macro - Separating Files from mail merge
I found this macro and it works FOR ME which is surprising but I need help in
modifying it. Right now it assigns the date and a numbered index at the end of the filename. I have bookmarks and reference fields in mail merge document. Is there a way I can use the data from my reference fields {ref companynumber},{ref company}as the unique filename for each new document. 122366,ABC Company 156336,NYC Company 236993,Microsoft Company If references won't work. I can use bookmarks instead. Thanks Location where macro was found http://www.gmayor.com/individual_merge_letters.htm The macro assigned Sub Splitter() ' splitter Macro ' Macro created 16-08-98 by Doug Robbins to save each letter created by a ' mailmerge as a separate file. ' With minor modifications by Graham Mayor 10-02-03 Dim mask As String Selection.EndKey Unit:=wdStory Letters = Selection.Information(wdActiveEndSectionNumber) mask = "ddMMyy" Selection.HomeKey Unit:=wdStory Counter = 1 While Counter Letters DocName = "c:\DOCUMENTS\" & Format(Date, mask) & " " & LTrim$(Str$(Counter)) ActiveDocument.Sections.First.Range.Cut Documents.add With Selection .Paste .EndKey Unit:=wdStory .MoveLeft Unit:=wdCharacter, Count:=1 .Delete Unit:=wdCharacter, Count:=1 End With ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument ActiveWindow.Close Counter = Counter + 1 Wend End Sub |
#2
|
|||
|
|||
Here's a method that I have used that involves creating a separate
catalog type mailmerge maindocument which creates a word document containing a table in each row of which would be your data from the database that you want to use as the filename. You first execute that mailmerge, then save that file and close it. Then execute the mailmerge that you want to create the separate files from and with the result of that on the screen, run a macro containing the following code and when the File open dialog appears, select the file containing the table created by the first mailmerge ' Throw Away Macro created by Doug Robbins ' Dim Source As Document, oblist As Document, DocName As Range, DocumentName As String Dim i As Long, doctext As Range, target As Document Set Source = ActiveDocument With Dialogs(wdDialogFileOpen) .Show End With Set oblist = ActiveDocument Counter = 1 For i = 1 To oblist.Tables(1).Rows.Count Set DocName = oblist.Tables(1).Cell(i, 1).Range DocName.End = DocName.End - 1 'Change the path in the following command to suit where you want to save the documents. DocumentName = "I:\WorkArea\Documentum\" & DocName.Text Set doctext = Source.Sections(i).Range doctext.End = doctext.End - 1 Set target = Documents.Add target.Range.FormattedText = doctext target.SaveAs FileName:=DocumentName target.Close Next i -- 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 "Parisa" wrote in message ... I found this macro and it works FOR ME which is surprising but I need help in modifying it. Right now it assigns the date and a numbered index at the end of the filename. I have bookmarks and reference fields in mail merge document. Is there a way I can use the data from my reference fields {ref companynumber},{ref company}as the unique filename for each new document. 122366,ABC Company 156336,NYC Company 236993,Microsoft Company If references won't work. I can use bookmarks instead. Thanks Location where macro was found http://www.gmayor.com/individual_merge_letters.htm The macro assigned Sub Splitter() ' splitter Macro ' Macro created 16-08-98 by Doug Robbins to save each letter created by a ' mailmerge as a separate file. ' With minor modifications by Graham Mayor 10-02-03 Dim mask As String Selection.EndKey Unit:=wdStory Letters = Selection.Information(wdActiveEndSectionNumber) mask = "ddMMyy" Selection.HomeKey Unit:=wdStory Counter = 1 While Counter Letters DocName = "c:\DOCUMENTS\" & Format(Date, mask) & " " & LTrim$(Str$(Counter)) ActiveDocument.Sections.First.Range.Cut Documents.add With Selection .Paste .EndKey Unit:=wdStory .MoveLeft Unit:=wdCharacter, Count:=1 .Delete Unit:=wdCharacter, Count:=1 End With ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument ActiveWindow.Close Counter = Counter + 1 Wend End Sub |
#3
|
|||
|
|||
Do I replace DocName with the name of the field? What is the proper syntax to concatenate two fields together? "Doug Robbins" wrote: Here's a method that I have used that involves creating a separate catalog type mailmerge maindocument which creates a word document containing a table in each row of which would be your data from the database that you want to use as the filename. You first execute that mailmerge, then save that file and close it. Then execute the mailmerge that you want to create the separate files from and with the result of that on the screen, run a macro containing the following code and when the File open dialog appears, select the file containing the table created by the first mailmerge ' Throw Away Macro created by Doug Robbins ' Dim Source As Document, oblist As Document, DocName As Range, DocumentName As String Dim i As Long, doctext As Range, target As Document Set Source = ActiveDocument With Dialogs(wdDialogFileOpen) .Show End With Set oblist = ActiveDocument Counter = 1 For i = 1 To oblist.Tables(1).Rows.Count Set DocName = oblist.Tables(1).Cell(i, 1).Range DocName.End = DocName.End - 1 'Change the path in the following command to suit where you want to save the documents. DocumentName = "I:\WorkArea\Documentum\" & DocName.Text Set doctext = Source.Sections(i).Range doctext.End = doctext.End - 1 Set target = Documents.Add target.Range.FormattedText = doctext target.SaveAs FileName:=DocumentName target.Close Next i -- 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 "Parisa" wrote in message ... I found this macro and it works FOR ME which is surprising but I need help in modifying it. Right now it assigns the date and a numbered index at the end of the filename. I have bookmarks and reference fields in mail merge document. Is there a way I can use the data from my reference fields {ref companynumber},{ref company}as the unique filename for each new document. 122366,ABC Company 156336,NYC Company 236993,Microsoft Company If references won't work. I can use bookmarks instead. Thanks Location where macro was found http://www.gmayor.com/individual_merge_letters.htm The macro assigned Sub Splitter() ' splitter Macro ' Macro created 16-08-98 by Doug Robbins to save each letter created by a ' mailmerge as a separate file. ' With minor modifications by Graham Mayor 10-02-03 Dim mask As String Selection.EndKey Unit:=wdStory Letters = Selection.Information(wdActiveEndSectionNumber) mask = "ddMMyy" Selection.HomeKey Unit:=wdStory Counter = 1 While Counter Letters DocName = "c:\DOCUMENTS\" & Format(Date, mask) & " " & LTrim$(Str$(Counter)) ActiveDocument.Sections.First.Range.Cut Documents.add With Selection .Paste .EndKey Unit:=wdStory .MoveLeft Unit:=wdCharacter, Count:=1 .Delete Unit:=wdCharacter, Count:=1 End With ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument ActiveWindow.Close Counter = Counter + 1 Wend End Sub |
#4
|
|||
|
|||
http://www.gmayor.com/individual_merge_letters.htm
-- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (supplemented version of Microsoft's Legal Users' Guide) http://addbalance.com/usersguide See also the MVP FAQ: http://www.mvps.org/word which is awesome! --------- --------- --------- --------- --------- --------- This message is posted to a newsgroup. Please post replies and questions to the newsgroup so that others can learn from my ignorance and your wisdom. "Parisa" wrote in message ... I found this macro and it works FOR ME which is surprising but I need help in modifying it. Right now it assigns the date and a numbered index at the end of the filename. I have bookmarks and reference fields in mail merge document. Is there a way I can use the data from my reference fields {ref companynumber},{ref company}as the unique filename for each new document. 122366,ABC Company 156336,NYC Company 236993,Microsoft Company If references won't work. I can use bookmarks instead. Thanks Location where macro was found http://www.gmayor.com/individual_merge_letters.htm The macro assigned Sub Splitter() ' splitter Macro ' Macro created 16-08-98 by Doug Robbins to save each letter created by a ' mailmerge as a separate file. ' With minor modifications by Graham Mayor 10-02-03 Dim mask As String Selection.EndKey Unit:=wdStory Letters = Selection.Information(wdActiveEndSectionNumber) mask = "ddMMyy" Selection.HomeKey Unit:=wdStory Counter = 1 While Counter Letters DocName = "c:\DOCUMENTS\" & Format(Date, mask) & " " & LTrim$(Str$(Counter)) ActiveDocument.Sections.First.Range.Cut Documents.add With Selection .Paste .EndKey Unit:=wdStory .MoveLeft Unit:=wdCharacter, Count:=1 .Delete Unit:=wdCharacter, Count:=1 End With ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument ActiveWindow.Close Counter = Counter + 1 Wend End Sub |
#5
|
|||
|
|||
In the catalog mailmerge that this method requires you to use, insert both
of the mergefields into cell one of the table in the catalog mailmerge main document. Leave DocName as it is. When you run the macro (if you follow the instructions exactly) DocName will be "loaded" with the filename created by concatenating the two mergefields. -- 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 "Parisa" wrote in message ... Do I replace DocName with the name of the field? What is the proper syntax to concatenate two fields together? "Doug Robbins" wrote: Here's a method that I have used that involves creating a separate catalog type mailmerge maindocument which creates a word document containing a table in each row of which would be your data from the database that you want to use as the filename. You first execute that mailmerge, then save that file and close it. Then execute the mailmerge that you want to create the separate files from and with the result of that on the screen, run a macro containing the following code and when the File open dialog appears, select the file containing the table created by the first mailmerge ' Throw Away Macro created by Doug Robbins ' Dim Source As Document, oblist As Document, DocName As Range, DocumentName As String Dim i As Long, doctext As Range, target As Document Set Source = ActiveDocument With Dialogs(wdDialogFileOpen) .Show End With Set oblist = ActiveDocument Counter = 1 For i = 1 To oblist.Tables(1).Rows.Count Set DocName = oblist.Tables(1).Cell(i, 1).Range DocName.End = DocName.End - 1 'Change the path in the following command to suit where you want to save the documents. DocumentName = "I:\WorkArea\Documentum\" & DocName.Text Set doctext = Source.Sections(i).Range doctext.End = doctext.End - 1 Set target = Documents.Add target.Range.FormattedText = doctext target.SaveAs FileName:=DocumentName target.Close Next i -- 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 "Parisa" wrote in message ... I found this macro and it works FOR ME which is surprising but I need help in modifying it. Right now it assigns the date and a numbered index at the end of the filename. I have bookmarks and reference fields in mail merge document. Is there a way I can use the data from my reference fields {ref companynumber},{ref company}as the unique filename for each new document. 122366,ABC Company 156336,NYC Company 236993,Microsoft Company If references won't work. I can use bookmarks instead. Thanks Location where macro was found http://www.gmayor.com/individual_merge_letters.htm The macro assigned Sub Splitter() ' splitter Macro ' Macro created 16-08-98 by Doug Robbins to save each letter created by a ' mailmerge as a separate file. ' With minor modifications by Graham Mayor 10-02-03 Dim mask As String Selection.EndKey Unit:=wdStory Letters = Selection.Information(wdActiveEndSectionNumber) mask = "ddMMyy" Selection.HomeKey Unit:=wdStory Counter = 1 While Counter Letters DocName = "c:\DOCUMENTS\" & Format(Date, mask) & " " & LTrim$(Str$(Counter)) ActiveDocument.Sections.First.Range.Cut Documents.add With Selection .Paste .EndKey Unit:=wdStory .MoveLeft Unit:=wdCharacter, Count:=1 .Delete Unit:=wdCharacter, Count:=1 End With ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument ActiveWindow.Close Counter = Counter + 1 Wend End Sub |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge with attachments | Mailmerge | |||
Problem with automating mail merge | Mailmerge | |||
Mail Merge: How do you merge to file but create separate files | Mailmerge | |||
mail merge to individual files | Mailmerge | |||
How do I create separate files for each document a mail merge cre. | Mailmerge |