QueryTable connection using ADO Recordset

J

jfs

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.
 
N

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
 
J

jfs

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top