Results 1 to 7 of 7

Thread: Excel parameterized SQL query with IN clause

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel parameterized SQL query with IN clause

    Hi!

    I'm running Office365 package Excel 2016 with MS Query to DB2 SQL database with the query being parameterized towards a specific Excel cell value (although I need to populate this cell from a larger range first). While SQL clauses work fine when cell has a single value, I need to run SQL IN clause for multitude of values (can be 50-100).

    The regular query outside of Excel would look something like this and works fine:
    Code:
    select * from products where products.id IN ('A', 'C', 'F')
    Excel parameterized query with IN clause would look like:
    Code:
    select * from products where products.id IN ?
    with the parameter linked to cell A1 holding value A - this IS working. It won't work anymore if cell A1 has value A, C providing error message:
    Code:
    "Bad parameter type. Microsoft Excel is expecting a different kind of value than what was provided"
    I have already understood that running a parameterized query with multitude of values can be tricky, however there must be a workaround, right? Maybe my approach has to be somehow different here? All the values are also not originally in cell A1, but distributed over a range and I'm merging/concatting them together.

    Using a parameter for each potential cell with individual value looks to work with small sample size, however requires insane amount of parameters to really set up (and test).

    Please help with any ideas!
    Last edited by dotsent; May 24th, 2019 at 06:21 AM.

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,689
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    Here is something you can try. It is a Worksheet_Change event handler which changes the IN clause part of the query's command text (the SQL SELECT statement) to use the values in a specific cell (e.g. B1). This cell can contain either a text value with the values separated by commas, e.g. A or A,C, or a formula whose result is a string with the values separated by commas. In the latter case, the Worksheet_Change event is not fired when the result of a formula changes (only when the user types in a cell) and therefore the code below handles this by looking at both the B1 cell and the cells which the formula references (e.g. M1:M4).

    Put this code in the sheet module of the sheet containing the query and change the mentioned cell addresses to suit your setup.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim INvaluesCell As Range
        Dim SQLin As String, parts As Variant
        Dim i As Long, p1 As Long, p2 As Long
        Dim qt As QueryTable
    
        Set INvaluesCell = Range("B1")
        
        If Not Intersect(Target, Range(INvaluesCell, "M1:M4")) Is Nothing Then
            
            SQLin = ""
            parts = Split(INvaluesCell.Value, ",")
            For i = 0 To UBound(parts)
                SQLin = SQLin & "'" & parts(i) & "',"
            Next
            SQLin = " IN (" & Left(SQLin, Len(SQLin) - 1) & ")"
            
            Set qt = Me.ListObjects(1).QueryTable
            
            p1 = InStr(1, qt.CommandText, " IN (", vbTextCompare)
            If p1 > 0 Then
                p2 = InStr(p1, qt.CommandText, ")") + 1
                qt.CommandText = Left(qt.CommandText, p1 - 1) & SQLin & Mid(qt.CommandText, p2)
            End If
            
        End If
        
    End Sub
    Just make sure your initial SELECT statement contains an " IN (xxxx)" clause, so that the code can find the " IN (" part.

    The code also preserves any SQL clause after the " IN (xxxx)" clause, e.g. an ORDER BY clause.

  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    First of all - thanks for taking the time!
    But phew, that's some next level stuff. Unfortunately when adding this VBA to Worksheet level and changing data in M1:M4 range, I get an error message:
    Code:
    "Run-time error '5': Invalid procedure call or argument"
    highlighting this in the code:
    Code:
    SQLin = " IN (" & Left(SQLin, Len(SQLin) - 1) & ")"
    By the way, I still have IN-clause added in my query text " select * from products where products.id IN ? " does the SQLin variable also have to include "IN"? MS Query doesn't look to be running with parameterized clause anyway. Though the VBA error is something else, maybe a typo?

    Perhaps the VBA error message avoids me seeing the full functionality of this, but is it meant to add values automatically to B1 cell once I change something in M1:M4 range?

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,689
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    What is the value of cell B1? If it's empty that error will happen.

    To clarify, the code looks for changes in B1 or M1:M4.

    B1 should contain either

    a) A text value: A or A,B or X,Y,Z

    or

    b) A formula whose result is a text value: =M1 or =CONCATENATE(M1,",",M2), etc.

    Your IN clause must include the brackets:

    select * from products where products.id IN (?)


    The code does not add values to cell B1. It only reads the value of cell B1.

  5. #5
    New Member
    Join Date
    Feb 2016
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    Hello again John! This is starting to look fantastic! I'm not the fastest thinker you'll ever meet, however I now see this Worksheet-level VBA adjusts the SQL string itself from ? to whatever the range filling is and yes brackets were missing! Never seen that before, but just what the doctor ordered!

    I could use this formula in B1:
    Code:
    =TEXTJOIN(", ",TRUE,M1:M4)
    Apparently TEXTJOIN is only available from Excel 2016 onwards and works similar to CONCAT, but also allows to add separators for IN-clause. Please see the screenshot for results.

    Could we therefore simplify the VBA so that:

    1) take off the comma-adding procedure which I think happens in VBA at the moment?
    2) just check B1 range and forget all about M1:M4 (given that TEXTJOIN is looking there itself) - string in B1 should be already ready-made for the query?
    3) I'm a beginner at best in VBA and it's a bit difficult for me to read this code, but is it also adding ' to the beginning and end of each value? I am manipulating my M1:M4 range with Excel IF clause anyway (in live environment), I could add " ' " to the start and end of each value too! As on the other screenshot.

    Any chance you or someone else could help me with adjusting the code? Although I'm already tremendeously thankful for the help!






    Last edited by dotsent; May 25th, 2019 at 05:42 PM.

  6. #6
    New Member
    Join Date
    Feb 2016
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    In addition to any possible simplifying of the VBA code, it looks like I have to use it for more than 1 query. They are all on the same worksheet though. Could this be possible too? I'm assuming this defines which query the code addresses: Set qt = Me.ListObjects(1).QueryTable

    However should I duplicate this code on the Worksheet level and modify just the ListObjects(x) in each or is there a simple way to instead of 1, address ALL ListObjects on the sheet with single Sub?

  7. #7
    Board Regular
    Join Date
    Oct 2007
    Posts
    5,689
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Excel parameterized SQL query with IN clause

    This is quite hard to explain so I will take it step by step.

    Quote Originally Posted by dotsent View Post
    Hello again John! This is starting to look fantastic! I'm not the fastest thinker you'll ever meet, however I now see this Worksheet-level VBA adjusts the SQL string itself from ? to whatever the range filling is and yes brackets were missing! Never seen that before, but just what the doctor ordered!
    Yes, the code changes the SQL command text in the query definition from " IN (?)" (or whatever it is currently) to use the values in cell B1, putting apostrophes around each letter. The resultant SQLin string is, for example, " IN ('A','B','C')".

    I could use this formula in B1:
    Code:
    =TEXTJOIN(", ",TRUE,M1:M4)
    Apparently TEXTJOIN is only available from Excel 2016 onwards and works similar to CONCAT, but also allows to add separators for IN-clause. Please see the screenshot for results.
    TEXTJOIN is available in Excel 2016+, but only in the 365 subscription version. Yes, you could use that formula, although this one is better because you don't have to put apostrophes around each letter in M1:M4 (Excel uses the apostrophe at the start of cell value for a special purpose):
    Code:
    ="'"&TEXTJOIN("', '",TRUE,M1:M4)&"'"
    Or you could use this UDF (user-defined function) in a standard module:

    Code:
    Public Function JoinValues(CellsToJoin As Range, Optional Separator As String = ",") As String
    
        Dim cell As Range
        Dim result As String
        
        result = ""
        For Each cell In CellsToJoin
            If Not IsEmpty(cell.Value) Then result = result & cell.Value & Separator
        Next
        JoinValues = Left(result, Len(result) - Len(Separator))
        
    End Function
    and this formula:
    Code:
    ="'"&JoinValues(M1:M4,"','")&"'"
    Could we therefore simplify the VBA so that:

    1) take off the comma-adding procedure which I think happens in VBA at the moment?
    OK - the new code below just reads the B1 cell value exactly as it is.

    2) just check B1 range and forget all about M1:M4 (given that TEXTJOIN is looking there itself) - string in B1 should be already ready-made for the query?
    The previous code looked at (in the Intersect function) cell B1 and M1:M4 because it gave you the choice of putting a formula in B1 or a string value. The new code below, which expects a formula in B1, must also look at (in the Intersect function) the cells which are referenced in the B1 cell formula, ie. M1:M4. This is because, as previously stated, when the result of a formula is changed the Worksheet_Change event is not triggered. So if the query parameter (cell B1) is set to 'Refresh automatically when cell value changes', although the B1 result changes as a result of you changing any cell M1:M4, and the query refreshes automatically, you will find that the rows returned are not correct for the values in B1. And if you look at the SQL command text of the query definition you will see that the SQL SELECT statement has not changed - the IN clause is still using the previous cell B1 values. You can test this issue by changing the Intersect line in the new code to:

    Code:
        If Not Intersect(Target, INvaluesCell) Is Nothing Then
    and you will find that the code inside the If .... End If block is never executed, even though the formula result of B1 changes.

    3) I'm a beginner at best in VBA and it's a bit difficult for me to read this code, but is it also adding ' to the beginning and end of each value? I am manipulating my M1:M4 range with Excel IF clause anyway (in live environment), I could add " ' " to the start and end of each value too! As on the other screenshot.
    Yes, it is surrounding each value with a pair of apostrophes. Yes, you could add ' to the start and end of each value, but note the thing I mentioned above with apostrophes being a special character in Excel.

    Any chance you or someone else could help me with adjusting the code? Although I'm already tremendeously thankful for the help!
    See updated code below!

    Quote Originally Posted by dotsent View Post
    In addition to any possible simplifying of the VBA code, it looks like I have to use it for more than 1 query. They are all on the same worksheet though. Could this be possible too? I'm assuming this defines which query the code addresses: Set qt = Me.ListObjects(1).QueryTable

    However should I duplicate this code on the Worksheet level and modify just the ListObjects(x) in each or is there a simple way to instead of 1, address ALL ListObjects on the sheet with single Sub?
    That Set qt line references the first query on the sheet. The new code below loops through all the queries on the sheet.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        
        Dim INvaluesCell As Range
        Dim SQLin As String
        Dim i As Long, p1 As Long, p2 As Long
        Dim qt As QueryTable
        
        Set INvaluesCell = Range("B1")
        
        If Not Intersect(Target, Range("M1:M4")) Is Nothing Then
            
            For i = 1 To ListObjects.Count
            
                Set qt = ListObjects(i).QueryTable
                
                p1 = InStr(1, qt.CommandText, " IN (", vbTextCompare)
                If p1 > 0 Then
                    p2 = InStr(p1, qt.CommandText, ")") + 1
                    SQLin = " IN (" & INvaluesCell.Value & ")"
                    qt.CommandText = Left(qt.CommandText, p1 - 1) & SQLin & Mid(qt.CommandText, p2)
                    MsgBox "Target cell changed: " & Target.Address & vbCrLf & vbCrLf & _
                        "Query destination cell: " & qt.Destination.Address & vbCrLf & vbCrLf & _
                        "New SQL command text:" & vbCrLf & vbCrLf & qt.CommandText, Title:="Worksheet_Change event"
                End If
            
            Next
            
        End If
        
    End Sub
    The code includes a MsgBox showing details about the query. If the B1 query parameter is not set to 'Refresh automatically when cell value changes' then add the following line after the qt.CommandText line:
    Code:
                    qt.Refresh BackgroundQuery:=True

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •