将存储过程导出到Excel [英] Exporting s Stored Procedure to Excel

问题描述

一个简单的问题但是，是否可以使用docmd.TransferSpreadsheet函数将存储过程导出为ex​​cel？


如果没有，最简单的方法是什么？

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?

推荐答案

欢迎来到Bytes，Darkside。


无法使用TransferSpreadsheet方法传输Visual Basic for Applications过程。 Access VBA代码存储在数据库中的代码模块中，Excel中用户定义的VBA程序类似地存储在工作簿本身的代码模块中。


当使用TransferSpreadsheet将数据导出到Excel时，它会传输相关查询的结果，而不是基础计算方法。它相当于将值粘贴到Excel工作表中。

虽然可以在Access和Excel之间导出和导入完整的代码模块（而不是单独的程序），但这样做需要VBA编码。它比我目前在这里展示的更多参与。


如果任务是一次性的，那么从每个应用程序的VBA环境窗口手动执行此操作要容易得多（在Access中导出相关代码模块，在Excel中导入将模块放入工作簿中）。


进行这样的导入不需要很长时间，尽管可能需要调整或重写Access代码以适应Excel工作簿的不同环境 - 这可能是相当的这是一项耗时的任务，取决于代码需要更改的程度。


-Stewart

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

Hi Stewart，


可能应该更彻底地解释问题。


我有一个链接到SQL Server的Access Project数据库。我正在尝试创建一个循环，每次循环都会更改存储过程中的变量，然后将结果输出到excel。


创建代码以生成过程没有问题但是......将程序输出输出到excel证明更加困难。我尝试创建一个使用该过程作为数据源，然后输出表单，但得到的结果与尝试直接导出过程相同。


我注意到我可以导出使用docmd.transferspreadsheet方法访问项目中的表。


因此，每次通过循环修改我的sql以生成表是否可行（我可以在导出后删除表）。或者是否有一种我想念的简单方法。


任何你能给我的帮助都将不胜感激


Dark

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

Hi Dark。当然，在一个循环中你可以生成一个形式为SELECT ... INTO sometable的SQL字符串，然后使用Execute或RunSQL方法运行它来生成表。您可以在每次通过循环时在临时表上使用TransferSpreadsheet而不会出现问题，尽管您需要在每次情况下更改输出名称（或者您将在每次连续传递时覆盖最后一个输出）。


当你使用术语存储过程时，我仍然不清楚你指的是什么，因为它显然不是你所说的VBA代码;你的意思是一组操作（有点像Access宏）？或者你是一个SQL查询，你提供某种形式的参数？


-Stewart

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

这篇关于将存储过程导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！