vba 将数据从 Access 导出到现有的 Excel 电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25641752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting data from Access to an already existing Excel spreadsheet
提问by rob
How can I export a table from MS Access 2007 into an already existing excel spreadsheet? (Not a new spreadsheet)
如何将 MS Access 2007 中的表格导出到现有的 Excel 电子表格中？（不是新的电子表格）
The idea being that I have several macros set up on other excel sheets which will analyse the data and output graphs that I can't work out how to produce on Access.
我的想法是我在其他 Excel 工作表上设置了几个宏，这些宏将分析我无法在 Access 上生成的数据和输出图表。
I want to export table "Master" to:
我想将表“Master”导出到：
Excel file name: "Resource Planning"
Excel 文件名：“资源计划”
Sheet name: "Raw Data"
工作表名称：“原始数据”
Thanks in advance
提前致谢
回答by user2290801
You can create Name Ranges in Excel by going to the Formulas tab and providing a name and reference. Make sure the reference, for example: =Sheet1!$A$1; refers to the range where you want your data to be pasted. Try not to have the Excel file open while you run the subroutine. Then when you are exporting from Access you can use a vba module to run the following:
您可以通过转到“公式”选项卡并提供名称和引用来在 Excel 中创建名称范围。确保引用，例如：=Sheet1!$A$1; 指的是您希望粘贴数据的范围。运行子例程时尽量不要打开 Excel 文件。然后，当您从 Access 导出时，您可以使用 vba 模块运行以下命令：
Private Sub ExportMyQueryOtTableToExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
"sqYourQueryNameOrTable", "x:\ExcelFile.xls", -1, "NameRangeInExcelWorkBook"
End Sub
If you want to run this from a Module, just change the "Private Sub" part to "Public Function". That way you can call the function from the Macro with the RunCode action (Function name = ExportMyQueryOtTableToExcel()).
如果您想从模块运行它，只需将“私有子”部分更改为“公共功能”。这样您就可以使用 RunCode 操作（函数名称 = ExportMyQueryOtTableToExcel()）从宏调用函数。
