Exporting s Stored Procedure to Excel | Newbie | | Join Date: Jan 2008
Posts: 13
| | |
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?
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Exporting s Stored Procedure to Excel
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
| | Newbie | | Join Date: Jan 2008
Posts: 13
| | | re: Exporting s Stored Procedure to Excel
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
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Exporting s Stored Procedure to Excel
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,223
| | | re: Exporting s Stored Procedure to Excel
Quote:
Originally Posted by 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 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)
| | Newbie | | Join Date: Jan 2008
Posts: 13
| | | re: Exporting s Stored Procedure to Excel
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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,223
| | | re: Exporting s Stored Procedure to Excel
Quote:
Originally Posted by 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 You are quite welcome.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,741
| | | re: Exporting s Stored Procedure to Excel
Quote:
Originally Posted by Darkside12 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.
| | Moderator | | Join Date: Feb 2008 Location: Beauly, near Inverness, Scotland
Posts: 1,576
| | | re: Exporting s Stored Procedure to Excel
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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,223
| | | re: Exporting s Stored Procedure to Excel
Quote:
Originally Posted by Stewart Ross Inverness 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).
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,741
| | | re: Exporting s Stored Procedure to Excel
Quote:
Originally Posted by tyrdrannoy  is there a way to get this docmd.outputto command to pass the parameters through code, instead of having to type them in? My end users are not that SQL savvy. I would rather pass the stored procedure parameters that they have already entered into text boxes on the access form. This is not related to the original question of the thread so must be posted as a separate thread ( Using References to User Entered Data). I will move it for you and create the links so that you can find it again.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,510 network members.
|
