Exporting ACCESS Stored Procs into Excel

[Rob Wills]

Hi,

I have created a "Parameterized Stored Procedure" in Access 2007 using the
following ADODB command

Conn.Execute "CREATE PROCEDURE uspBalance_Summary_Rec1 (@Owner Text(50)) AS
......"

I wish to export data from the database into a spreadsheet based on the
owner specified in the code.

I have tried Transferspreadsheet - that doesn't work and have been looking
at OutputTo

DoCmd.OutputTo acOutputStoredProcedure, "uspBalance_Summary_Rec1 ""Fred""",
acformatxls, "c:\Temp\Fred.xls"

This doesn't appear to work either...

Any ideas?

Thanks
Rob

 

[pietlinden]

Rob,

How about this:
Open an ADO recordset off you stored procedure.
Copy the data to excel like this:

xlWs.Cells(2, 1).CopyFromRecordset rstAccess


Here's the article with all the details:
http://support.microsoft.com/kb/246335/EN-US/

HTH,

Pieter

 

[Rob Wills]

Hi Pieter,

Yes I was aware of that methodology - and have used it before in Excel.
However I am wanting to push from Access into Excel

Using your method would mean writing code in Access to open an Excel app -
to pull the data in from Access - seems slightly perverse?!

 

[Rob Wills]

In fact - I've decided it's easier to create a query that uses a "control"
table that I can populate with my parameters....