尝试使用 时出现方法错误.另存为VBA函数

Question

我正在尝试让 Access 在每次程序运行时通过程序导出我正在操作的表的副本。我在 myExcel.SaveAs 行上收到以下错误："运行时错误'438'：对象不支持此属性或方法">

我启用的参考库如下："Visual Basic for Applications"、"Microsoft Access 16.0 对象库"、"OLE 自动化"、"Microsoft Office 16.0 Access 数据库引擎对象库"、"Microsoft ActivceX 数据对象 6.1 库"、"Microsoft Excel 16.0 对象库"。

我尝试使用库以及前缀的各种格式。保存为方法无济于事。

Dim myrs As DAO.Recordset ' Create a recordset to hold the data
Dim myExcel As New Excel.Application ' Create Excel with Early binding
Dim mySheet As Excel.Worksheet

Set mySheet = myExcel.Workbooks.Add(1).Worksheets(1) ' Create Workbook
Set myrs = CurrentDb.OpenRecordset("SELECT * FROM tablename") ' Define recordset
Dim i As Integer
'Add header names
For i = 0 To myrs.Fields.Count - 1
mySheet.Cells(1, i + 1).Value = myrs.Fields(i).Name
Next
Dim fname As String
fname = "Detail_Export"
Dim timestamp As Date
timestamp = Now()
Dim strTime As String
Dim strTime2 As String
timestamp = CStr(timestamp)
strTime = Replace([timestamp], ":", ".")
strTime2 = Replace([strTime], "/", ".")
fname = fname & " " & strTime2
'Add data to excel and make Excel visible
mySheet.Range("A2").CopyFromRecordset myrs
myExcel.Visible = False
myExcel.SaveAs FileName:=("C:UsermeDatabase Backups" & fname), FileFormat:=xlOpenXMLWorkbook
myrs.Close

Answer 1

myExcel是 ExcelApplication对象。SaveAs是Workbook对象的方法：

改变

Set mySheet = myExcel.Workbooks.Add(1).Worksheets(1) ' Create Workbook

自

Dim myWb as Excel.Workbook
Set myWb = myExcel.Workbooks.Add
Set mySheet = myWb.Worksheets(1)

现在您可以在Workbook上SaveAs。