Automating mail merge Patrick 6/6/2007 3:08:00 PM Hi! I apologize if this is answered in the forum, I have looked and can not find an appropriate answer. I have 218ish xls files that I need to merge with a word mail merge document and save the output .doc file with the same name. i.e. test1.xls test2.xls test3.xls output to: test1.doc test2.doc test3.doc Any help would be appreciated as I have been unable to do this with vb. Thank you! patrick

Re: Automating mail merge "Joergen Bondesen" <bondesen[ at ]cool.dk> 6/6/2007 4:31:40 PM Hi Patric. Your template must be a *.doc E.g. Automerge.doc Open the file and place below i a Module. Notice if you will open the Automerge.doc you must push/keep SHIFT down. Place the Automerge.doc in the folder with your *.xls files. Prehaps you should split the files to a less number. NB.: Sometime I do have probleme with the special signs we are using in my langauge, æ, ø, å. They are after mergin displayes as old "Japanese" letters and give no sense in the word. Enjoy. 8-) Option Explicit '// General Sub autoopen() '// Joergen Bondesen, 20050920 Application.ScreenUpdating = False Dim MyPath As String Dim MyFile As String Dim MyfileLen As Long Dim MyfileName As String MyPath = CurDir MyFile = Dir(MyPath & "\*.xls") Do While MyFile <> "" MyfileLen = Len(MyFile) MyfileName = Left(MyFile, MyfileLen - 4) '// Search With Application.FileSearch .FileName = MyfileName & ".doc" .LookIn = MyPath & "\" .Execute 'Exist goto line01 If .Execute() > 0 Then GoTo line01 End If End With '// Merge SendKeys "{enter}" ActiveDocument.MailMerge.OpenDataSource _ Name:=MyPath & "\" & MyFile With ActiveDocument.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord .LastRecord = wdDefaultLastRecord End With .Execute Pause:=True End With '// Save merged doc ChangeFileOpenDirectory MyPath & "\" ActiveDocument.SaveAs FileName:=MyfileName _ & ".doc", FileFormat:= _ wdFormatDocument, AddToRecentFiles:=True '// close merged file ActiveDocument.Close line01: '// Next MyFile = Dir Loop '// Close merge doc without saving ''SendKeys "{Tab}", True SendKeys "%{F4}" SendKeys "%N" Application.ScreenUpdating = True End Sub -- Best Regards Joergen Bondesen "Patrick" <Patrick[ at ]discussions.microsoft.com> wrote in message news:2875D664-8362-408F-85ED-9C1F2966C28B[ at ]microsoft.com... [Quoted Text] > Hi! > > I apologize if this is answered in the forum, I have looked and can not > find > an appropriate answer. I have 218ish xls files that I need to merge with > a > word mail merge document and save the output .doc file with the same name. > i.e. > > test1.xls > test2.xls > test3.xls > > output to: > > test1.doc > test2.doc > test3.doc > > > Any help would be appreciated as I have been unable to do this with vb. > > Thank you! > > patrick

Re: Automating mail merge Patrick 6/7/2007 9:28:01 PM Joergen, Thank you! I love it and it works! I have one minor question/glitch. The final documents that are created by the merge also have the autoopen() code in them - consequently I need to hold the shift key down while I open them or they run the code. What I did is move the code from a module to a macro and assign it a key code - then just kick it off from there. THANK YOU! Patrick

Re: Automating mail merge "Doug Robbins - Word MVP" <dkr[ at ]REMOVECAPSmvps.org> 6/8/2007 6:02:29 AM Store the following macro in the normal.dot template and run it when the mail merge main document is the active document. Dim MyPath As String Dim MyName As String Dim MyMergeDoc As Document Dim MyNewFile As String 'let user select a path MsgBox "In the following dialog box, select the folder containing the data sources." With Dialogs(wdDialogCopyFile) If .Display() <> -1 Then Exit Sub MyPath = .Directory End With 'strip quotation marks from path If Len(MyPath) = 0 Then Exit Sub If Asc(MyPath) = 34 Then MyPath = Mid$(MyPath, 2, Len(MyPath) - 2) End If 'Set a reference to the mailmerge main document Set MyMergeDoc = ActiveDocument 'Merge the main document with each of the files in the data source folder. MyName = Dir$(MyPath & "*.*") Do While MyName <> "" MyNewFile = Left(MyName, InStr(MyName, ".") - 1) With MyMergeDoc.MailMerge .OpenDataSource (MyPath & MyName) .Destination = wdSendToNewDocument .Execute Pause:=False End With With ActiveDocument .SaveAs MyNewFile .Close End With MyName = Dir Loop -- 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 "Patrick" <Patrick[ at ]discussions.microsoft.com> wrote in message news:C101F484-5DAD-4CF7-947F-4CBC7A380AD7[ at ]microsoft.com... [Quoted Text] > Joergen, > > Thank you! I love it and it works! I have one minor question/glitch. > The > final documents that are created by the merge also have the autoopen() > code > in them - consequently I need to hold the shift key down while I open them > or > they run the code. > > What I did is move the code from a module to a macro and assign it a key > code - then just kick it off from there. > > THANK YOU! > > Patrick