ADDing QueryTables
In an Excel sheet you can Add a QueryTable object using the
Data > Get External Data feature.
I do not recommend ADDING QueryTables via code. In my 20+ years of using QueryTables in Excel, I have not encountered a need to add QTs on the fly, although it can be done with care.
You definitely do not want to have a bunch of unused QT objects cluttering up your workbook!
Once a QT is Added to a sheet, it is there until you Delete it. Until then, the Connect string and/or the SQL string can be modified as needed.
Of course, when you connect to a database there are a number of different kinds of database: Oracle, DB2, MS Access, SQL Server, Excel, Text File, Web. There may be more. These are the ones that I have tapped. With each you would have a unique Connection String.
CHANGing QueryTables
The significant properties to manage in a QT are:
[ul]
[li]Path[/li]
[li]Database Name[/li]
[li]Connection String[/li]
[li]SQL String[/li]
[/ul]
You have already set up your QT and it is returning data to your sheet from the database.
How can you determine the content of Connection String and/or SQL String?
Code:
'
With ActiveSheet.ListObjects(1).QueryTable
Debug.Print .Connection
Debug.Print .Sql
End With
When you look at the Immediate Window (ctrl+G), you will see two strings.
It happens that my QT is connected to another sheet/table in ThisWorkbook.
[pre]
[highlight #E9B96E]ODBC;DSN=Excel Files;
DBQ=C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx;
DefaultDir=C:\Users\Skip\Documents;
DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
[/highlight][highlight #8AE234]SELECT `'2017$'`.Month, `'2017$'`.`2017`, `'2017$'`.`2016`, `'2017$'`.`2017 Cum`, `'2017$'`.`2016 Cum`, `'2017$'`.F6, `'2017$'`.F7, `'2017$'`.F8, `'2017$'`.F9, `'2017$'`.F10, `'2017$'`.F11, `'2017$'`.F12, `'2017$'`.F13
FROM `C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx`.`'2017$'` `'2017$'`[/highlight]
[/pre]
I made Line feeds to isolate the
DBQ and the
DefaultDir because I want to manage the
Path and
DB name like this...
Code:
Sub ChangeQT_ThisWorkbookPath()
Dim sPath As String, sDB As String
Dim sConn As String, sSQL As String
sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name
'ODBC;DSN=Excel Files;
'DBQ=C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx;
'DefaultDir=C:\Users\Skip\Documents;
'DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
sConn = sConn & "ODBC;DSN=Excel Files;"
sConn = sConn & "DBQ=" & [color #204A87]sPath[/color] & "\" & [color #204A87]sDB[/color] & ";"
sConn = sConn & "DefaultDir=" & [color #204A87]sPath[/color] & ";"
sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
With ActiveSheet.ListObjects(1).QueryTable
.Connection = sConn
End With
End Sub
But this is how I run my refreshes
Code:
Sub ChangeQT_ThisWorkbookPath()
Dim sPath As String, sDB As String
Dim sConn As String, sSQL As String
sPath = ThisWorkbook.Path
sDB = ThisWorkbook.Name
'ODBC;DSN=Excel Files;
'DBQ=C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx;
'DefaultDir=C:\Users\Skip\Documents;
'DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
sConn = sConn & "ODBC;DSN=Excel Files;"
sConn = sConn & "DBQ=" & [color #204A87]sPath[/color] & "\" & [color #204A87]sDB[/color] & ";"
sConn = sConn & "DefaultDir=" & [color #204A87]sPath[/color] & ";"
sConn = sConn & "DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"
'''SELECT
''' `'2017$'`.Month
''', `'2017$'`.`2017`
''', `'2017$'`.`2016`
''', `'2017$'`.`2017 Cum`
''', `'2017$'`.`2016 Cum`
'''
'''FROM `C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx`.`'2017$'` `'2017$'`
sSQL = sSQL & "SELECT"
sSQL = sSQL & " `'2017$'`.Month"
sSQL = sSQL & ", `'2017$'`.`2017`"
sSQL = sSQL & ", `'2017$'`.`2016`"
sSQL = sSQL & ", `'2017$'`.`2017 Cum`"
sSQL = sSQL & ", `'2017$'`.`2016 Cum`"
sSQL = sSQL & vbLf
sSQL = sSQL & "FROM `" & [color #204A87]sPath[/color] & "\" & [color #204A87]sDB[/color] & "`.`'2017$'` `'2017$'`"
With ActiveSheet.ListObjects(1).QueryTable
.Connection = sConn
.Sql = sSQL
.Refresh False
End With
End Sub
At any time I can change the Connect String or the SQL String as needed. In this particular case, if my workbook name changes or the path, not to worry, the Path and DB is updated each time it runs.
