I have a slight issue retrieving multiple recordsets (with column names) and pasting the data into an excel sheet, like the image below
我有一个小问题,检索多个记录集(带列名)并将数据粘贴到Excel工作表中,如下图
The VBA code that I have just retrieves the first recordset but not the rest
我刚才检索第一个记录集而不是其余记录集的VBA代码
Any help will be much appreciated , thank you
非常感谢任何帮助,谢谢
Sub CProcedure()
Dim Conn As ADODB.Connection, RecordSet As ADODB.RecordSet
Dim Command As ADODB.Command
Dim ConnectionString As String, StoredProcName As String
Dim range1 As ADODB.Parameter, range2 As ADODB.Parameter
Dim SP_Param1 As String
Dim SP_Param2 As String
Application.ScreenUpdating = False
Set Conn = New ADODB.Connection
Set RecordSet = New ADODB.RecordSet
Set Command = New ADODB.Command
ServerName = "1111"
DatabaseName = "dataReporting"
UserId = "88888"
Password = "88888"
SP_Param1 = "StartDate"
SP_Param2 = "EndDate"
StoredProcName = "KPI_Report"
ConnectionString = "PROVIDER=SQLOLEDB;DATA SOURCE=" & ServerName & _
";INITIAL CATALOG=" & DatabaseName & "; User Id=" & _
UserId & "; Password=" & Password & ";"
Conn.Open ConnectionString
With Command
.ActiveConnection = Conn
.CommandType = adCmdStoredProc
.CommandText = StoredProcName
.CommandTimeout = 0
End With
Set range1 = Command.CreateParameter(SP_Param1, adDBDate, adParamInput, , DateSerial(2018, 1, 1))
Command.Parameters.Append range1
Set range2 = Command.CreateParameter(SP_Param2, adDBDate, adParamInput, , DateSerial(2018, 4, 1))
Command.Parameters.Append range2
Set RecordSet = Command.Execute
Sheets("Sheet1").Range("A2").CopyFromRecordset RecordSet
RecordSet.Close
Conn.Close
On Error GoTo 0
Application.ScreenUpdating = True
Exit Sub
End Sub
Update
更新
I added the below loop but still no luck
我添加了以下循环,但仍然没有运气
'Loop through recordset and place values
RecordSet.MoveFirst
Do While RecordSet.EOF = False
For i = 0 To RecordSet.Fields.Count - 1
ActiveCell.Value = RecordSet.Fields(i).Value
ActiveCell.Offset(0, 1).Activate
Next i
ActiveCell.Offset(1, -i).Activate
RecordSet.MoveNext
Loop
Proc Code below
下面的代码
Sub storeproc()
Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.RecordSet
Dim i As Integer
Dim sConnect As String
ServerName = "1111"
DatabaseName = "dataReporting"
UserId = "88888"
Password = "88888"
SP_Param1 = "StartDate"
SP_Param2 = "EndDate"
StoredProcName = "KPI_Report"
sConnect = "PROVIDER=SQLOLEDB;DATA SOURCE=" & ServerName &
";INITIAL CATALOG=" & DatabaseName & "; User Id=" & UserId &
"; Password=" & Password & ";"
'Establish connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
'Open recordset
Set ADODBCmd = New ADODB.Command
ADODBCmd.ActiveConnection = Conn
ADODBCmd.CommandText = StoredProcName
ADODBCmd.CommandType = adCmdStoredProc
ADODBCmd.CommandTimeout = 0
Set range1 = ADODBCmd.CreateParameter(SP_Param1, adDBDate, adParamInput, , DateSerial(2018, 1, 1))
ADODBCmd.Parameters.Append range1
Set range2 = ADODBCmd.CreateParameter(SP_Param2, adDBDate, adParamInput, , DateSerial(2018, 4, 1))
ADODBCmd.Parameters.Append range2
Set rs = ADODBCmd.Execute()
'Loop through recordset and place values
rs.MoveFirst
Do While rs.EOF = False
For i = 0 To rs.Fields.Count - 1
ActiveCell.Value = rs.Fields(i).Value
ActiveCell.Offset(0, 1).Activate
Next i
ActiveCell.Offset(1, -i).Activate
rs.MoveNext
Loop
'Clean up
rs.Close
Set rs = Nothing
End Sub
2 个解决方案
#1
2
The answer of S Meaden mentioning the GetNextRecordSet
solves your issue having multiple recordsets as result of a stored proc.
The following code dumps all recordsets, including the field names, into a sheet (do not forget to cleanup the sheet first if you run it repeatedly).
S Meaden提到GetNextRecordSet的答案解决了因存储过程导致多个记录集的问题。以下代码将所有记录集(包括字段名称)转储到工作表中(如果重复运行,请不要忘记首先清理工作表)。
...
Set rs = Command.Execute
Dim startcol As Long
startcol = 1
With ThisWorkbook.Sheets(1)
Do While Not rs Is Nothing
Dim col As Long
For col = 0 To rs.Fields.Count - 1
.Cells(1, startcol + col) = rs.Fields(col).Name
Next col
.Cells(2, startcol).CopyFromRecordset rs
startcol = startcol + rs.Fields.Count + 1
Set rs = rs.NextRecordset
Loop
End With
#2
0
So the Stored procedure needs to return more than one recordset such as
因此,存储过程需要返回多个记录集,例如
CREATE PROCEDURE GetCarsAndCategories
AS
BEGIN
SELECT * FROM Cars
SELECT * FROM Categories
END
and then the VBA code initially has access to the Cars recordset and to get to the Categories recordset one calls GetNextRecordSet
然后VBA代码最初可以访问Cars记录集并进入类别记录集,一个调用GetNextRecordSet
#1
2
The answer of S Meaden mentioning the GetNextRecordSet
solves your issue having multiple recordsets as result of a stored proc.
The following code dumps all recordsets, including the field names, into a sheet (do not forget to cleanup the sheet first if you run it repeatedly).
S Meaden提到GetNextRecordSet的答案解决了因存储过程导致多个记录集的问题。以下代码将所有记录集(包括字段名称)转储到工作表中(如果重复运行,请不要忘记首先清理工作表)。
...
Set rs = Command.Execute
Dim startcol As Long
startcol = 1
With ThisWorkbook.Sheets(1)
Do While Not rs Is Nothing
Dim col As Long
For col = 0 To rs.Fields.Count - 1
.Cells(1, startcol + col) = rs.Fields(col).Name
Next col
.Cells(2, startcol).CopyFromRecordset rs
startcol = startcol + rs.Fields.Count + 1
Set rs = rs.NextRecordset
Loop
End With
#2
0
So the Stored procedure needs to return more than one recordset such as
因此,存储过程需要返回多个记录集,例如
CREATE PROCEDURE GetCarsAndCategories
AS
BEGIN
SELECT * FROM Cars
SELECT * FROM Categories
END
and then the VBA code initially has access to the Cars recordset and to get to the Categories recordset one calls GetNextRecordSet
然后VBA代码最初可以访问Cars记录集并进入类别记录集,一个调用GetNextRecordSet