Automating mail merge

[Patrick]

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

 

[Joergen Bondesen]

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.


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

 

[Patrick]

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

 

[Doug Robbins - Word MVP]

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