vba 将数据从 Access 写入 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/280729/
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
Write data from Access to Excel file
提问by tksy
I am trying to use the following code to write data into an excel file
我正在尝试使用以下代码将数据写入 excel 文件
Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Set objexcel = CreateObject("excel.Application")
objexcel.Visible = True
On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Documents.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.xls")
wbExists = True
Openwb:
On Error GoTo 0
If Not wbExists Then
Set wbexcel = objexcel.Workbook.Add
End If
but I'm getting an
但我得到了
runtime error object doesn't support property or method
运行时错误对象不支持属性或方法
in the line
在行中
Set wbexcel = objexcel.Workbook.Add
I have referenced the Excel object library.
我已经引用了 Excel 对象库。
回答by Fionnuala
You will need to change this line:
您将需要更改此行：
Set wbexcel = objexcel.WorkBooks.Open( _
"C:\Documents and Settings\TAYYAPP\Desktop\test folder\ERROR REPORT2.xls")
Note WorkBooks, not Documents
注意工作簿，而不是文档
As For this line Set wbexcel = objexcel.Workbook.Add, wbexcel is defined as a workbook, but the line is an action, so:
至于这一行 Set wbexcel = objexcel.Workbook.Add，wbexcel 被定义为一个工作簿，但该行是一个动作，所以：
objexcel.Workbooks.Add
Set wbexcel = objexcel.ActiveWorkbook
EDIT: As an aside, DoCmd.Transferspreadsheet is probably the easiest way of transferring a set of data (query, table) from Access to Excel.
编辑：顺便说一句，DoCmd.Transferspreadsheet 可能是将一组数据（查询、表）从 Access 传输到 Excel 的最简单方法。
回答by tksy
I have got this code which works fine
我有这个工作正常的代码
Dim objexcel As Excel.Application
Dim wbexcel As Excel.Workbook
Dim wbExists As Boolean
Dim objSht As Excel.Worksheet
Dim objRange As Excel.Range
Set objexcel = CreateObject("excel.Application")
objexcel.Visible = True
On Error GoTo Openwb
wbExists = False
Set wbexcel = objexcel.Workbooks.Open("C:\Documents and Settings\TAYYAPP\Desktop\test folder\reports\ERROR REPORT2.xls")
Set objSht = wbexcel.Worksheets("Sheet1")
objSht.Activate
wbExists = True
Openwb:
On Error GoTo 0
If Not wbExists Then
objexcel.Workbooks.Add
Set wbexcel = objexcel.ActiveWorkbook
Set objSht = wbexcel.Worksheets("Sheet1")
End If
but I want to add one more check that if the file exists then I want to see if its is populated with values and if so then I want the next set of values to be populated from the end. As of now it is overwriting the existing values
但我想再添加一个检查，如果文件存在，那么我想看看它是否填充了值，如果是，那么我希望从最后填充下一组值。截至目前，它正在覆盖现有值
