如何在按下按钮时运行查询并将结果导出到 Excel 文件 [英] How to run a query when a button is pressed and export results to Excel File

问题描述

我有一个包含组合框和按钮的表单.当在其中选择一个值时，我必须检索在组合框中选择的值并基于该值运行查询.该查询生成一个包含 5 列的表.我希望将此表导出到 Excel 工作表.请帮帮我.

I have a form which has a combobox and a button. When a value is selected in it, I have to retrieve the value selected in combo box and based on that value run a query. The query produces a table of 5 columns. I want this table to be exported to Excel Sheet. Please help me out.

推荐答案

正如 Tom 所说，您可以使用 Docmd.TransferSpreadsheet 命令.首先，您需要将查询设置为引用组合框的参数化查询.我假设它是您的 WHERE 子句的一部分.因此，您的查询可能如下所示:

As Tom said, you can use the Docmd.TransferSpreadsheet command. First you need to have your query set to be a parameterized query that references your combobox. I'm assuming it's part of your WHERE clause. So, your query may look like:

 SELECT * FROM yourTable WHERE values = [Forms]![yourForm]![yourComboBox];

假设您刚刚编写的查询名为qryExport"，您将将此代码放入表单上的 on click 事件按钮中:

Assuming your query you just wrote is named "qryExport", You'll put this code into a button on your form on the on click event:

 DoCmd.TransferSpreadsheet acExport, , "qryExport", "C:\yourPath\exportedReport.xlsm", True

然后您应该能够进入您指定的路径并打开该 Excel 文件并确保一切正常.

You should then be able to go in the path you specified and open that excel file and make sure everything worked.

希望这会有所帮助.

这篇关于如何在按下按钮时运行查询并将结果导出到 Excel 文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！