Excel VBA调用存储过程报错：对象关闭时不允许操作

出现“Operation is not allowed when the object is closed.”错误，主要是两个原因：

1. VBA中未明确指定Command对象的类型为存储过程，导致ADODB解析调用逻辑出错；
2. 存储过程里的INSERT操作会返回受影响行数的消息，ADODB将其视为第一个结果集，而你需要的查询结果在后续结果集中，最终导致Recordset无法正确获取数据。

下面是具体的解决方法：

一、修改VBA代码，指定CommandType #

在设置cmd.CommandText之后，添加一行代码明确指定命令类型为存储过程：

cmd.CommandType = 4 ' 对应ADODB的adCmdStoredProc常量，无需额外引用库

修改后的完整VBA代码：

Sub ExecuteStoredProcedureAndRetrieveData()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "" ' 填写你的数据库连接字符串
    conn.Open

    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "ProcedureStored"
    cmd.CommandType = 4 ' 明确指定为存储过程类型

    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open cmd

    Sheets("Sheet1").Range("A1").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    cmd.ActiveConnection = Nothing
    conn.Close
    Set conn = Nothing
End Sub

二、修改存储过程，添加SET NOCOUNT ON #

在存储过程的BEGIN语句后第一行添加SET NOCOUNT ON;，抑制中间操作返回的行数消息，让ADODB直接获取最终的查询结果集：

修改后的存储过程代码：

CREATE PROCEDURE ProcedureStored
AS
BEGIN
    SET NOCOUNT ON; -- 抑制中间操作的行数返回消息

    DECLARE @day_date DATE = '1973-12-25';
    DECLARE @date_start DATETIME;
    DECLARE @date_end DATETIME;

    SET @date_start = CAST(CONVERT(VARCHAR, @day_date) + ' 06:00:00' AS DATETIME);
    SET @date_end = CAST(CONVERT(VARCHAR, @day_date) + ' 16:30:00' AS DATETIME);

    DECLARE @Hours TABLE (Date DATETIME, Hour INT);
    DECLARE @i INT;
    DECLARE @j INT;
    DECLARE @loop_count INT;
    DECLARE @date_change_flag INT;

    SET @i = CONVERT(INT, DATEPART(HOUR, @date_start));
    SET @j = CONVERT(INT, DATEPART(HOUR, @date_end));
    SET @loop_count = 0;
    SET @date_change_flag = 0;

    WHILE @loop_count < 11
    BEGIN
        IF @i < 24 AND @date_change_flag = 0
        BEGIN
            INSERT INTO @Hours
            VALUES (@date_start, @i);
        END
        ELSE IF @i >= 24 AND @date_change_flag = 0
        BEGIN
            SET @date_change_flag = 1;
            SET @i = 0;

            INSERT INTO @Hours
            VALUES (DATEADD(DAY, 1, @date_start), @i);
        END
        ELSE IF @date_change_flag = 1
        BEGIN
            INSERT INTO @Hours
            VALUES (DATEADD(DAY, 1, @date_start), @i);
        END

        SET @loop_count = @loop_count + 1;
        SET @i = @i + 1;
    END

    SELECT 'ID 19' AS [ID], [Date], [Hour],
           SUM(ISNULL([Errors (Delays)], 0)) AS [Errors (Delays)]
    FROM
    (
        SELECT 'ID' AS [ID], FORMAT([time_stamp], 'yyyy-MM-dd') AS [Date], DATEPART(HOUR, [time_stamp]) AS [Hour], COUNT(*) AS [Errors (Delays)]
        FROM [error_events]
        WHERE [error_type] = 0 AND [time_stamp] BETWEEN @date_start AND @date_end
          AND [source] LIKE '%Filter1%'
          AND ([trace_path] LIKE '%/Filter2%' 
               OR [trace_path] LIKE '%/Filter3%' 
               OR [trace_path] LIKE '%/Filter4%' 
               OR [trace_path] LIKE '%/Filter5%')
        GROUP BY FORMAT([time_stamp], 'yyyy-MM-dd'), DATEPART(HOUR, [time_stamp])
        UNION ALL
        SELECT 'ID' AS [ID], FORMAT(DATE, 'yyyy-MM-dd') AS [Date], Hour AS [Hour], NULL
        FROM @Hours
    ) AS Data
    GROUP BY [Date], [Hour]
    ORDER BY [ID];
END

补充说明 #

• 指定CommandType后，ADODB会正确识别这是存储过程调用，而非普通SQL语句，避免解析错误；
• SET NOCOUNT ON会关闭中间DML操作的行数反馈，确保ADODB只接收你需要的SELECT结果集，不会因为多个结果集导致Recordset异常关闭。

内容的提问来源于stack exchange，提问作者hydctrl