Excel Microsoft Query: External table is not in the expected format

[KrisW77]

A while ago I successfully created a macro (with the aid of various forums) to query a Named Table in a separate macro-enabled workbook

Option Private Module
Option Explicit

Const conn_name = "Query from Drill Sheet Database"
Const pvt_Name = "pvt_Linked"
Dim cur_File As String, cur_Path As String, new_File As String

Sub DB_Connect()
Dim file_Exists As Boolean, file_ExistChk As String, file_ExistMsg As VbMsgBoxResult
Dim conn_Exists As Boolean, conn_ExistChk As String
Dim conn_RefreshType As Boolean, conn_RefreshChk As Integer

'Set File Link Names
cur_Path = ThisWorkbook.Path 'Current Folder
cur_File = cur_Path & "\" & DATA.Range("db_FileName").Value & ".xlsm" 'Database File inc Folder
new_File = ThisWorkbook.Name 'This File Name

'Check if File Exists
'   Returns TRUE if the file exists
    file_ExistChk = Dir(cur_File)
    If file_ExistChk <> "" Then
        file_Exists = True
    Else
        'Try as XLSX Type
        cur_File = cur_Path & "\" & DATA.Range("db_FileName").Value & ".xlsx" 'Database File inc Folder
        file_ExistChk = Dir(cur_File)
        If file_ExistChk <> "" Then
            file_Exists = True
        Else
            file_Exists = False
        End If
    End If
    
'If File Doesnt Exist Then Message & Exit Sub
If file_Exists = False Then
    file_ExistMsg = MsgBox("File " & DATA.Range("db_FileName").Value & ".xlsm" & Chr(13) & _
                        "Does Not Exist in Folder" & Chr(13) & cur_Path, vbCritical + vbOKOnly, _
                        "FILENAME DOES NOT EXIST")
    Exit Sub
End If

'Check if Connection Exists
On Error Resume Next
conn_ExistChk = ThisWorkbook.Connections(conn_name).Name
If Err Then
    conn_Exists = False
Else
    conn_Exists = True
End If
On Error GoTo 0

'If Connection Exists then Refresh Connection String Else Create New Connection
If conn_Exists = True Then
    'Check if Connection Still Matches
    conn_RefreshChk = InStr(1, ThisWorkbook.Connections(conn_name).ODBCConnection.Connection, cur_File, vbTextCompare)
    
    If conn_RefreshChk = 0 Then
        'Update Connection String
        ThisWorkbook.Connections(conn_name).ODBCConnection.Connection = Array("ODBC;DBQ=", cur_File, ";" _
                                                                            , "DefaultDir=", cur_Path, ";" _
                                                                            , "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
                                                                            , "MaxBufferSize=2048;" _
                                                                            , "MaxScanRows=8;" _
                                                                            , "PageTimeout=50;" _
                                                                            , "ReadOnly=1;" _
                                                                            )
    End If
    
    'Refresh Connection & PivotTable
    ThisWorkbook.Connections("Query from Drill Sheet Database").Refresh
    
Else
    New_Connection
    New_PivotTable
End If

DATA.Shapes("btn_DB_Connect").TextFrame.Characters.Text = "Update Drill Sheet Connection"
End Sub

Sub New_Connection()

'Create Connection in New Workbook
Workbooks(new_File).Connections.Add conn_name, "" _
    , Array("ODBC;DBQ=", cur_File, ";" _
    , "DefaultDir=", cur_Path, ";" _
    , "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
    , "MaxBufferSize=2048;" _
    , "MaxScanRows=8;" _
    , "PageTimeout=50;" _
    , "ReadOnly=1;" _
    ), _
    Array("SELECT * " & Chr(13) & "" & Chr(10) & "FROM `Data$` `Data$`"), 2
    
End Sub

Sub New_PivotTable()
Dim pvt_Dest As String

Application.ScreenUpdating = False
'Create Pivot Table with Connection in DATA Sheet
pvt_Dest = DATA.Name & "!R10C1"

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal _
    , SourceData:=ActiveWorkbook.Connections(conn_name)) _
    .CreatePivotTable TableDestination:=pvt_Dest _
        , TableName:=pvt_Name
    
'Sets Refresh as PivotCache Create Removes Refresh Settings
With ActiveWorkbook.Connections(conn_name).ODBCConnection
    .BackgroundQuery = True
    .RefreshOnFileOpen = True
End With

With ActiveSheet.PivotTables(pvt_Name)
     'Formatting PivotTable
End With

Application.ScreenUpdating = True
End Sub

Since then I have modified this separate workbook and the Named Table is a slightly different size, but the macro to query the Named Table no longer works & errors with External Table is not in the expected format message

HOWEVER, the query macro does work if I copy the Named Table into a separate excel book - which is a far from an ideal workaround.

Can anyone point me in the right direction, as how to solve this issue?

Thanks very much.

Kris