Table Select Error with Mail Merge from Excel to word

Question

First off here is my code.

Sub RunMerge()
' Word constants

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(ThisWorkbook.Path & "\AAFCAAC-#4077508-v1-AAFC-FFPB-COPE-SATD-_AgriInnovation_Draft_Survey_Instructions_189318.doc")

strWorkbookName = ThisWorkbook.Path & "\" & "MD.xlsm"

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource _
        Name:=strWorkbookName, _
        AddToRecentFiles:=False, _
        Revert:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Data Source=" & strWorkbookName, _
        SQLStatement:="SELECT * FROM 'Mail Merge Data$'"

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

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

Set wdocSource = Nothing
Set wd = Nothing

End Sub

I'm trying to get a mail merge going from excel to word. My problem (I believe) is with SQLStatement:="SELECT * FROM 'Mail Merge Data$'" for when I run my code excel goes into limbo waiting for word. When I go into my task manager I see word is stuck on selecting a table. There are no options in the table select menu and the designated workbook is only the path of my workbook with .xls appended to the end. If I remove the SQLStatement line the same thing happens but the select table is populated with the sheets and name ranges in my workbook. I'm hoping that this is a minor error or a typo on my part.

Answer 1

I think the problem is that you removed additional quotation marks which are required in your situation. Your SQL statement should rather looks like:

SQLStatement:="SELECT * FROM `'Mail Merge Data$'`"

You could also try with this code:

SQLStatement:="SELECT * FROM [Mail Merge Data$]"