Originally Posted by
SamT
If you do enough coding to use a third party code tester, and need to save many "micro" versions, here's what I use in My Personal.xls
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'If I forget to save before closing. Provides a warning, in case I really don't want the latest changes.
If Not Me.Saved Then Me.Save
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUi As Boolean, Cancel As Boolean)
'Everytime the File is saved
Me.SaveCopyAs ("E:\COMPUTING\Programming\_VBA\MyPersonal\" & CDbl(Now) & "- Personal.xls")
End Sub
When I actually want to SaveAs a new version, I just do that normally from the Menu.
Thank you SamT - that's a great idea.
I found that I needed to modify this slightly for PowerPoint as when you call .SaveCopyAs from the equivalent PresentationBeforeSave event handler I get an error "Presentation (unknown member): Failed". A solution involving timers (posted here: https://www.pcreview.co.uk/threads/e...aveas.2839232/) did not work for me. So instead I call .SaveCopyAs from within a PresentationSave event handler - with a check for a recent save to prevent looping:
MyEventsModule:
Option Explicit
Public m_oMyEvents As CMyEvents
Private Sub Auto_Open()
If m_oMyEvents Is Nothing Then
Set m_oMyEvents = New CMyEvents
End If
Set m_oMyEvents.PPTEvent = Application
End Sub
CMyEvents class:
Option Explicit
Public WithEvents PPTEvent As Application
Private m_strSaveMinute As String
Private Sub PPTEvent_PresentationSave(ByVal Pres As Presentation)
Dim strNowMinute As String
strNowMinute = Format(Now, "yyyy-mm-dd-hh-nn")
If strNowMinute <> m_strSaveMinute Then
' This required to prevent an infinite loop as the SaveCopyAs event also
' triggers the PresentationSave event.
Application.ActivePresentation.SaveCopyAs Environ("HOMEPATH") & _
"\Documents\FileBackups\PowerPoint\" & "mybackup" & "-" & _
Format(Now, "yyyy-mm-dd-hh-nn-ss") & ".pptm", _
ppSaveAsOpenXMLPresentationMacroEnabled
End If
m_strSaveMinute = Format(Now, "yyyy-mm-dd-hh-nn")
End Sub