Exporting StoredProcedure to Excel in VBA 
Author Message
 Exporting StoredProcedure to Excel in VBA

I have a stored procedure on SQL server I would like to export to Excel and
would like to do this in a form wrapping a user interface around parameter
entry.

DoCmd.OpenStoredProcedure doesn't appear to allow parameters, so I wrote
some VBA to do an ADODB command, iterate through the resulting recordset,
and send out the data to an Excel spreadsheet via automation.

The problem I have is that this is *much* slower than running the stored
procedure saving the data as a spreadsheet.  I've tried mucking with the
recordset's CursorLocation and CursorType, it doesn't seem to make much of a
difference, I'm sure most of the overhead is the OLE automation calls to
Excel.

Does anybody have any ideas on how I can speed up the export of a stored
procedure from an .ADP file to an Excel spreadsheet in VBA?

Thanks in advance...



Sat, 22 May 2004 02:40:21 GMT
 Exporting StoredProcedure to Excel in VBA

you are trying to export a stored procedure itself into Excel? or the
results of its execution?


Quote:
> I have a stored procedure on SQL server I would like to export to Excel
and
> would like to do this in a form wrapping a user interface around parameter
> entry.

> DoCmd.OpenStoredProcedure doesn't appear to allow parameters, so I wrote
> some VBA to do an ADODB command, iterate through the resulting recordset,
> and send out the data to an Excel spreadsheet via automation.

> The problem I have is that this is *much* slower than running the stored
> procedure saving the data as a spreadsheet.  I've tried mucking with the
> recordset's CursorLocation and CursorType, it doesn't seem to make much of
a
> difference, I'm sure most of the overhead is the OLE automation calls to
> Excel.

> Does anybody have any ideas on how I can speed up the export of a stored
> procedure from an .ADP file to an Excel spreadsheet in VBA?

> Thanks in advance...



Sat, 22 May 2004 23:17:46 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. VBA to export from Excel to SQL Server

2. LDIF-file-import to Excel and prepare data for VBA-ADSI-Export

3. How to execute StoredProcedure from VBA ?

4. Export to Excel - numbers are exported as text

5. DTS Export to Excel with Detailed Excel Formatting

6. Exporting recordset into Excel and opening Excel

7. Controlling ADP from Excel with VBA

8. Mixing Excel, VBA and SQL server

9. DTS / VBA Excel

10. How to save a Excel worksheet interactively to SQL from VBA/DTS

11. Excel VBA In ActiveX Help

12. Connecting to an SQL ODBC source from Excel VBA


 
Powered by phpBB® Forum Software