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

bastian18

New Member
Joined
Aug 15, 2018
Messages
22
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::)

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. :confused:
How can I fix Error 91 in my code?
Any idea is welcome


Thanks
 
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".
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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?
 
Upvote 0
I raised that point in post 9 but there was no comment from OP on why he/she's taking this approach.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top