Prev: Error on Excel 2003 Workbook_Open XmlMaps
Next: Excel Object Model Diagram?
From: jfs on 23 Nov 2006 14:36
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.
From: NickHK on 23 Nov 2006 21:13
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


"jfs" <jfs(a)discussions.microsoft.com> wrote in message
news:D0F6F5F1-6881-44F5-9FC2-A509A66BB0BA(a)microsoft.com...
> 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.


From: jfs on 24 Nov 2006 08:29
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" wrote:

> 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
>
>
> "jfs" <jfs(a)discussions.microsoft.com> wrote in message
> news:D0F6F5F1-6881-44F5-9FC2-A509A66BB0BA(a)microsoft.com...
> > 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.
>
>
>
From: NickHK on 26 Nov 2006 20:54
What do you think this line means ?
> Set oExcel = CreateObject("Excel.Application")

NickHK

"jfs" <jfs(a)discussions.microsoft.com> wrote in message
news:E23F22EB-CE9C-4554-9D01-40B2307FF25F(a)microsoft.com...
> 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" wrote:
>
> > 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
> >
> >
> > "jfs" <jfs(a)discussions.microsoft.com> wrote in message
> > news:D0F6F5F1-6881-44F5-9FC2-A509A66BB0BA(a)microsoft.com...
> > > 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.
> >
> >
> >


 | 
Pages: 1
Prev: Error on Excel 2003 Workbook_Open XmlMaps
Next: Excel Object Model Diagram?