How to use a parameter in an external Excel data query?

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 data. Cool.

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

My request will come from

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

to something like

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

Is it possible? If so, how?

Answer 1

MS Query, Excel, , .

Answer 2

:

Dim strSQL as String
Dim strFruit As String

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

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

, SQL-.

SQL / .

Answer 3

Excel 2007, Excel SQL Query. , , , VBA. , , Rob Van Gelder Excel :

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

"" . , , . . .

, , . Excel . , , .

A third-party tool, albeit a free one, may not be what you are looking for, but it works well for me. Good luck

Answer 4

Dim SQLString as String

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

It would be interesting to look at SQL Injection attacks to see the flaws of this approach, but it may not be necessary to understand what you need to do.