Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can I execute a stored procedure in Microsoft Excel and get all the data it returns?

share|improve this question
    
I guess this shows how important correct tagging is. When I answered, the vba tag wasn't there, and I didn't think it to be a vba question ... –  takrl Aug 24 '11 at 11:07

3 Answers 3

up vote 4 down vote accepted

This is a job for an ADODB connection in VBA. Here's a link with a sample code for a simple SELECT query, but this will handle stored procedures just as well.

http://www.ozgrid.com/forum/showthread.php?t=83016&page=1

The key items are the need to declare an ADODB.Connection, ADODB.Recordset, and a connection string that matches your database. After opening the connection, you execute your SQL statement using syntax like the following (taken from the link):

With cnt 
    .CursorLocation = adUseClient 
    .Open stADO    // stADO is the connection string.
    .CommandTimeout = 0 
    Set rst = .Execute(stSQL) 
End With

Then move the data from your recordset (rst, above) into a range using Range.CopyFromRecordSet.

share|improve this answer

I'm not sure about the latest incarnation of Excel, but in 2000 and 2003 all you could do was access a view and have it's data displayed on the excel sheet.

The main advantage of a stored procedure would be the ability to parameterize the result, but for that you'd need some sort of UI, and you'd need a way to programmatically modify the query definition after it's first defined within excel. We didn't find a way to do this, but using views provided enough functionality for what we needed to do.

share|improve this answer

This VBA is very similar to @Excellll's answer, and I've used it to good effect in my own work.

Use this little utility function:

Public Function IsEmptyRecordset(rs As Recordset) As Boolean
     IsEmptyRecordset = ((rs.BOF = True) And (rs.EOF = True))
End Function

And then here's the big function (I apologize for the crummy-looking paragraph alignment):

Option Explicit

Public Sub OpenConnection()
Dim conn As ADODB.Connection
Dim str As String
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim myPath
Dim fld
Dim i As Integer

On Error GoTo errlbl


'Open database connection
Set conn = New ADODB.Connection

'First, construct the connection string.

'NOTE:  YOU CAN DO THIS WITH A STRING SPELLING OUT THE ENTIRE CONNECTION...
'conn.ConnectionString = _
'    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
'    "Data Source=" & _
'    myPath & "\ConnectionTest.mdb"

'...OR WITH AN ODBC CONNECTION YOU'VE ALREADY SET UP:
conn.ConnectionString = "DSN=myDSN"

conn.Open       'Here's where the connection is opened.

Debug.Print conn.ConnectionString  'This can be very handy to help debug!

Set rs = New ADODB.Recordset
'Construct string.  This can "Select" statement constructed on-the-fly,
'str = "Select * from vwMyView order by Col1, Col2, Col3"  
'or an "Execute" statement:
str = "exec uspMyStoredProc"

rs.Open str, conn, adOpenStatic, adLockReadOnly  ‘recordset is opened here

If Not IsEmptyRecordset(rs) Then     
    rs.MoveFirst

    'Populate the first row of the sheet with recordset’s field names
    i = 0
    For Each fld In rs.Fields
        Sheet1.Cells(1, i + 1).Value = rs.Fields.Item(i).Name
        i = i + 1
    Next fld
    'Populate the sheet with the data from the recordset
    Sheet1.Range("A2").CopyFromRecordset rs     


Else
    MsgBox "Unable to open recordset, or unable to connect to database.", _
       vbCritical, "Can't get requested records"

End If

'Cleanup
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

exitlbl:
  Debug.Print "Error: " & Err.Number
  If Err.Number = 0 Then
    MsgBox "All data has been pulled and placed on Sheet1", vbOKOnly, "All Done."
  End If
  Exit Sub
errlbl:
   MsgBox "Error #: " & Err.Number & ", Description:  " & Err.Description, _     vbCritical, "Error in OpenConnection()"
Exit Sub
'Resume exitlbl
End Sub

Hope this helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.