By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Exporting s Stored Procedure to Excel

P: 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?
Sep 24 '08 #1
Share this Question
Share on Google+
10 Replies


Expert Mod 2.5K+
P: 2,545
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
Sep 24 '08 #2

P: 13
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
Sep 24 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Sep 24 '08 #4

ADezii
Expert 5K+
P: 8,627
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strOutputPath As String
  2.  
  3. strOutputPath = "C:\Test\Test Stored Procedure.xls"
  4.  
  5. DoCmd.OutputTo acStoredProcedure, "procTestExport", "MicrosoftExcel(*.xls)", strOutputPath, False, ""
P.S. - It will not AutoStart Excel in the Export Process (the False Argument)
Sep 24 '08 #5

P: 13
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
Sep 24 '08 #6

ADezii
Expert 5K+
P: 8,627
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.
Sep 24 '08 #7

NeoPa
Expert Mod 15k+
P: 31,441
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.
Sep 25 '08 #8

Expert Mod 2.5K+
P: 2,545
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
Sep 25 '08 #9

ADezii
Expert 5K+
P: 8,627
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).
Sep 25 '08 #10

NeoPa
Expert Mod 15k+
P: 31,441
@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.
Jan 6 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.