I have the following code in my Excel workbook that I copied from this page.
Sub Button1_Click() Dim con As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim WSP1 As Worksheet Set con = New ADODB.Connection Set cmd = New ADODB.Command Set rs = New ADODB.Recordset '''Clear extract area''' Worksheets("Extract").UsedRange.Delete '''Log into SQL Server''' con.Open "Provider = SQLOLEDB;" & _ "Data Source = MySource;" & _ "Initial Catalog = MyDB;" & _ "User ID = MyID;" & _ "Password = MyPassword;" cmd.ActiveConnection = con '''Set up parameters for stored procedure''' cmd.Parameters.Append cmd.CreateParameter("startDate", adDate, adParamInput, , Range("C2")) cmd.Parameters.Append cmd.CreateParameter("endDate", adDate, adParamInput, , Range("C3")) cmd.CommandText = "DB.StoredProc" Set rs = cmd.Execute(, , adCmdStoredProc) Set WSP1 = Worksheets("Extract") WSP1.Activate If rs.EOF = False Then WSP1.Cells(1, 1).CopyFromRecordset rs rs.Close Set rs = Nothing Set cmd = Nothing con.Close Set con = Nothing End Sub
I get the following error message on the line 'If rs.EOF = False Then'
"Operation is not allowed when the object is closed."
This is the first time I've used these functions. What have I done wrong?
Also, have I set up the multiple parameters correctly?
---Quick Edit--- Not sure if it's worth mentioning that I have formatted my date as yyyy-mm-dd, as it is in SQL server.
I've solved the issue by adding
SET NOCOUNT ON to my stored procedure.
I wasn't initially sure if I would have access to be able to make that change. I have a new issue though. It works if I only use a single parameter but not when I use multiple parameters.
I will close this thread, do some research and potentially open a new one.
Thanks for your help.