SaveAs Macro still asks me on exit to save file

[JohnZ1156]

I have an Excel Workbook with many sheets. After I enter my new data, I created a button and assigned a macro to save the file to multiple locations. Two "backup" locations and my primary location.
Even though I run the macro and it saves the file, when I go to close the workbook or exit excel, it asks me if I want to save the file again?

Here is my macro.

Sub SaveAllOver()

    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs "E:\Finance\Finances.xlsm", 52
    ThisWorkbook.SaveAs "Z:\Finance\Finances.xlsm", 52

    'ThisWorkbook.Save
    MsgBox ("The " & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & " Workbook was saved!")
    ThisWorkbook.SaveAs "C:\Users\John\Documents\Finance\Finances.xlsm", 52
    Application.DisplayAlerts = True
        
End Sub

 

[cmowla]

Try and omit the ", 52" from the lines of code, as that tells to save as ".xlsm", which you already specified. XlFileFormat enumeration (Excel)

 

[JohnZ1156]

Try and omit the ", 52" from the lines of code, as that tells to save as ".xlsm", which you already specified. XlFileFormat enumeration (Excel)

Thanks fo rgetting back to me.

I deleted the ", 52" from each line as you suggested.
After running the macro and the file gets saved, Excel still asks me if I want to save the file when I go to exit Excel.

I don't understand why it's asking me that since the file was JUST saved?

 

[cmowla]

I honestly don't know, as it doesn't prompt me to save after closing (and I plugged in a USB drive into my computer to save the file there and on my computer's harddrive before I replied above). But, I did notice that if I turn alerts back on, it mentions that the file exists and if I want to override. This could be the issue. (In theory, "muting the alert doesn't make the problem go away".) So if you want to force it to override (which is technically deleting the existing file and then saving the new one), here's a way. This could fix the problem, but if this doesn't work, I don't know!

(This code is safe, but please make a copy of the file just in case! I commented out the DisplayAlerts, as I doubt you don't need them with this code.)

Sub SaveAllOver()

    'Application.DisplayAlerts = False
    Custom_SaveOverride ("E:\Finance\Finances.xlsm")
    Custom_SaveOverride ("Z:\Finance\Finances.xlsm")

    'ThisWorkbook.Save
    MsgBox ("The " & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5) & " Workbook was saved!")
    Custom_SaveOverride ("C:\Users\John\Documents\Finance\Finances.xlsm")
    'Application.DisplayAlerts = True
        
End Sub


Sub Custom_SaveOverride(filePath As String)
If File_Path_Exists(filePath) Then DeleteFile (filePath)
ThisWorkbook.SaveAs filePath
End Sub
Sub Test__File_Path_Exists()
MsgBox File_Path_Exists("C:\Users\Chris\Desktop\2BcaV5nu.jpg")
End Sub
Function File_Path_Exists(filePath As String)
'Note:  For folder addresses/paths, append & "\" to the file path!!!
File_Path_Exists = Not Dir(filePath) = ""
End Function
Sub Test__DeleteFile()
Call DeleteFile("C:\Users\cmowla\Desktop\Prgm Logs\test.txt")
End Sub
Sub DeleteFile(fileToDelete As String)
'https://stackoverflow.com/a/67860
   If File_Path_Exists(fileToDelete) Then
      ' First remove readonly attribute, if set
      SetAttr fileToDelete, vbNormal
      ' Then delete the file
      Kill fileToDelete
   End If
End Sub

 

[MARK858]

Try and omit the ", 52" from the lines of code, as that tells to save as ".xlsm", which you already specified. XlFileFormat enumeration (Excel)

You need the 52 if you are saving as a different fileformat, the .xlsm isn't enough

 

[cmowla]

In response to @MARK858 , if it turns out my code works, then you need to append back the ", 52" in the following sub as follows:

Sub Custom_SaveOverride(filePath As String)
If File_Path_Exists(filePath) Then DeleteFile (filePath)
ThisWorkbook.SaveAs filePath, 52
End Sub

But the ", 52" wasn't necessary to save the file in this case, because you're continuing to save a file that was already ".xlsm" file extension. But, it's always better to keep it general. (Sorry! I learned something new today.)

 

[GWteB]

Try this. Note the red colored line, although it shouldn't be necessary.
In case you're still getting the "save workbook?" question then an (event handler) macro must have been executed afterwards which made a change to your workbook.

Sub SaveAllOver()

    Application.DisplayAlerts = False
    With ThisWorkbook
        .SaveAs "E:\Finance\Finances.xlsm", 52
        .SaveAs "Z:\Finance\Finances.xlsm", 52

        MsgBox ("The " & Left(.Name, Len(.Name) - 5) & " Workbook was saved!")
        .SaveAs "C:\Users\John\Documents\Finance\Finances.xlsm", 52
        .Saved = True
    End With
    Application.DisplayAlerts = True
End Sub

 

[MARK858]

because you're continuing to save a file that was already ".xlsm" file extension

Where in the code or the question does it tell you this? How do you know if the file was originally xls, xlsm, xlsb or xltm?

It most likely is xlsm, but this should never be assumed when using SaveAs (for example my computer is set up to default to xlsb when you create a new file).
I'm being a bit pedantic but we do get questions where the issue is only that OP's haven't stated the fileformat so it is always better to include it in any code using saveas.

Anyway this post is diverting away from the main question a bit. @JohnZ1156 please don't miss post number 7 by GWteB.

 

[cmowla]

Where in the code or the question does it tell you this? How do you know if the file was originally xls, xlsm, xlsb or xltm?

He explicitly stated that the suggestion to remove the , 52 successfully saved the file. That's how I know. And he already saved it once with the , 52. He was just having problems with continuously getting a prompt to save when closing the Workbook.

After running the macro and the file gets saved, Excel still asks me if I want to save the file when I go to exit Excel.

And it's very hard to believe that someone who has already written code (and is having trouble with that code) in a code module hasn't already saved the file as .xlsm or .xlsb. That they just straight up opened up a .xls or .xlsx, put the code in to save it. Because naturally Excel will give you a warning to save or not save as a macro-free Workbook!

. . . But let's just forget that assumption/logic (and what I wrote at the beginning of this post) of mine in this case because he also said (in the original post):

Even though I run the macro and it saves the file, when I go to close the workbook or exit excel, it asks me if I want to save the file again?

If it saved the file, that means the currently opened Workbook is saved, is it not? And if it's saved, that means it has to be saved as a macro-enabled workbook. (If he would be continuously be getting 3 "save as macro-enabled workbook" notifications (because he was saving the file in 3 different locations), don't you think he would have told us?)

Most people don't know of .xlsb, he had ".xlsm" in his code, . . .

 

[MARK858]

hasn't already saved the file as .xlsm or .xlsb

If he had originally saved it as xlsb then he would need to use the 52 as it is a change in fileformat

He explicitly stated that the suggestion to remove the , 52 successfully saved the file. That's how I know.

That doesn't mean it originally started as xlsm, he had already saved it using 52 in the original code. What the file started as you don't know (or if he is going to be running it from scratch in the future once the coding is sorted out).

Anyway this post is diverting away from the main question a bit. @JohnZ1156 please don't miss post number 7 by GWteB.