Getting method error when trying to use .SaveAs VBA function

Question

I am trying to get Access to export a copy of the table I am actioning on via the program each time the program runs. I am getting the following error on the myExcel.SaveAs line: "Run-time error '438': Object doesn't support this property or method"

The reference libraries I have enabled are as follows: "Visual Basic For Applications", "Microsoft Access 16.0 Object Library", "OLE Automation", "Microsoft Office 16.0 Access database engine Object Library", "Microsoft ActivceX Data Objects 6.1 Library", "Microsoft Excel 16.0 Object Library".

I've tried playing with the libraries as well as various formats for the prefixes befor the .SaveAs method to no avail.

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:\User\me\Database Backups\" & fname), FileFormat:=xlOpenXMLWorkbook
myrs.Close

Answer 1

myExcel is the Excel Application object. SaveAs is a method of the Workbook object:

Change

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

to

Dim myWb as Excel.Workbook
Set myWb = myExcel.Workbooks.Add

Set mySheet = myWb.Worksheets(1)

Now you can SaveAs on the Workbook.