Exporting s Stored Procedure to Excel

[Darkside12]

Hi,

A simple question but, is it possible to export a stored Procedure to excel using the docmd.TransferSpreadsheet function?

If not, what is the easiest way to accomplish this?

[Stewart Ross]

Welcome to Bytes, Darkside.

Visual Basic for Applications procedures cannot be transferred using the TransferSpreadsheet method. Access VBA code is stored in code modules within the database, and similarly user-defined VBA procedures in Excel are stored in code modules within the workbook itself.

When TransferSpreadsheet is used to export data to Excel it transfers the results of the query concerned, not the underlying method of calculation. It is equivalent to pasting values into an Excel sheet.

Whilst it is possible to export and import complete code modules (not individual procedures) between Access and Excel doing so automatically requires VBA coding. It is more involved than I can show here at present.

If the task is one-off it is much easier to do this manually from the VBA environment window in each application (in Access to export the code module concerned, and in Excel to import the module into the workbook).

It does not take long to do such an import, although it may then prove necessary to adapt or rewrite the Access code to fit the different environment of an Excel workbook - this can be quite a time-consuming task, depending on how much or little of the code has to be changed.

-Stewart

[Darkside12]

Hi Stewart,

Probably should have explained the problem more thoroughly.

I have an Access Project database linked to SQL Server. I'm trying to create a loop that will change the variable in a stored procedure each time through the loop and then output the results to excel.

Creating the code to generate the procedure was no problem but..... exporting the procedures output to excel is proving more difficult. I tried creating a from that used the procedure as a datasource and then outputting the form but got the same results as trying to export the procedure directly.

I have noticed however that I can export tables in access project using the docmd.transferspreadsheet method.

So, is it feasible to modify my sql to generate a table each time through the loop (I can just delete the table after export). Or is there a simpler way that I'm missing.

Any help you can give me would be greatly appreciated

Dark

[Stewart Ross]

Hi Dark. Sure, within a loop you can generate an SQL string of the form "SELECT ... INTO sometable", then run this using either the Execute or RunSQL methods to generate the table. You can use TransferSpreadsheet on the temporary table on each pass through the loop without a problem, although you would need to vary the output name on each occasion (or you will just overwrite the last output on each successive pass).

I'm still unclear what you are referring to when you use the term 'stored procedure', since it is clearly not VBA code you are meaning; do you mean a set of operations (somewhat like an Access macro)? Or are you meaning an SQL query, where you supply some form of parameter?

-Stewart

[ADezii]

Hi Stewart,

Probably should have explained the problem more thoroughly.

I have an Access Project database linked to SQL Server. I'm trying to create a loop that will change the variable in a stored procedure each time through the loop and then output the results to excel.

Creating the code to generate the procedure was no problem but..... exporting the procedures output to excel is proving more difficult. I tried creating a from that used the procedure as a datasource and then outputting the form but got the same results as trying to export the procedure directly.

I have noticed however that I can export tables in access project using the docmd.transferspreadsheet method.

So, is it feasible to modify my sql to generate a table each time through the loop (I can just delete the table after export). Or is there a simpler way that I'm missing.

Any help you can give me would be greatly appreciated

Dark

This should point you in the right direction. The following code will Export the Stored Procedure named procTestExport, in an Access Project, to Microsoft Excel Format, specifically to Test Stored Procedure.xls in the C:\Test\ Directory.

Dim strOutputPath As String
 
strOutputPath = "C:\Test\Test Stored Procedure.xls"
 
DoCmd.OutputTo acStoredProcedure, "procTestExport", "MicrosoftExcel(*.xls)", strOutputPath, False, ""

P.S. - It will not AutoStart Excel in the Export Process (the False Argument)

[Darkside12]

Cheers Guys,

Figured there was a simple way of doing it. You've just saved one of my friends from having to run the same query 7903 times lol.

Thanks again,

Dark

[ADezii]

Cheers Guys,

Figured there was a simple way of doing it. You've just saved one of my friends from having to run the same query 7903 times lol.

Thanks again,

Dark

You are quite welcome.

[NeoPa]

Probably should have explained the problem more thoroughly.

Considering your later replies are so much clearer I won't labour the point, but yes. Your first post wasn't the most clear ;)

@Stewart, A Stored Procedure is a SQL Server term referring to a piece of stored (and pre-optimised) T-SQL code (can include variables etc) which returns a CURSOR or recordset. A bit like a SQL Server VIEW.

Linking an Access database to a SQL Server Stored Procedure treats it as a linked table.

[Stewart Ross]

Thanks ADezii and NeoPa for the assistance, and apologies Dark if my posts were less of assistance than they should have been.

Gesh, the term 'stored procedure' confuses me anyway. I would never have guessed it was an SQL procedure package used by SQL Server, but then I don't develop client-server apps in SQL-Server. I live and learn.

Cheers

Stewart

[ADezii]

Thanks ADezii and NeoPa for the assistance, and apologies Dark if my posts were less of assistance than they should have been.

Gesh, the term 'stored procedure' confuses me anyway. I would never have guessed it was an SQL procedure package used by SQL Server, but then I don't develop client-server apps in SQL-Server. I live and learn.

Cheers

Stewart

You know the way it goes, Stewart. I watch your back - you watch mine - and we both watch NeoPa's. (LOL).

[NeoPa]

@tyrdrannoy
This is not related to the original question of the thread so must be posted as a separate thread (http://bytes.com/topic/access/answer...r-entered-data). I will move it for you and create the links so that you can find it again.