Saving Workbooks On Workbook Open Event

[Ark68]

I have two workbooks that I fear other users may unintentionally change and then save the changes that shouldn't have been made.
Is there a way that when the users opens the workbook, before they have a chance to monkey with it, that original workbook is saved elsewhere with a different name?
I thought this ...

dt = Format(Now(), "dd-mmm-yy hh:mm:ss")
myFileName = ActiveWorkbook.Name & dt
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal, Password:="31286", addToMru:=False

This might work, but it will change the name of the activeworkbook, which will cause all kinds of problems in code needing the original file's name.

The second workbook isn't open yet. Is there a way I can copy it from it's directory, pasted into a second directory and renamed?

 

[Micron]

The main problem I see with that is that you're trying to append a date after the extension, so the result would be
MyFile.xlsx08-Dec-22 08:22:33 - not possible. First maybe think about whether or not you really need the time value. If so, I suppose you want to add another file and not over-write an existing one.

 

[Micron]

Also, you cannot use : in a file name, and when you do a save as, it becomes the current workbook. Is that what you want?
Beginning to think you want to use File Scripting Object to save a copy and keep the opened wb as the current session, or use SaveCopyAs, not SaveAs.

 

[Micron]

Here is a code sample using SaveCopyAs and an altered format that doesn't use :

Private Sub Workbook_Open()
Dim strName As String, strExtension As String

strName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".")) - 1)
strExtension = Mid(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".")))
strName = "C:\Temp\" & strName & " " & Format(Now, "dd-mm-yy hh_mm_ss") & strExtension
ActiveWorkbook.SaveCopyAs strName
End Sub

Create workbook open event, replace C:\Temp\ with the path location that you need. It should work for any Excel extension. Alter format of date/time details as required. Test with a copy of a file.
Hope that's what you're after.

 

[Jaafar Tribak]

Another approach you may consider is to make the workbook ReadOnly when opened by other users.

Private Sub Workbook_Open()
    If Environ("username") <> "Enter_Your_Windows_Login_UserName_Here" Then
        Application.DisplayAlerts = False
        Me.ChangeFileAccess xlReadOnly
        Application.DisplayAlerts = False
    End If
End Sub

Now, if the users try to save any changes they made to the workbook, they will be prompted to save under a different name hence leaving your original workbook intact.