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]

You seem to have dim'd 2 variables for the app object; xl and xlApp. You SET xl but you try to use xlApp.
The clue is in the message "Object variable...not set."

In addition, your code has no error handling or cleanup. For cleanup, you should destroy every object you set:
Set xl = Nothing
etc.

 

[bastian18]

Thanks for your reply.
I delete: 'Dim xlApp As Excel.Application
Hello
I wrote below set ws:

Set ws = wb.Sheets("qry_records_student")Set xl = Nothing

I also use:

xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False
xl.wb.Close SaveChanges:=True
xl.DisplayAlerts = True

But VBA againg displays Error 91 in this line:

xl.DisplayAlerts = False

With xl. Intellisense is not working.

 

[Micron]

Watch the order of code lines. You're trying to set Display alerts property for the application object after you close it. Intellisense cannot work for user defined objects unless they are created with a class module, which is an advanced topic. If this answer isn't the solution, post the whole code. It's too hard to scroll up and down on a screen to make sense of the original part with the new parts. I'm also assuming you inadvertently posted 2 procedure lines on the same line in your post?

xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

I also do not see where you declared (Dim'd) FullName?? I think you're writing code then running it without compiling it first. You should write, save, compile, then run if it compiles without errors.

 

[bastian18]

I apologize for omitting the full code in the previous post.

[Option Compare DatabaseOption Explicit
Private Sub cmbsearch_Click()


Dim myDir As String
Dim FileName As String
Dim Fullname As String
Dim wb As Object
Dim xl As Object
Dim sExcelWB As String
Dim ws As Worksheet


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")


'lines o code to save and close the workbook and stops making a backup file


xl.wb.SaveAs xl.wb.FullName, CreateBackup:=False
xl.wb.Close SaveChanges:=True
xl.DisplayAlerts = True


End Sub/CODE]

If I use:
[CODE]xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

'Now VBA shows font in red

If I use:

l.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

Now VBA shows font in red

 

[bastian18]

My last line was incomplete,. My copy and paste was incomplete:

xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

Still, VBA adds red colour to line code.

Cheers

 

[Micron]

Watch out for lines that don't get broken up when posting code because it can confuse the issue. For instance, you have
Option Compare DatabaseOption Explicit which must be a pasting mistake. For the xl.DisplayAlerts line, not sure if that's the case also:

There are at least 2 separate operations here that you have jammed together
xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

xl.DisplayAlerts = False
xl.wb.SaveAs xl.wb.FullName, CreateBackup:=False

I don't see where you assigned a value to FullName. If not, it still won't work as FullName will be Null. Or you have assigned a value but are not showing it, in which case I'm not sure because I've never tried to use the application object variable in any SaveAs operation for Excel. I would only use the workbook (wb) variable, but you will probably be OK with what you have.

 

[bastian18]

Again, thank you for your recommendations.

Your right this lines must be:

Option Compare Database
Option Explicit

With respect to Fullname, this word was at the end of in my first code:

'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

I said that it was reported by Titleist2 in his post.

I think by the moment, my original code works, being the problem that I don't know how to stop making a backup file.

My code:

[/COLOR]Option Compare DatabaseOption 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


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


End Sub[COLOR=#333333]

Cheers

 

[Micron]

Saving a backup used to be an option setting, depending on your version. Regardless, thought you should be able to override this with CreateBackup:=False
You did use it, but you keep altering your code so much that one version doesn't look like the previous. When you had that in there, you had it jammed up on one line with what should have been separate code lines, but you never fixed it. Now you don't have the save line at all??

If you can't stop the backup with properly written code, then check to see if your version has that option. I believe it was there for Excel 2007. Otherwise, post the complete code you want to use.

Plus, you are missing my point about FullName. I said you declare it, but you never assign a value to it.
Your code should probably look more like (sticking with late binding as you have)

Private Sub cmbsearch_Click()

Dim FileName As String, sExcelWB As String
Dim xlApp As Object
Dim wb As Object, ws As Object

sExcelWB = CurrentProject.Path & "qry_recds.xlsx" 'LIKELY A \ IS MISSING HERE
Set xlApp = CreateObject("excel.application")
Set wb = xlApp.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_recds.xlsx")

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_recds.xlsx", sExcelWB, True
With xlApp
  .Visible = True
  .UserControl = True 
  .DisplayAlerts = False
  .wb.SaveAs sExcelWB, CreateBackup:=False
  .wb.Close
  .DisplayAlerts = True
End With

Set xlApp = Nothing
Set wb = Nothing
Set ws = Nothing

End Sub

I cannot be sure that will work as I've made some assumptions. Note that I also mention that your file path concatenation is probably missing a slash. You also had 2 variables declared for the same thing (xl and xlApp) and another one besides FullName that you aren't using - MyDir.

You realize that unless you want someone to look at the workbook, there's no reason to do more than simply TransferSpreadsheet? I doubt anyone sees the file since your code shows it, saves it, then closes it right away.

 

[bastian18]

My Excel version is 2016.

I am going to test your code and I let you know. Thank you.

When writing a VBA code how can know the version?

Regarding Fullname, Titleist2 does not show if he or she declared it. I just only included in my post those lines of code:

In the message Titleist2 says:
"Hmmmmm,
I think you need to perform a .SaveAs to set the CreateBackup attribute... "

[/COLOR]'untestedXLApp.DisplayAlerts = False
XLApp.ActiveWorkbook.SaveAs XLApp.ActiveWorkbook.FullName, CreateBackup:=False
XLApp.ActiveWorkbook.Close SaveChanges:=True

XLApp.DisplayAlerts = True[COLOR=#333333]

Titleist2 reply: "Perfect!!! Thanks buddy"

I think that lines of code instead of helped me those lines of code instead of helping me confused me. Therefore, I had to use my first code, while looking a way to avoid CreateBackup.

In my MS Acces Form, I am using different values in the query textbox. Therefore, every query name is different.

Cheers.