vba docmd.TransferSpreadsheet Access --> Excel //// 指定目标工作表和范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21630581/
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
docmd.TransferSpreadsheet Access --> Excel //// specify destination worksheet AND range
提问by sion_corn
I have to write some Access VBA to export data from an Access query into a specific range of cells in an Excel document that has several worksheets.
我必须编写一些 Access VBA 才能将数据从 Access 查询导出到具有多个工作表的 Excel 文档中的特定单元格范围。
I am having trouble finding the right way to specify the worksheet AND range.
我无法找到指定工作表和范围的正确方法。
Here is what I have so far:
这是我到目前为止所拥有的：
docmd.TransferSpreadsheet(TransferType:=acExport, SpreadsheetType:=acSpreadsheetTypeExcel8, TableName:=qry_Main, _
FileName:="c:\test.xlsm", _
HasFieldNames:=False, _
Range:="Main!J9:J10")
The broken piece is Range:="Main!J9:J10"
破碎的部分是 Range:="Main!J9:J10"
What's the proper way to make this reference?
进行此参考的正确方法是什么？
回答by Fionnuala
You can use CopyFromRecordset and automation:
您可以使用 CopyFromRecordset 和自动化：
Sub XLTrans()
''Reference: Microsoft ActiveX Data Object x.x Library
Dim rs As New ADODB.Recordset
Dim xl As Object ''Excel.Application
Dim wb As Object ''Workbook
Set xl = CreateObject("Excel.Application")
''Pick one
''1. New book
Set wb = xl.Workbooks.Add
''2. Existing book
Set wb = xl.Workbooks.Open("z:\docs\book1.xlsx")
''Connection relevant for 2007 or 2010
rs.Open "MyTableOrQuery", CurrentProject.AccessConnection
wb.Sheets("Sheet1").Cells(4, 5).CopyFromRecordset rs
xl.Visible = True
End Sub
Note that this will not include column headings, but you can add them as well, for example:
请注意，这不会包括列标题，但您也可以添加它们，例如：
For i = 0 To rs.Fields.Count - 1
Worksheets("Sheet1").Cells(3, i + 5) = rs(i).Name
Next
回答by Alan Waage
http://msdn.microsoft.com/en-us/library/office/ff844793.aspxhttp://msdn.microsoft.com/en-us/library/office/aa141565(v=office.10).aspx
http://msdn.microsoft.com/en-us/library/office/ff844793.aspx http://msdn.microsoft.com/en-us/library/office/aa141565(v=office.10).aspx
You cannot use RANGE for exporting: " Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.
您不能使用 RANGE 进行导出：“ Range Optional Variant。一个字符串表达式，它是电子表格中有效的单元格范围或范围的名称。此参数仅适用于导入。将此参数留空以导入整个电子表格。当您导出到电子表格，您必须将此参数留空。如果您输入一个范围，导出将失败。
"
”
