CODEWITHSUNDEEP
excelexcel-2007vba
user1188125
user1188125

Reputation: 687

open mssql stored procedure from excel 2007 VBA and get data to excel

I try to call a stored procedure from excel 2007 by VBA. I would like get return from stored procedure into excel sheet. But I get the 'run-time 3074 Operation is not allowed when the object is closed' on Worksheets("Sheet1").Range("A1").CopyFromRecordset objMyRecordset

below is the code I write:

Option Explicit

Sub OpenConnection()

Dim objMyConn As ADODB.Connection
Dim objMyCmd As ADODB.Command
Dim objMyRecordset As ADODB.Recordset
Dim ConnectionString As String
Dim CommandText As String

Set objMyConn = CreateObject("ADODB.Connection")
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset



ConnectionString = "Provider=sqloledb;Data Source=abcsql,1433;Initial Catalog=admin;User Id=sa;Password=1234abcd;"
objMyConn.Open ConnectionString


Set objMyCmd.ActiveConnection = objMyConn
CommandText = "exec [admin].[dbo].[sp_weekly]"
objMyCmd.CommandType = adCmdText

Set objMyRecordset.Source = objMyCmd
objMyRecordset.Open 

Worksheets("Sheet1").Range("A1").CopyFromRecordset objMyRecordset

end

Upvotes: 0

Views: 1238

Answers (1)

ron tornambe
ron tornambe

Reputation: 10780

You need to specify that you are calling a procedure as follows:

...

Set objMyCmd.ActiveConnection = objMyConn
CommandText = "sp_weekly"
objMyCmd.CommandType = adCmdStoredProc

...

Upvotes: 1

Related Questions