Excel to Word Macro Merge File Select Help

[kdauria]

Hello all,

I am currently using this marco which allows me to merge to word directly. Thismethod seems to preserve my formatting the best, however, I do not know how to make it allow me to choose a folder with the files in it to be merged. The concept being that you can put all the files that you want to merge with that data into one folder, hit run the macro and be able to choose that folder and whatever is inside will merge into a new document that you can then save individually. Below is what I'm currently using, can someone help me modify? As you can see, right now it will only open the files that are entered into the macro itself. Please help.

Sub NewMerge()
'
' NewMerge Macro


Application.ScreenUpdating = True
Application.DisplayAlerts = False
Set WordApp = New Word.Application


WordApp.Visible = True
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFCORPOC1.dot"
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFCORPEPC1.dot"
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFCORPCG1.dot"
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFCORPCG2.dot"
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFINDV(I).dot"
WordApp.Documents.Open Filename:="C:\Users\kdauria\Desktop\TEST\AFFINDV(WE).dot"


With WordApp


.ActiveDocument.MailMerge.OpenDataSource Name:="C:\Users\kdauria\Desktop\TEST\TEST DATA FILE.xls" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=False, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", _
SQLStatement:="", SQLStatement1:=""


With .ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
End With
'
End Sub