Dynamic Query of a SQL table in Excel

[Mitchell5656]

So I've been stuck working on this problem for the last several days, and would really like a solution to it.
What I am doing is going into the data tab in excel and getting external data from my SQL server (I will provide a link to my database). And I select the Products table, and now have the table showing in my spreadsheet. Now what I am trying to do is open the connections tab, and click on the table that I am connected to, and go to the table properties and definition tab. Under Command Type I change that to SQL. In the Command Text I want to enter a dynamic query. What this dynamic query does is in Sheet2 cell a1 you are supposed enter a category and in cell a2 you enter some property for that category. So for example with the data I have you enter categoryid for the category and for the property you enter 1 and then sheet 1 is queried so it shows the productid, productname, supplierid, categoryid, unitprice, and discontinued for all products with categoryid 1.
I have written up something that I want to enter into the Command Text, but it is not working: <code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; background-color: rgb(238, 238, 238); font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">SELECT * FROM "TSQL2012"."Production"."Products" WHERE [sheet2$a1] = [sheet2$a2];</code>
database: File
Unzip it, and it is the TSQL2012 DB
If there is anything else that you need or have questions about, please post. This is the way I need to solve this problem so unfortunately other solutions will not be helpful.

 

[Mitchell5656]

Could you elaborate a little more on what you are saying? I'm a bit confused.

 

[Jerry Sullivan]

Here's an example.

1. Add Data Validation to Cell Z1 to allow the selection of some of all of your Field names from a drop down list.

2. Place an initial value in Cell Z2.

3. Modify the ParameterQueryExample sub to reference the Value in Z2 instead of Z1

4. Run ParameterQueryExample to create the new ListObject Table replacing any exising table in A1.

5. Paste this code into the Sheet Code Module (not a standard module) of the sheet that has the parameters in Z1:Z2.

Private Sub Worksheet_Change(ByVal Target As Range)
'--updates query to replace existing query's WHERE field _
'     with new field in Z1
'     assumes existing query includes a string expression in this format:
'           WHERE Products.fieldname =


    If Intersect(Target, Me.Range("Z1")) Is Nothing Or _
        Target.CountLarge > 1 Or _
        Target.Text = vbNullString Then Exit Sub

    
    Dim sOldSQL As String, sOldField As String, sNewField As String
    Dim lFind As Long, lFind2 As Long
    Const sTableName As String = "Products"

            
    On Error GoTo CleanUp
    Application.EnableEvents = False

    
    With Me.Range("A1").ListObject.QueryTable
        sOldSQL = .CommandText

        
        '--find old field then replace with new field in query
        lFind = InStr(1, sOldSQL, "WHERE " & sTableName, 1)
        lFind2 = InStr(lFind + Len("WHERE " & sTableName), sOldSQL, " ", 1)
        sOldField = Mid(sOldSQL, lFind, lFind2 - lFind)
        sNewField = "WHERE " & sTableName & "." & Me.Range("Z1")
        .CommandText = Replace(sOldSQL, sOldField, sNewField)
        .Refresh
    End With

    
CleanUp:
    Application.EnableEvents = True
End Sub

Once setup, whenever you change the field name parameter in Z1, the Worksheet_Change code will modify the query to use the new field name and refresh.

Note that this code is somewhat tailored to the query you are using and isn't written to allow variations like fieldnames in [brackets] or 'field names with spaces in the name', or queries that have multiple criteria in the WHERE clause.

 

[tusharm]

Why not just retrieve all the columns and then index into whichever column interests you? Easiest and quickest.

There is an alternative that does not require a dynamic query but why bother when the above is available.

Yes I did end up getting my VBA code to work. The only difference was that I have SQL Server Native Client 11.0 instead of 10.0. So for example in cell Z1 you had the code take a value from the product id column and query it based on that value. What I was curious was to see if say Z1 you entered in the column you wanted to query, so instead of productId you wanted to use categoryId you could type that instead, and in cell Z2 you would type in the value for categoryId. So this way you could query based off of different columns without having to modify the VBA code each time for a different result

 

[Jerry Sullivan]

Hi Tushar,

I haven't worked much with Dynamic Queries and even less with Parameter Queries, so I'm interested to learn more.

With a large database, wouldn't there be a benefit to having the SQL do the work of filtering the data, then only importing into the workbook the data which is currently needed?

 

[tusharm]

Your code looks good...nothing wrong with it.

But...if the user makes frequent changes to the column of interest and the database is remote, the overhead of repeated calls may be greater than the benefit of letting SQL do the work.

A couple of links related to dynamic SQL

Dynamic Query in SQL Server - Stack Overflow

and

The curse and blessings of dynamic SQL

A 'compromise' approach might be retrieve all the columns into a module level temporary table. Then, using event-driven code, simply select from this table the column of interest.

With my current client, I have admin access to the database I am working with. So, (almost) all of the database access is with SQL Server stored procedures (parametrized queries in Access are a poor alternative). Then when I, or someone else, has a need to do something with the db, we call the appropriate stored procedure with a set of parameters. This minimizes the extent to which we have to expose the database schema to the ASP developers and/or end-users.

For parametrized queries in Excel see

RDBMS in Excel

Hi Tushar,

I haven't worked much with Dynamic Queries and even less with Parameter Queries, so I'm interested to learn more.

With a large database, wouldn't there be a benefit to having the SQL do the work of filtering the data, then only importing into the workbook the data which is currently needed?

 

[Jerry Sullivan]

Tushar, That makes sense that it would depend on the frequency of the queries.

Thank you for the links- very informative.

 

[tusharm]

You are welcome.

Tushar, That makes sense that it would depend on the frequency of the queries.

Thank you for the links- very informative.

 

[WBenhart]

This is the script I use...

Sub Refresh_Button()

    Dim sDate     As String
    Dim eDate     As String
    Dim Comp      As String
    Dim Rpt_Sheet As String
    Dim Sql_Sheet As String
    Dim qt        As QueryTable
    
    'Default Work Sheet names:
    Rpt_Sheet = "Report Detail"
    Sql_Sheet = "SQL_Build"
    
    'User Input Parameters from Work Sheet
    Sheets(Rpt_Sheet).Select
    sDate = Range("B3").Value
    eDate = Format(CDate(Range("B4").Value) + 1, "mm/dd/yy")
    Comp = Range("B2").Value
    
    '****************************************************************************************************************
    '*** NOTE This VB Code looks for the RAW SQL Statement on a hidden page called from a Sheet called: SQL_Build ***
    '****************************************************************************************************************
    Application.ScreenUpdating = False
    Worksheets(Sql_Sheet).Visible = True
    Sheets(Sql_Sheet).Select
        Range("A1").Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        For Each rRow In Selection.Rows
            For Each rCol In rRow.Columns
                If IsError(Range(rCol.Address)) <> True And Left(Trim(rCol.Value), 2) <> "--" And Left(Trim(rCol.Value), 2) <> "#NAME?" Then
                    If Len(rCol.Value) > 0 Then
                        SQLStr = SQLStr & " " & Trim(rCol.Value) & " "
                        'dNum = MsgBox(rCol.Address & rCol.Value, vbOKCancel + vbQuestion, "Current Cell")
                        'If dNum = 2 Then Exit Sub
                    End If
                End If
            Next rCol  ' Move to next cell in selection from left to right
            'dNum = MsgBox(SQLStr, vbOKCancel + vbQuestion, "Line Info")
            'If dNum = 2 Then Exit Sub
        Next rRow  ' Moves to the next row in Selection
        
    'Add the Report Filters to The SQL Statement:
    SQLStr = SQLStr & " dbo.RCV_HDR.DOC_DATE >= '" & sDate & "' AND dbo.RCV_HDR.DOC_DATE < '" & eDate & "' "
    
    If UCase(Comp) <> "ALL" Then
        SQLStr = SQLStr & " AND dbo.RCV_HDR.COMPANYNO = " & Comp
    End If
    
    Worksheets(Sql_Sheet).Visible = False
    Application.ScreenUpdating = True
    
    Set qt = Sheets(Rpt_Sheet).ListObjects(1).QueryTable
    With qt
        .CommandText = SQLStr
        Sheets(Rpt_Sheet).Select
        Range("Table_Query_from_PAG[[#Headers],[" + Range("A6").Value + "]]").Select
        Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
        Range("B2").Select
    End With
    
    Set qt = Sheets(Rpt_Sheet).ListObjects(1).QueryTable
    With qt
        .CommandText = ""
        Sheets(Rpt_Sheet).Select
    End With
End Sub

 

[pencuyut]

WBenhart your code looks great, I am trying to do something like that. Do you mind sharing your excel file so I can modify it and make it work in my end?

Thanks!

 

[WBenhart]

Sure no problem, PM me your email address and I will send it to you.

My spreadsheet has additional functions in it that you may find useful like it automatically creates the ODBC connection for the user on opening the excel document.

If you have complex SQL statements that have problems passing to the SQL server, I have a different version that converts you SQL code to an execute statement.

Wayne