Mail Merge Runtime Error

[VBA-Idiot]

Hi,

I've got a macro which formats a worksheet, opens a pre-formatted word document then runs a mail merge on the worksheet data. Getting a runtime error 4198 and when I run it in the editor it highlights the SQL Statement paragraph and states - Identifier under cursor not recognised.

This is the code which seems to be causing the problem - All i have changed is removing a filepath.

It opens the word document but fails to run the merge.

Appreciate any suggestions!

'run mail merge
    Dim wd As Object
    Dim wdocSource As Object
    Dim strWorkbookName As String
    Dim NewFileName As String
    Dim wdOutputName, wdInputName As String
    wdOutputName = ThisWorkbook.Path & "\Merged information " & Format(Date, "dd mmm yyyy")
    wdInputName = ThisWorkbook.Path & "\Form.docm"
    
    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("[I]filepath[/I].docm")
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    wdocSource.MailMerge.MainDocumentType = wdformletters
    wdocSource.MailMerge.OpenDataSource _
            Name:=strWorkbookName, _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=wdopenformatAuto, _
            Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
            SQLStatement:="SELECT * FROM `2$`"
    With wdocSource.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    wd.Visible = True
    wd.Application.ActiveDocument.SaveAs wdOutputName
   
    wdocSource.Close SaveChanges:=False
    
    Set wdocSource = Nothing
    Set wd = Nothing
Application.DisplayAlerts = False
On Error Resume Next
Sheets("2").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit

Thanks

C

 

[ranman256]

did you step thru it (f8) to see where it fails?
does it throw an error?
if so , where?

you need to remove
On Error Resume Next
so we can find the error

 

[VBA-Idiot]

I stepped through it it fails at the line

SQLStatement:="SELECT * FROM `2$`"

The source workbook has a sheet called "2" it should pull the data but that's where it stops.