Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Managing Queries in Excel via MS Query

Excel How To

Managing Queries in Excel via MS Query

by  SkipVought  Posted    (Edited  )
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.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top