ExcelBanter - pass paramter to SQL server stored procedure

Hello Souris,

Here is some VBA code that shows one way to run a parameterized SQL stored
procedure and return and display the results on an Excel spreadsheet.

Sub OpencnnSQL()
Dim cnnSQL As ADODB.Connection
Dim cmdSQL As ADODB.Command
Dim rstSQL As ADODB.Recordset
Dim prm(2) As ADODB.Parameter
Dim strCnn As String, strSQL As String
Dim iCol As Integer, fldCount As Integer

strCnn = Empty
strCnn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;" & _
"Initial Catalog=Northwind;Data Source=localhost"
strSQL = Empty
' This stored procedure is in the SQL Server 2000 Northwind dB.
' It takes 2 parameters (@Beginning_Date and @Ending_Date) and returns
6 fields.
strSQL = strSQL & "dbo.[Employee Sales by Country]"

Set cnnSQL = New ADODB.Connection
Set cmdSQL = New ADODB.Command
Application.StatusBar = "Connecting ..."
With cnnSQL
.CursorLocation = adUseClient
.Open strCnn
End With
With cmdSQL
.ActiveConnection = cnnSQL
.CommandText = strSQL
.CommandType = adCmdStoredProc
Set prm(1) = .CreateParameter("Beginning_Date", adDate, adParamInput)
' Parameter values could also be passed to this subroutine or
retrieved from user at run-time.
prm(1).Value = "June 1, 1997"
Set prm(2) = .CreateParameter("Ending_Date", adDate, adParamInput)
prm(2).Value = "June 30, 1997"
For i = 1 To UBound(prm)
.Parameters.Append prm(i)
Next i
End With
Application.StatusBar = "Executing ..."
Set rstSQL = cmdSQL.Execute
' Select destination for results
With ThisWorkbook.Worksheets(6)
.Activate
.UsedRange.EntireColumn.Delete
Application.StatusBar = "Populating ..."
fldCount = rstSQL.Fields.Count
For iCol = 1 To fldCount
.Cells(1, iCol).Value = rstSQL.Fields(iCol - 1).Name
Next
.Cells(2, 1).CopyFromRecordset rstSQL
Application.StatusBar = "Formatting ..."
.Rows(1).Font.Bold = True
.UsedRange.Columns.AutoFit
.Cells(1, 1).Activate
End With
Application.StatusBar = "Closing ..."
rstSQL.Close
cnnSQL.Close
Set rstSQL = Nothing
Set cmdSQL = Nothing
Set cnnSQL = Nothing
Application.StatusBar = False
End Sub

Is that what you were looking for? HTH and let me know how you get on.

Cheers, Sean.

"Souris" wrote:

I have a SQL server procedure like following:

MystoredProc @DateLow as DateTime, @DateHigh as DateTime

I would like to use Excel to retrieve information.

I wanted to use MS SQL and pass cell A1 as DateLow and A2 as DateHigh.

Is it possible to do this?
If yes, Can you please give me some instructions to do it?

Thanks millions,