Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SavingBackUp (2003)

    I would like to automatically save a copy of a workbook upon opening, designate a file location and use a variable name for the back-up that has the year, month, date and time.

    The following code obviously falls short, but contains some of the logic that I am thinking of.

    Private Sub Workbook_Open()
    ActiveWorkbook.SaveCopyAs "My DocumentsAmyWorkbook_yyyy-mm-dd-time.xls"
    strSaveName = "Amy'sWorkbook_" & Format(Date, "yyyy-mm-dd-time") & ".xls"
    .SaveCopyAs FileName:=strSaveName
    End Sub

    Any help is always appreciated. Ice cream for everyone! My evil doppleganger will pick up the tab.

    Amy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SavingBackUp (2003)

    The first line ActiveWorkbook.SaveAs contains yyyy-mm-dd-time as a literal string, i.e. the name of the workbook will always be AmyWorkbook_yyyy-mm-dd-time.xls whatever the date and time is.
    There are two problems with the next line:
    - Date doesn't have a time component, it's just the date.
    - ...-time doesn't make sense in a Format string.
    The third line has .SaveCopyAs but it's not clear what it applies to.

    Try this:

    Private Sub Workbook_Open()
    Dim strSaveName As String
    strSaveName = "Amy'sWorkbook_" & Format(Now, "yyyy-mm-dd-hh-mm-ss") & ".xls"
    ThisWorkbook.SaveCopyAs Filename:=strSaveName
    End Sub

    You can change the format string but you can't use / or : since those aren't allowed in file names.
    PS It would be nice if you provided feedback to the replies that you receive, so that others know whether the replies were helpful.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SavingBackUp (2003)

    Hans,
    This worked perfectly. This saved the file in the My Documents directory by default. If I added a file directory prefix to the file name, that places the file in the specified location as opposed to the default My Documents.

    For example, if I wanted to redirect to a sub-folder then I use the syntax "publicdriveAmyNMy DocumentsAdminAmy'sWorkbook_"

    I think this is a better solution than the Back Up Copy File-->Save As...-->Tools-->General Options...-->Always create backup

    The problem with the standard backup is that the backup always gets overwritten with the potential of overwriting a good file with a bad file. That is, a user can enter the file and add data or delete things that you don't want. Having a the standard backup doesn't help prevent that. The solution that you provided can keep a record of changes.

    Thanks.

    Do you have a thread that discusses how you create a log of those who entered an Excel file?

    Amy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SavingBackUp (2003)

    See for example the thread starting at <post:=689,904>post 689,904</post:> or the one at <post:=683,345>post 683,345</post:>.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SavingBackUp (2003)

    Thanks Hans. I remembered asking about the username part of the log, but couldn't track down when I had asked. I see how it works together now.

    Amy

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SavingBackUp (2003)

    I have found that I need a modification to the provided code:

    I have combined it with Worksheets("Sheet2").Activate. No problem there.

    However, I need the back up save disabled when the workbook name doesn't match the orginal file. So for the code below, I would want the back up code to work if the workbook name was "Amy'sWorkbook.xls", but if the file was renamed, then the code would not create a back up.

    Private Sub Workbook_Open()
    Worksheets("Sheet2").Activate
    Dim strSaveName As String
    strSaveName = "Amy'sWorkbook_" & Format(Now, "yyyy-mm-dd-hh-mm-ss") & ".xls"
    ThisWorkbook.SaveCopyAs Filename:=strSaveName
    End Sub

    Thanks
    Amy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SavingBackUp (2003)

    You could do something like this:

    Private Sub Workbook_Open()
    Dim strSaveName As String
    Worksheets("Sheet2").Activate
    If ThisWorkbook.Name = "Amy'sWorkbook.xls" Then
    strSaveName = "Amy'sWorkbook_" & Format(Now, "yyyy-mm-dd-hh-mm-ss") & ".xls"
    ThisWorkbook.SaveCopyAs Filename:=strSaveName
    End If
    End Sub

  8. #8
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SavingBackUp (2003)

    Thanks! This prevents users from saving a personal copy of a shared workbook and then creating archived back-ups of the changes they make to their personal workbook.

    a thousand blessings
    Amy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •