Solved: Before save event

I have this before save event in my code, which, when saves runs ok.

Sub befor_save()
Range("A1").Select
MsgBox "Enjoy your metrics. Now please save this file so as not to overwrite the original"
sFilename = Application.GetSaveAsFilename("", "Excel files (*.XL*), *.XL*")
End Sub

I get the message box, then the save as box appears. The only issue I have is that when I do enter a name to save the file under, then click OK, it doesn’t actually save, and I am still left with the original copy open. Note that this code comes at the end of a lots of other lines (nothing too eleaborate), but why would that make a difference? If I open a new work book and just add this in now, it works fine?????? Any ideas?

 

Hello again widgeboy!

Well, it depends on what you want to do with the orignal copy of the file. You can save the file under a different name (deleting the original) or you can save the file under a different name whilst keeping the original intact. If you want to do the latter, you'll also need to know if you want to save the workbook at the time of name correction or if you want to leave the file as-is (assuming you saved prior) and only have the 'new' version have the latest changes.

If you can answer those questions we can line out a solution for you no problem.

 

You can also automatically "Save As" when the user closes the Workbook using the original File Name and adding the Date to the File Name, which does not require User intervention.
The date has to manipulated with left mid and right string to remove the "/"s from it first though

 

Are you sure Tony? AFAIK when the SaveAs method is used, any original copy is still maintained unless there has been no original save performed already.

Also, no need for formulas to weed out the illegal name characters, just manipulate with the Format() function using the Date variable.

 

Zack, the original is definitely maintained, which is why I like it, you can see the "history" of changes to the workbook, it is just a matter of taste and what you want to use the SaveAs for.

What is the Expression for fomatting the Date without the "/"?

 

If you are worried about people saving over the original, why don't you make the file a template file by using the .xlt extension. This will force them to save under a different name while preserving the original and not requiring any code or macros to be enabled. I personally prefer to leave a trail of changes like OBP states by appending the date to the filename via a macro.

Regards,
Rollin

 

Thanks for the responses. The workbook is a shared workbook – does that make a difference? Zack – in answer to your question, it is the latter of your suggestions. I want the user to save the file under a new name. I don’t want to save the original “as is” and so only have the “new” version with the changes.

 

@OBP: Just use the Format() function and a legal character, such as the dash..

Code:

Format(Date, "mm-dd-yyyy")

@widgeboy: Shared workbook should not make a difference in a save this way. Here is some basic code which is called from a standard module. Note that this is different than an actual save event code. Also note that with events turned to False when the save is performed, you will not trigger another save event, basically saving twice (one with code and one without). Talk about a troubleshooting nightmare. LOL! The other thing this code assumes is that there already is a date in the workbook name, if not it will add one, and the format will be of that specified. Note the date format I used in my code, always use two digit days and months (can be mm-dd or dd-mm, that part doesn't matter - for the coding), this will make it easier for you later.

Code:

Sub My_Before_Save()
    Const MYFORMAT As String = "mm-dd-yyyy"
    Dim strSaveName As String, strOldFile As String
    Dim strPath As String, strName As String, blnDate As Boolean
    MsgBox "Enjoy your metrics!" & vbNewLine & vbNewLine & _
           "This workbook will now be saved.", vbInformation
    strPath = ThisWorkbook.Path & Application.PathSeparator
    strOldFile = ThisWorkbook.FullName
    strName = ThisWorkbook.Name
    If Right(UCase(strName), 4) Like ".XL*" Then
        strName = Left(strName, Len(strName) - 4)
    Else
        'workbook not saved yet!
        MsgBox "You have not saved this workbook yet!", vbCritical, "NOT SAVED!"
        Exit Sub
    End If
    blnDate = False
    If Len(strName) > 10 Then
        If IsDate(CDate(Right(strName, 10))) Then blnDate = True
    End If
    If blnDate = True Then
        'change date
        strSaveName = Left(strName, Len(strName) - 11) & Format(Date, MYFORMAT) & ".XLS"
    Else
        'add date
        strSaveName = strName & " " & Format(Date, MYFORMAT) & ".XLS"
    End If
    Application.EnableEvents = False
    ThisWorkbook.SaveAs strPath & strSaveName
    Application.EnableEvents = True
    Kill strOldFile
End Sub

Let us know how this works for you.

HTH