在Excel中使用VBA执行SQL Server存储过程并传递变量

Sub ExecuteStoredProcedure()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object
    Dim connectionString As String
    Dim procedureName As String
    Dim inputParam As String
    Dim outputParam As String
    
    ' 设置连接字符串
    connectionString = "Provider=SQLOLEDB;Data Source=YourServerName;Initial Catalog=YourDatabaseName;Integrated Security=SSPI;"
    
    ' 设置存储过程名称和参数
    procedureName = "YourStoredProcedureName"
    inputParam = "InputValue"
    outputParam = ""
    
    ' 创建ADODB连接对象
    Set conn = CreateObject("ADODB.Connection")
    conn.Open connectionString
    
    ' 创建ADODB命令对象
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = procedureName
    
    ' 添加输入参数
    cmd.Parameters.Append cmd.CreateParameter("@InputParam", adVarChar, adParamInput, 50, inputParam)
    
    ' 添加输出参数
    cmd.Parameters.Append cmd.CreateParameter("@OutputParam", adVarChar, adParamOutput, 50)
    
    ' 执行存储过程
    cmd.Execute
    
    ' 获取输出参数的值
    outputParam = cmd.Parameters("@OutputParam").Value
    
    ' 关闭连接
    conn.Close
    
    ' 输出结果
    MsgBox "Output Parameter: " & outputParam
End Sub