Error 91 when save and close workbook and stops making a backup file.

[bastian18]

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:

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

 

[Micron]

Ok, I went back and took another look at the link. FullName is just an alias for "your full path and file name goes here".

 

[xenou]

Just to go back to the beginning, the code is confusing.
You export to a workbook. Then open it and save it and close it. Why do the last three things at all?

 

[Micron]

I raised that point in post 9 but there was no comment from OP on why he/she's taking this approach.

 

[bastian18]

Micron and xenou.
I already edited the code, and there is not more back up.

Thanks for your help.