Troubleshooting OpenDataSource in VBA script

[GeorgeFDAMH]

I am trying to run Word mail merge from an Excel button. I had a 4198 run time error at OpenDataSource so I did a Word macro recording and inserted the relevant code but to no avail. How to debug this? Andrew Poulsom asked "Snecz" to compare with a Word macro mail recording but that dialogue was inconclusive.Cindy Meister has suggested asynchronicity
as a cause but I have had no success with that approach. Any help would be most appreciated!

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub RunMerge()
Dim wd As Object
Dim wdocSource As Object

Dim strWorkbookName As String

On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdocSource = wd.Documents.Open("C:\Users\george\Desktop\VBA\Mergeletter.docm")

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource Name:= _
"C:\Users\george\Desktop\VBA\Datafile.xlsm", 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\george\Desktop\VBA\Datafile.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locki" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.Last Record = wdDefaultlastRecord
End With
.Execute Pause:=False
End With

wd.Visible = True
wdocSource.Close SaveChanges:=False

Set wdocSource = Nothing
Set wd = Nothing

End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

As you are late binding you need to use the value of constants like wdOpenFormatAuto instead of the constant itself. You can find out the values in the VBE Object Browser. wdOpenFormatAuto is zero.

 

[GeorgeFDAMH]

I put wdOpenFormatAuto:=0 and
wdMergeSubTypeAccess:=1
now I get run time error 438 Object doesn't support property or method.

 

[GeorgeFDAMH]

Found extra space in .LastRecord.
Everything now works beautifully.
Many thanks, Mr Poulsom!!!