Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating mail merge
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating mail merge
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" wrote in message ... 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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating mail merge
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Automating mail merge
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" wrote in message ... 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automating mail merge | Mailmerge | |||
Mail-Merge: Can we have master detail relationship or multiple entities in word Mail-Merge? | Microsoft Word Help | |||
Automating Mail merge between Word 2003 and Access 2003 using VB.NET | Mailmerge | |||
Problem with automating mail merge | Mailmerge | |||
Loosing data connection when automating merge | Mailmerge |