• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Sql Return multiple results to multiple worksheets

Morning Experts,

I connect excel to sql through using vba and executing a stored procedure.  In the past I have only allowed the Stored procedure to return one data set and then copy it to a single worksheet.  Now I was wondering if there is a way to deal with a stored procedure that returns 3 data sets that would put them on sheets (data1, data2, data3).  


Sub Data_Sheet()

    Dim cn As New ADODB.Connection
    Dim comm As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim dbConnectStr As String
    Dim strSheet As String
    Dim strRange As String
    Dim vName As String
    Dim vBDate As Date
    Dim vEDate As Date

    Application.ScreenUpdating = False

    ' Connection to SQL Server
    UID = "blah"
    Password = "pwrd"
    Srv = "connection"


    strSheet = "Data1"
    strRange = "A2"
    vName = ActiveWorksheet("test").Range("A2").Value
    vData = Workbooks("Loop and open files in directory").Worksheets("Sheet1").Range("A2").Value

    


    'dbConnectStr
    cn.ConnectionString = "Driver={SQL Server};" & _
                          "Server=" & Srv & ";" & _
                          "User Id=" & UID & ";" & _
                          "Password=" & Password
     cn.ConnectionTimeout = 0    ' Set Connection to not Time Out


    comm.CommandType = adCmdText
    comm.CommandText = "EXEC SP " & " " & "'" & vName & "'" & "," & "'" & vdata & "'"    'Execute Stored Procedure

    Set comm.ActiveConnection = cn
    rs.ActiveConnection = cn

    comm.CommandTimeout = 0
    rs.Open comm

    'Clear Previous data

    Sheets(strSheet).Select
    Range(strRange).Select
    Range(strRange, "P100").Select
    Selection.ClearContents
    Range(strRange).Select


    Sheets(strSheet).Range(strRange).CopyFromRecordset rs  'copy the records
    
    Call FormulaFill

    rs.Close    
   
 cn.Close

    Application.ScreenUpdating = True
    '
    MsgBox "Done"
   
End Sub

Open in new window


Thanks,
Montrof
0
montrof
Asked:
montrof
1 Solution
 
Rory ArchibaldCommented:
Once you have output the first recordset, use
set rs = rs.nextrecordset

Open in new window

output that to the relevant sheet and then repeat. :)
0
 
montrofAuthor Commented:
awesome!!! Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now