Running SQL query from excel and refer to specific cell

Question

I'm using excel to run sql query and i want to refer the WHERE command to a specific cell in excel. For example, my code is:

SELECT [Value]
FROM [Sheet1$]
WHERE [ClientID]=2> 

Instead of 2, i went to refer to specific cell in excel, suppose T1
how can I write that query? Thanks, Sharon

I try to write the cell instead of 2 (T1), but it doesn't work

Answer 1

When you use SQL queries in Excel you can't directly reference a cell. However, there are some ways to solve your issue, each depends on the desired automatization level.

1. Almost manually - using Microsoft Query Write query like this and run, then Excel will prompt you to enter a value

   SELECT [Value]
   FROM [Sheet1$]
   WHERE [ClientID] = ?
   

2. Half manually - using Power Query (Get and Transform): Load your table into Power Query; create a named range for T1 (Formulas -> Define Name); load this named range into Power Query; modify the SQL Query to filter based on this value:

• use Power Query to join tables
• filter them based on the value from named range

3. Automatically - using dynamic query with VBA (which will automatically pull the value from T1 into your SQL query)

   Sub RunQuery()
   Dim conn As Object
   Dim rs As Object
   Dim sql As String
   Dim clientID As String
   
   ' Get value from cell T1
   clientID = Sheets("Sheet1").Range("T1").Value
   
   ' Construct SQL query dynamically
   sql = "SELECT [Value] FROM [Sheet1$] WHERE [ClientID] = " & clientID
   
   ' Create connection
   Set conn = CreateObject("ADODB.Connection")
   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"
   
   ' Run query
   Set rs = conn.Execute(sql)
   
   ' Output results (example: paste in column A)
   Sheets("Sheet1").Range("A2").CopyFromRecordset rs
   
   ' Cleanup
   rs.Close
   conn.Close
   Set rs = Nothing
   Set conn = Nothing
   End Sub
   

Answer 2

Simply useing direct cell reference:

SELECT [Value]
FROM [Sheet1$]
WHERE [ClientID] = T1

or

SELECT [Value]
FROM [Sheet1$]
WHERE [ClientID] = [T1]