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
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
Code:
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