Results 1 to 6 of 6

Thread: Backing Up (97)

  1. #1
    New Lounger
    Join Date
    Jul 2003
    Location
    london
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Backing Up (97)

    I want to automatically back up 2 of my excel documents everytime I save them and decide on the location where the back up will be held - is this possible?

    Thanks in advance

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Backing Up (97)

    Add something like this to the tisworbook object of each of the files.

    Steve

    <pre>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim sBackupname As String
    Dim sFullBackupName As String
    Dim iResponse As Integer
    sBackupname = Application.WorksheetFunction.Substitute( _
    ThisWorkbook.Name, ".xls", "(backup).xls")
    sFullBackupName = Application.GetSaveAsFilename( _
    InitialFilename:=sBackupname, _
    Title:="Backup Name")

    If sFullBackupName <> "False" Then
    ThisWorkbook.SaveCopyAs sFullBackupName
    Else
    iResponse = MsgBox( _
    prompt:="No Backup file was chosen." & vbCrLf & _
    "Do you still want to save the file?", _
    Buttons:=vbYesNo)
    If iResponse = vbNo Then
    Cancel = True
    MsgBox "File is NOT saved"
    End If
    End If
    End Sub</pre>


  3. #3
    New Lounger
    Join Date
    Jul 2003
    Location
    london
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up (97)

    thanks for that - it's a bit too advanced for the user groups I am working with - isn't it possible to do something through the options option in the tools menu?
    cheers

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Backing Up (97)

    No, there are no options to do this. You will need the VBA code in the BeforeSave event routine. Once the code is placed in the BeforeSave event routine, the users will not have to know anything except that they have to specify a location for the backup file.
    Legare Coleman

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Backing Up (97)

    If desired, you could hard code the path and the filename for backing up and they would NEVER be prompted for a path.

    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Backing Up (97)

    A compromise:
    If you SAVE AS and choose options, you can elect to always save a backup, but the backup will be be in the same folder as the original.

    Steve

Posting Permissions

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