Excel parameterized SQL query with IN clause

[dotsent]

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:

select * from products where products.id IN ('A', 'C', 'F')

Excel parameterized query with IN clause would look like:

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:

"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!

 

[John_w]

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.

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]

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:

"Run-time error '5': Invalid procedure call or argument"

highlighting this in the 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]

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]

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:

=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!

 

[dotsent]

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]

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:

=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):

[INDENT]="'"&TEXTJOIN("', '",TRUE,M1:M4)&"'"
[/INDENT]

Or you could use this UDF (user-defined function) in a standard module:

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:

[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:

    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.

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:

                qt.Refresh BackgroundQuery:=True

 

[ssjody1960]

I'm using Excel 2019. This is almost exactly what I need except I can't figure out how to set the target range when I'm using a table. How would I alter this code to use the value in table4 of my spreadsheet which is on a separate worksheet.
I've tried the following but it doesn't work

If Not Intersect(Target, Range("Table4[OEM]")) Is Nothing Then

 

[John_w]

Welcome to MrExcel forums.

As your Table4 is on a separate worksheet you'll need to use that sheet's Worksheet_Change event handler, not the SQL query sheet's Worksheet_Change, so that any change you make to the values in the OEM column are detected and cause the SQL query to be updated and refreshed.

Put this code in the sheet module of the sheet where Table4 resides. The name of the SQL query sheet is "SQL query"; change this string in the code as required.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim INvalues As String
    Dim SQLin As String
    Dim i As Long, p1 As Long, p2 As Long
    Dim qt As QueryTable

    If Not Intersect(Target, Range("Table4[OEM]")) Is Nothing Then
    
        INvalues = JoinValues2(Range("Table4[OEM]"))
        
        With Worksheets("SQL query")
        
            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 (" & INvalues & ")"
                    qt.CommandText = Left(qt.CommandText, p1 - 1) & SQLin & Mid(qt.CommandText, p2)
                    MsgBox "Target cell changed: " & Target.Address & vbCrLf & vbCrLf & _
                        "Table column address: " & Range("ShelfTable[Shelf]").Address & vbCrLf & vbCrLf & _
                        "Query destination cell: " & qt.Destination.Address & vbCrLf & vbCrLf & _
                        "New SQL command text:" & vbCrLf & vbCrLf & qt.CommandText, Title:="Worksheet_Change event on " & Me.Name & " sheet"
                    qt.Refresh BackgroundQuery:=True
                End If
            
            Next
        
        End With
        
    End If
    
End Sub

And put this code in a standard module:

Public Function JoinValues2(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
    JoinValues2 = Left(result, Len(result) - 1)
    
End Function

 

[ssjody1960]

Thank you very much for the code. I did put the code within the 1st code block in the sheet where Table4 resides. I then put the code in the 2nd code block within module1. I also changed "SQL Query" to be "OEMnumToCpPartCode" which is the name of my SQL Query sheet. When I run the code I get an error on this line of code that says "Sub or Function not defined" on the line of code "INvalues = JoinValues2(Range("Table4[OEM]"))" Any help is much appreciated!