Excel parameterized SQL query with IN clause

dotsent

Board Regular
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:

John_w

MrExcel MVP
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.
 

dotsent

Board Regular
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?
 

John_w

MrExcel MVP
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.
 

dotsent

Board Regular
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:

dotsent

Board Regular
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?
 

John_w

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

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:
[INDENT]="'"&TEXTJOIN("', '",TRUE,M1:M4)&"'"
[/INDENT]
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:
[INDENT]="'"&JoinValues(M1:M4,"','")&"'"
[/INDENT]
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!

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

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top