I have two tables in my access database that I want to be able to export to excel.
我的access数据库中有两个表，我希望能够导出到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.
我可以打开表，然后执行File->Export…然后选择格式并输入文件名。
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.
我想要做的是在窗体上创建一个按钮，该按钮自动将一个表导出到一个工作表，另一个工作表导出到另一个工作表，这两个工作表都位于同一个excel工作簿中。
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.
如果把它们放在同一个工作簿里是不可能的，那就好了。我只想让它们自动导出到我的access数据库保存的目录中。
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?
如果你知道怎么做，额外的好处是可以自定义名称来包含日期。这样，目录也将具有历史导出。任何建议吗?
3 个解决方案
#1
33
You can use VBA to export an Access database table as a Worksheet in an Excel Workbook.
您可以使用VBA将访问数据库表作为Excel工作簿中的工作表导出。
To obtain the path of the Access database, use the CurrentProject.Path property.
要获取访问数据库的路径，请使用CurrentProject。路径属性。
To name the Excel Workbook file with the current date, use the Format(Date, "yyyyMMdd") method.
要将Excel工作簿文件命名为当前日期，请使用Format(date，“yyyyyyymmdd”)方法。
Finally, to export the table as a Worksheet, use the DoCmd.TransferSpreadsheet method.
最后，要将表导出为工作表，请使用DoCmd。TransferSpreadsheet方法。
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.
这将把Table1和Table2都输出到同一个工作簿中。
HTH
HTH
#2
7
Lawrence has given you a good answer. But if you want more control over what gets exported to where in Excel see Modules: Sample Excel Automation - cell by cell which is slow and Modules: Transferring Records to Excel with Automation You can do things such as export the recordset starting in row 2 and insert custom text in row 1. As well as any custom formatting required.
劳伦斯给了你一个很好的答案。但是如果你想要更多的控制出口在Excel中看到什么模块:样本Excel自动化-细胞通过细胞缓慢和模块:记录转移到Excel导出等自动化可以做记录集从第二行开始,在第一行插入自定义的文本。以及所需的任何自定义格式。
#3
-4
For people who find this via search engines, you do not need VBA. You can just:
对于通过搜索引擎找到这一点的人来说，不需要VBA。你可以:
1.) select the query or table with your mouse
2.) click export data from the ribbon
3.) click excel from the export subgroup
4.) follow the wizard to select the output file and location.
#1
33
You can use VBA to export an Access database table as a Worksheet in an Excel Workbook.
您可以使用VBA将访问数据库表作为Excel工作簿中的工作表导出。
To obtain the path of the Access database, use the CurrentProject.Path property.
要获取访问数据库的路径，请使用CurrentProject。路径属性。
To name the Excel Workbook file with the current date, use the Format(Date, "yyyyMMdd") method.
要将Excel工作簿文件命名为当前日期，请使用Format(date，“yyyyyyymmdd”)方法。
Finally, to export the table as a Worksheet, use the DoCmd.TransferSpreadsheet method.
最后，要将表导出为工作表，请使用DoCmd。TransferSpreadsheet方法。
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.
这将把Table1和Table2都输出到同一个工作簿中。
HTH
HTH
#2
7
Lawrence has given you a good answer. But if you want more control over what gets exported to where in Excel see Modules: Sample Excel Automation - cell by cell which is slow and Modules: Transferring Records to Excel with Automation You can do things such as export the recordset starting in row 2 and insert custom text in row 1. As well as any custom formatting required.
劳伦斯给了你一个很好的答案。但是如果你想要更多的控制出口在Excel中看到什么模块:样本Excel自动化-细胞通过细胞缓慢和模块:记录转移到Excel导出等自动化可以做记录集从第二行开始,在第一行插入自定义的文本。以及所需的任何自定义格式。
#3
-4
For people who find this via search engines, you do not need VBA. You can just:
对于通过搜索引擎找到这一点的人来说，不需要VBA。你可以:
1.) select the query or table with your mouse
2.) click export data from the ribbon
3.) click excel from the export subgroup
4.) follow the wizard to select the output file and location.