在 Excel 消息框中显示 SQL Server 过程调用的结果

Question

>我在Excel中有一个VBA子。 它需要调用 SQL Server 过程，并在消息框中显示调用的结果。 (主要是因为我正在维护别人的代码 - 否则，我不会使用 Excel。

我创建我的 SQL 语句来调用该过程。 我有打开连接的潜艇。 但是在显示该过程调用的结果的过程中，我仍然缺少一些东西。

这是我到目前为止所拥有的：

Dim Today As Date
Dim result As Date
Dim sSQLStatement As String
Dim strDate As String
Dim Recordset As ADODB.Recordset
Today = DateTime.Now()
result = DateSerial(Year(Today), Month(Today) - 1, 1)
strDate = Year(result) & "-" & Format(Month(result), "00") & "-" & Format(Day(result), "00")
DBOpen
sSQLStatement = "set nocount on; EXEC [MySchema].[dbo].[MyProcedure] @END_DATE = N'" & strDate & "'"
MsgBox sSQLStatement
Dim i As Long
Dim Ary
Dim strMsg As String
Set Recordset = New ADODB.Recordset
With Recordset
    .ActiveConnection = cnPubs 'this is defined elsewhere, used in the DBOpen call, and works
    'I can call the execute and it works, but it fails here when I try to assign the results to the recordset
     Recordset = cnPubs.Execute(sSQLStatement)
     'I found this online and don't know yet if it works.  I have to get past the statement above first.
     If Not Recordset.EOF Then
          Ary = Recordset.GetRows
          For i = 0 To UBound(Ary, 2)
              If i = 0 Then
                  strMsg = Ary(0, i)
              Else
                  strMsg = strMsg & ", " & Ary(0, i)
              End If
          Next i
          MsgBox strMsg, , UBound(Ary, 2) + 1 & " records"
     End If
     .Close
End With
DBClose

因此，如果我自己调用cnPubs.Execute(sSQLStatement(，我可以判断它正在运行。 我打开和关闭连接就好了。 但它应该返回一组几行，我需要显示它。 我不想将它们写到任何地方的电子表格中 - 它们应该只显示在消息框中。

Answer 1

更改

Recordset = cnPubs.Execute(sSQLStatement)

自

recordset.open sSQLStatement, cnPubs