×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Managing Queries in Excel via MS Query by SkipVought
Posted: 29 Mar 18

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:
  • Path
  • Database Name
  • Connection String
  • SQL String
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.
ODBC;DSN=Excel Files;
DBQ=C:\Users\Skip\Documents\2016PharmacyAnalysis.xlsx;
DefaultDir=C:\Users\Skip\Documents;
DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
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$'`
 
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=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    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=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    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 `" & sPath & "\" & sDB & "`.`'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.

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close