How do I use a parameter in an Excel external data request?

Question

I can already use Excel (2007) to import data from SQL Server 2005. I add a data connection and I enter a custom SQL query that retrieves my desired data. Cool.

But what I'd like to add is the ability to parameterize that query based on a value found in a known cell on the spreadsheet.

My query would go from

SELECT * FROM dbo.MyDataTable WHERE Col1 = 'apples'

to something like

SELECT * FROM dbo.MyDataTable WHERE Col1 = 'Cell("B2")'

Is this possible? If so, how?

Answer 1

If you're using MS Query to get the data into Excel, this page shows how to use a value from a cell on the worksheet as a parameter in your query.

Answer 2

Try your structuring your code as follows:

Dim strSQL as String
Dim strFruit As String

strFruit = CStr(ThisWorkbook.Sheets("Sheet1").Range("A1").FormulaR1C1)

strSQL = "SELECT * FROM dbo.MyDataTable WHERE Col1 = '" & strFruit & "'"

I have found it useful to load the parameter into a variable first, before merging it into the SQL query.

It makes your SQL string more readable and allows you to check/clean the parameter before using it.