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

Dim SQLString as String

SQLString = "SELECT * FROM dbo.MyDataTable WHERE Col1 = '" & Cells("B2") & "'"

It would be worth looking at SQL Injection attacks to see the downsides of this approach, but it may not necessarily be a consideration for what you need to do

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.

Answer 3

I have not been using Excel 2007, but in previous versions of Excel I really hate the SQL Query integration. In particular I found it hard to do exactly what you ask about without resorting to hacking together a bunch of VBA code. I did some research and finally decided to use Rob Van Gelder's Excel query add in:

http://vangelder.orconhosting.net.nz/excel/queryeditor.html

The 'parameters' section in his GUI allows you to set up a parameter and have the utility either prompt the user for the value or pull the value from a cell reference. I believe the latter is exactly what you want to do. It also has the ability to auto-refresh when a cell value changes. I use the auto refresh function a lot when validating data in my DB.

I have had a couple of situations where my queries became corrupt and I could no longer open them or see the text. As a result, I recommend keeping a tab in the excel spreadsheet for your queries and copying the text of the queries into cells on that special page. That way if the query tool eats your queries you won't have to rebuild them from scratch.

A third party tool, albeit free, might not be what you are looking for, but it works well for me. Good luck!

Answer 4

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.