How to execute a stored SQL procedure in Microsoft Excel?
Solution 1
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
.
Solution 2
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.
Solution 3
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.
Safran Ali
like to do web stuff specifically .Net professional, currently working on social media integration ... beside that ... make friends, have a chit chat while having a coffee ... and do some more bits to enjoy life ...
Updated on September 18, 2022Comments
-
Safran Ali over 1 year
How can I execute a stored procedure in Microsoft Excel and get all the data it returns?
-
SUBIN CHANDRAN over 12 yearsI 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 ...
-