Parameter-Driven SQL Query from Excel

Private Sub CommandButton1_Click()

Dim TheQuery As QueryTable

Dim TheSheet As Worksheet

Dim FirstLetter As String

Dim RunDate As Date

Dim parm As Parameter

Set TheSheet = ActiveSheet

With TheSheet

FirstLetter = .Range("B1")

RunDate = .Range("B2")

Set TheQuery = .QueryTables(1)

End With

With TheQuery

.Connection = "ODBC;DRIVER=SQL Server;SERVER=(local);UID=(UserID);PWD=(password);DATABASE=(DBname)"

.Parameters.Delete

.CommandText = "EXEC dbo.prc_SelectTest @FirstLetter=?, @RunDate=?"

Set parm = .Parameters.Add("@FirstLetter")

parm.SetParam xlConstant, FirstLetter

Set parm = .Parameters.Add("@RunDate")

parm.SetParam xlConstant, RunDate

.Refresh

End With

End Sub