CODEWITHSUNDEEP
excelvbams-wordmailmerge
rakesh seebaruth
rakesh seebaruth

Reputation: 69

mail merge excel vba

I have my excel data sheet and my word document as per link below

https://www.dropbox.com/s/my62tw9jsf...32018.xls?dl=0

https://www.dropbox.com/s/h5wb91ymd3...flo1.docx?dl=0

My word document is already configured with the merge fields layout. I have tried mail merge to word but unfortunately the Word application becomes "not responding" each time I try mail merge.

Here it is the recorded macro:

Sub Macro1()
'
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\[userName]\Desktop\New folder (18)\fixedcharge16032018.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Je" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
End Sub

Upvotes: 1

Views: 1103

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

The connection string for the data source is longer than the macro recorder is able to handle. Notice how it cuts off very strangely:

"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;Je" _

That it ends with ;Je" is not normal. I see something similar when I try recording in Word 2016. (The difference is due to the length of the file path.)

Luckily you don't need all the information at the end of the standard connection string. It can be edited / pared down to a form that Word's mail merge can work with.

The following worked for me - cutting off the connection string after IMEX=1;". Note that for your security I replaced the part of the folder path that contains your name! You need to modify this back to your path.

Sub MergeMacro
' Macro1 Macro
'
'
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\[userName]\Desktop\New folder (18)\fixedcharge16032018.xls", _
        ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\[UserName]\Desktop\New folder (18)\fixedcharge16032018.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;", _
        SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
End Sub

Upvotes: 3

Related Questions