Set Excel ODBC paramater based on user input

Question

I have an Excel 2007 ODBC connection defined and I need to allow users to specify two of the paramaters each time the query is run.

Something along the lines of SELECT * FROM table WHERE tdate between ? AND ?

With the user specifying the date range each time the query is run. Any ideas?

Thanks,

David

Answer 1

This code snippet is taken from Excel 2003 help:

Set qt = Sheets("sheet1").QueryTables(1)
qt.Sql = "SELECT * FROM authors  WHERE (city=?)"
Set param1 = qt.Parameters.Add("City Parameter", _
    xlParamTypeVarChar)
param1.SetParam xlConstant, "Oakland"
qt.Refresh

It shows how to properly use a parameter in an SQL query string in Excel. A quick search for 'parameter query' in Excel 2007 help should confirm that this is still valid.