Excel 2007 AND Retrieving Data from Stored Procedure on SQL Exp 20
 

Currently have some code that when I pass through a select * on a dbo.tbl it
the code retrieves all the table data and places back on sheet1. When I pass
through the stored procedure, it retrieves nothing back. If we run the
stored procedure which is a select of 5 tables, it retrieves the data back.

Any ideas or assistance would be great. See code below.

Sub GetData()

Dim c As ADODB.Connection
Dim rsPubs As ADODB.Recordset

Set c = New ADODB.Connection
Set rsPubs = New ADODB.Recordset

c.Open "DSN=Portfolio"

With rsPubs
.ActiveConnection = c


rsPubs.Open "dbo.sp_getstaffinventory"

Sheet1.Range("A1").CopyFromRecordset rsPubs

.Close

End With

c.Close
Set c = Nothing

End Sub