从excel 2007 VBA打开mssql存储过程并将数据导入excel

Question

我尝试使用VBA从Excel 2007中调用存储过程。我想从存储过程返回到Excel表单。 。但我得到的 “运行时，当对象被关闭3074不允许操作”的工作表（“工作表Sheet1”）范围（“A1”）CopyFromRecordset objMyRecordset

下面的代码是我写的：

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 

Answer 1

您需要指定要调用的程序如下：

...

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

...