I've copied this code that I found which sort of does what I want to 2 diary workbooks, 2016 and 2017. I want to copy the file, rename it as a backup adding the date and time and saving it to a backup folder every 30 minutes but only keep the 5 latest backups. If I have one of the relevant workbooks open, I think it opens the other one as well and runs the code, which I don't want to happen. Can someone please confirm the code is causing this and maybe let me know what I should amend to not run the code if the workbook is not open. I have the Microsoft Scripting Runtime reference checked. And I'm now running Windows 10 with Office 16
In This Workbook I have:
and in a module I have:
In This Workbook I have:
Code:
Sub workbook_open()
dTime = Time + TimeValue("00:01:00")
Application.OnTime dTime, "AutoSaveAs"
End Sub
and in a module I have:
Code:
Public dTime As Date
Sub AutoSaveAs()
Dim fso As New FileSystemObject
Dim fil As File
Dim oldfile As File
Dim BackUpPath As String 'This is the FOLDER where your backups are stored
dTime = Time + TimeValue("00:01:00")
With Application
.OnTime dTime, "AutoSaveAs"
.EnableEvents = False
.DisplayAlerts = False
ThisWorkbook.SaveCopyAs "C:/blinds/30 minute diary backups/diary backup" & _
Format(Now, "dd.mm.yyyy.hh.mm") & ".xls"
.EnableEvents = True
Do Until fso.GetFolder("C:/blinds/30 minute diary backups").Files.Count < 6
For Each fil In fso.GetFolder("C:/blinds/30 minute diary backups").Files
'Checks to see if this file is older than the oldest file thus far
If oldfile Is Nothing Then Set oldfile = fil
If oldfile.DateLastModified > fil.DateLastModified Then Set oldfile = fil
Next fil
fso.DeleteFile oldfile, True
Set oldfile = Nothing
Loop
End With
End Sub