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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
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

New Member
Joined
Aug 15, 2018
Messages
22
Thanks for your reply.
I delete: 'Dim xlApp As Excel.Application
Hello
I wrote below set ws:
Code:
Set ws = wb.Sheets("qry_records_student")Set xl = Nothing
I also use:
Code:
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::confused:
Code:
xl.DisplayAlerts = False
With xl. Intellisense is not working.:confused:
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
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?
Code:
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

New Member
Joined
Aug 15, 2018
Messages
22
I apologize for omitting the full code in the previous post.

Code:
[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:
Code:
l.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False
Now VBA shows font in red
 

bastian18

New Member
Joined
Aug 15, 2018
Messages
22
My last line was incomplete,. My copy and paste was incomplete:
Code:
xl.DisplayAlerts = Falsexl.wb.SaveAs xl.wb.FullName, CreateBackup:=False
Still, VBA adds red colour to line code.

Cheers
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
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.
 
Last edited:

bastian18

New Member
Joined
Aug 15, 2018
Messages
22
Again, thank you for your recommendations.

Your right this lines must be:
Code:
Option Compare Database
Option Explicit
With respect to Fullname, this word was at the end of in my first code:
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:
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
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)
Code:
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.
 
Last edited:

bastian18

New Member
Joined
Aug 15, 2018
Messages
22
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... "
Code:
[/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.
 

Forum statistics

Threads
1,082,132
Messages
5,363,340
Members
400,728
Latest member
Hoan1985

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top