I followed the code from the Ownwise 56.7 (https://www.youtube.com/watch?v=hYq0ElVLZhg) to run a SQL procedure with VBA to excel, the code bellow works fine if the procedure is small and takes less than 30 seconds, for a larger procedures (60seconds) it gave me an error "Run time error 3704 - Operation is not allowed when the object is closed" when I am at line "ws.range("a2").copyrecordset rs" I think it is saying that the recordset is close or empty, but it doesn't make sense because it worked for small procedure. Any way to define the "commandtimeout=0" to the recordset?
(sorry but pasting the code didn't work as planed)
If you have a solution I will be very thankful. cheers
Public Sub Get_Results_From_SP()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim NumResults As Long
Dim TotalOscars As Long
Dim AvgLength As Double
Dim AvgBoxOffice As Double
Dim wsReport As Worksheet
Dim ws As Worksheet
Dim i As Integer
Dim Server_Name As String
Dim DatabaseName As String
Dim SQL As String
Dim querydate As String
Dim strConn As String
Dim NewFile As Workbook
Dim col As Integer
Dim tw As Workbook
Dim LWeekday As Integer
Dim Lmes, Lano, Ldia As Integer
Dim n As Integer
Dim FSO, f As Object
Dim Pth, Pth2, FileSource, FileDest As String
Set cn = New ADODB.Connection
cn.ConnectionString = _
"CommandTimeout = 0;" & _
"Provider=PROVIDER NAME;" & _
"Server=SERVER NAME;" & _
"Database=DATABASE NAME;" & _
"Trusted_Connection=yes;"
cn.Open
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "PROCEDURE_NAME"
cmd.CommandTimeout = 0
Set rs = cmd.Execute
Set ws = ThisWorkbook.Worksheets.Add
ws.Select
For i = 0 To rs.Fields.Count - 1
ws.Cells(1, i + 1).Value = rs.Fields(i).Name
Next I
ws.Range("A2").CopyFromRecordset rs
ws.Range("A1").CurrentRegion.EntireColumn.AutoFit
Set ws = Nothing
rs.Close
cn.Close
End Sub