通过VBA将MS Access表导出到同一目录中的excel电子表格 [英] Export MS Access tables through VBA to an excel spreadsheet in same directory

问题描述

我的访问数据库中有两个表，我希望能够导出到excel 。

我可以通过打开表格，然后执行File-> Export ...然后选择格式并输入文件名来完成。但是，这样用户实际上必须键入名称，所以有空间误导文件或将其保存为错误的格式或错误的位置。另外，这两个表必须导出到两个单独的工作簿。

我想要做的是在一个窗体上设置一个按钮，自动将一个表导出到一个工作表，另一个用于另一个工作表，都在相同的excel工作簿中。

如果将它们放在同一个工作簿中是不可能的，那没关系。我只是希望它们自动导出到我的访问数据库被保存的同一个目录。

如果你知道如何做，添加的perk可能是自定义名称包括日期。这样，目录也将具有历史出口。任何建议？

解决方案

您可以使用VBA将Access数据库表作为工作表导出到Excel工作簿中。

要获取Access数据库的路径，请使用 CurrentProject.Path 属性。

要使用当前日期命名Excel Workbook文件，请使用方法。

最后，要将表导出为工作表，请使用 DoCmd.TransferSpreadsheet 方法。

示例：

  Dim outputFileName As String 
 outputFileName = CurrentProject.Path& \Export_&格式（日期，yyyyMMdd）& .xls
 DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel9，Table1，outputFileName，True 
 DoCmd.TransferSpreadsheet acExport，acSpreadsheetTypeExcel9，Table2，outputFileName，True 
  

这将把Table1和Table2都输出到相同的工作簿中。

HTH

I have two tables in my access database that I want to be able to export to excel.

I can do it by opening the table and then doing File->Export... and then choosing the format and typing in the file name.

However, this way the user actually has to type the name in so there is room for misnaming the file or for saving it as the wrong format or in the wrong location. Also, the two tables have to be exported to two separate workbooks.

What I want to be able to do is make a button on a form that automatically exports one table to one worksheet and the other to another worksheet, both in the same excel workbook.

If putting them in the same workbook isn't possible, that's fine. I just want them to automatically be exported to the same directory my access database is saved in.

If you know how to do it, an added perk might be to customize the name to include the date. That way the directory would have historical exports as well. Any advice?

解决方案

You can use VBA to export an Access database table as a Worksheet in an Excel Workbook.

To obtain the path of the Access database, use the CurrentProject.Path property.

To name the Excel Workbook file with the current date, use the Format(Date, "yyyyMMdd") method.

Finally, to export the table as a Worksheet, use the DoCmd.TransferSpreadsheet method.

Example:

Dim outputFileName As String
outputFileName = CurrentProject.Path & "\Export_" & Format(Date, "yyyyMMdd") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", outputFileName , True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table2", outputFileName , True

This will output both Table1 and Table2 into the same Workbook.

HTH

这篇关于通过VBA将MS Access表导出到同一目录中的excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！