Auto Save after each loop but bypass "file already exists, do you want to save anyway?"

Bret1

Board Regular
Joined
Jun 14, 2013
Messages
199
I'd like to auto-save a looping Macro at end of each loop I run each day. I only need the latest "save", so I want it to save over the previous saves for that day. I found the simple code method below that looks like it will work, but I need help tweeking it some....
I need it to allow it to save over the previous save without the pop-up asking for permission to copy over existing file.
I need it to save as "Auto Save" & Today's date.... Such as, "Auto Save 10-18-22"
I would like to designate the name of the folder it saves to (inside the Macro only).... Currently, it saves to my general Documents folder

Here is the code I found...
Code:
Dim newName As String
    newName = "Auto Save"
    ActiveWorkbook.SaveAs Filename:=newName
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
newName = "Auto Save"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=newName
Application.DisplayAlerts = True

but why bother with a string variable if it's that simple? Why not just
ActiveWorkbook.SaveAs "Auto Save"

Doesn't anybody Google anymore?
 
Upvote 0
I found and tweeked this that works pretty well. Is there a way I can make it copy over the previous save that has the same date? This Macro loops every 5 minutes throughout the day and this is only for back up purposes. The current code below adds a new file for each new loop. At the end of the day, I only need one back up that shows the last back up time, but know that it backed up after each loop.

Code:
      Application.ScreenUpdating = False
Application.DisplayAlerts = False
        With ActiveWorkbook
            .SaveAs "C:\Users\.............." & "\" & Format(Now, "mm-dd-yyyy hh-mm") & " "
            '.Close
        End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
Upvote 0
Maybe try Name statement instead of ActiveWorkbook.SaveAs
You're complicating this by adding time value in the name - you can't rename a file if you don't know its full path. Unless you either store the last filename in a module level variable I don't know how else you'd know that. The 1st time it runs, the variable would have no value, so you'd have to work around that as well.
If variable = "" Then
<< simply save without prompt
Else Name variable As NewFileNameGoesHere

This might give you a start
 
Upvote 0
Maybe try Name statement instead of ActiveWorkbook.SaveAs
You're complicating this by adding time value in the name - you can't rename a file if you don't know its full path. Unless you either store the last filename in a module level variable I don't know how else you'd know that. The 1st time it runs, the variable would have no value, so you'd have to work around that as well.
If variable = "" Then
<< simply save without prompt
Else Name variable As NewFileNameGoesHere

This might give you a start
 
Upvote 0
Thanks for your help. I think this one will work fine. As long as I know it's being backed up after each loop, I don't need it to save a time stamp. Just the date is enough. I can view when it was last saved.
Thanks!

Code:
    Application.ScreenUpdating = False
Application.DisplayAlerts = False
        With ActiveWorkbook
            .SaveAs "C:\Users\............" & "\" & " IntraDay Updates" & Format(Now, "mm-dd-yyyy") & " Backed Up Every 5 Minutes" & " "
            '.Close
        End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
 
Upvote 0
I don't need it to save a time stamp.
Then you might as well just use Date as opposed to using Now then formatting it as a date?
"IntraDay Updates" & Date() or" IntraDay Updates" & Date
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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