Data Connections > SQL Query > Use Excel Range to filter

So - you want to dynamically add a WHERE clause to your SQL statement based on the sheet? Is that correct?


Edit:

NOTE: This will only work if you edit my code to fit your project This is the general technique I've used in the past for this same situation. The key is, you can access the Command Text of your query table programmatically.

It looks to me that you are looking to add a clause like: WHERE Name IN ('Tom','Harry','Jane'). To your SQL statement.


The way I do this, is to use VBA to re-author the query as needed.

Your query sits in a table object in the workbook, and it has a specific name -- assume that name is T_People. You need a macro to alter the command text as needed.

Something like:

Sub alterCMDText()
Dim S As String
Dim namesRange As Range, c As Range


Set namesRange = Sheet1.Cells("A1:A10")

S = "Select * from row WHERE YEAR([Date]) = 2015 OR (YEAR([Date]) = 2014 AND MONTH([Date]) = 12 OR MONTH([Date]) = 11)"
S = S & " AND name IN ('"

'Add Names'
For Each c In nameRange
 S = S & c.Value & "','"
Next c

'End the IN list properly'
S = Left(S, Len(S) - 2) & ") "

'Add rest of SQL'
S = S & " order by ID asc"

Sheet1.ListObjects("T_People").QueryTable.CommandText = S
Sheet1.ListObjects("T_People").QueryTable.Refresh (False)

End Sub

Then you just need to decide what launches this (a button? a worksheet event? a workbook event?)...