# Excel parameterized SQL query with IN clause



## dotsent (May 24, 2019)

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 (May 24, 2019)

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 (May 24, 2019)

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 (May 24, 2019)

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 (May 25, 2019)

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 (May 25, 2019)

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 (May 27, 2019)

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



dotsent said:


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



dotsent said:


> 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 (May 29, 2020)

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 (May 31, 2020)

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 (Jun 1, 2020)

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!


----------



## dotsent (May 24, 2019)

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 (Jun 2, 2020)

You would get that error if the JoinValues2 function is not in a standard module.  It should also be available as a UDF: type =JoinValues2 in a cell to confirm this.


----------



## ssjody1960 (Jun 2, 2020)

I fixed my previous problem. My fault. The code I had in module1 wasn't what John had posted. I don't get the error now but I still get no results when the sql query is ran.


----------



## ssjody1960 (Jun 2, 2020)

I did confirm that the UDF is working by typing in =JoinValues2. It worked


----------



## John_w (Jun 2, 2020)

ssjody1960 said:


> I don't get the error now but I still get no results when the sql query is ran.


If you have created the SQL query correctly then you should be able to refresh it manually and it will return results.

The Worksheet_Change code simply changes the query's command text (SQL SELECT statement), specifically the "IN (xxxx)" clause, as per the requirement in the original post.  You can see the current command text in the query's connection definition.


----------



## ssjody1960 (Jun 2, 2020)

Maybe this is where my failure is and maybe I'm missing a detail on how I should structure the command_text in my connection. In the command text of the connection I'm using for the query I have the following 
	
	
	
	
	
	



```
select * from "CPIC"."OEMNUMTOCPCODE" where OEMNUM IN ('')
```
. Maybe I should have mentioned to that the connection is to an ORACLE database table. Don't know if that has anything to do with the syntax of the command_text.


----------



## John_w (Jun 2, 2020)

I don't know about SQL Server or Oracle syntax, but try changing it to:

select * from "CPIC"."OEMNUMTOCPCODE" where CPIC.OEMNUM IN ('1234')

where 1234 is a valid string.   I would also try omitting the double quotes: CPIC.OEMNUMTOCPCODE

However the apostrophes in '1234' suggests a string, not a number, so maybe this IN clause wouldn't work anyway if OEMNUM is a numeric column.  Maybe IN (1,2,3,4) would work for you.


----------

