QueryTable connection using ADO Recordset

[Guest]

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.

 

[NickHK]

Do you need to create another instance of Excel ?

You seems to be confusing yourself which instance you are working with.

'Created instance
osheet.Activate

'This instance - no oExcel qualifier
Set oQueryTable = ActiveSheet.QueryTables.Add(Connection:=rs,

'Created instance
Destination:=oExcel.ActiveSheet.Range("A1"))

NickHK

 

[Guest]

Honestly, those details don't matter - no, its not invoking another instance
of Excel. I read somewhere that the Destination parameter needs EXPLICIT
instructions on the sheet name. The issue I'm having is that I cannot
refresh the querytable once its defined. Can anyone help with this.

Thanks.

 

[NickHK]

What do you think this line means ?

Set oExcel = CreateObject("Excel.Application")

NickHK