I have the following code snippet to transfer the results of a SQL Server
2000 sp to an Excel Sheet:

Dim Newcn as new adodb.connection
Dim oQueryTable As Excel.QueryTable

Newcn.CommandTimeout = 0
Newcn.ConnectionString = ConnString ' Connection string passed into this
procedure
Newcn.Open
Newcn.Errors.Clear

' Create a record set with the results from the stored proc
Set rs = New ADODB.Recordset

rs.Open QueryString, Newcn, adOpenStatic, adLockOptimistic

Set oExcel = CreateObject("Excel.Application")

Set oBook = oExcel.Workbooks.Open(ExportTo)

Set osheet = oBook.Worksheets(TabName)

'Transfer the data to Excel

osheet.Activate
Set oQueryTable = ActiveSheet.QueryTables.Add(Connection:=rs,
Destination:=oExcel.ActiveSheet.Range("A1"))

When it attempts to add the querytable, I get the following error:

Error number 5
Invalid procedure call or argument

I have tried using an OLEDB connection to add the Querytable which works,
but when I attempt to refresh it with the stored procedure as the
commandtext, I get "The Query did not run, or the database table could not be
opened."

I have checked access permissions in SQL and all is well - the sp will run
from Query Analyzer.

Any insight would be most welcome.
Thanks.