Hello
In my code to export a query to Excel, I am trying to adapt the code from Titleist23 https://www.mrexcel.com/forum/excel-questions/584129-disable-access-excel-backup-file-vba.html to save and close the workbook and stops making a backup file.
This is my code working fine to export a query to excel:
The last 4 lines of code worked as recommended by Titleist23 in his post, but is not working for me.
How can I fix Error 91 in my code?
Any idea is welcome
Thanks
In my code to export a query to Excel, I am trying to adapt the code from Titleist23 https://www.mrexcel.com/forum/excel-questions/584129-disable-access-excel-backup-file-vba.html to save and close the workbook and stops making a backup file.
This is my code working fine to export a query to excel:

Code:
Option Compare Database
Option Explicit
Private Sub cmbsearch_Click()
Dim myDir As String
Dim FileName As String
Dim wb As Object
Dim xl As Object
Dim sExcelWB As String
Dim ws As Worksheet
Dim xlApp As Excel.Application
Set xl = CreateObject("excel.application")
sExcelWB = CurrentProject.Path & "qry_recds.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_recds.xlsx", sExcelWB, True
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_recds.xlsx")
xl.Visible = True
xl.UserControl = True
'lines o code to save and close the workbook and stops making a backup file
xlApp.DisplayAlerts = False ''Here vba displays Error '91' Object variable or With block variable not set
xlApp.wb.SaveAs xlApp.wb.FullName, CreateBackup:=False
xlApp.wb.Close SaveChanges:=True
xlApp.DisplayAlerts = True
End Sub
The last 4 lines of code worked as recommended by Titleist23 in his post, but is not working for me.

How can I fix Error 91 in my code?
Any idea is welcome
Thanks