1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Before save event

Discussion in 'Business Applications' started by widgeboy, Jan 19, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. widgeboy

    widgeboy Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    43
    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?
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. :)
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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 "/"?
     
  6. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  7. widgeboy

    widgeboy Thread Starter

    Joined:
    Jul 17, 2006
    Messages:
    43
    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.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    @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
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/536580

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice