Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto backup of open Workbook - again ! (Excel 2000)

    People,

    I've seen the posts about doing an automatic backup of the open workbook to a different folder when saving. I've decided to use the application event Workbook_BeforeSave which works well. I save my code in an add-in and run Workbook_Open to initiate the events with no trouble.

    But it gets very confused if the file has not been saved to disk before. It's difficult to save a backup for a file if it doesn't have a name ! So, I tried popping-up the SaveAs dialogue which worked except that the saveas dialogue popped-up again when the Save event ran.

    So, for the time being I'm excluding un-saved workbooks from the autobackup. It would really help if there was an 'AfterSave' event but I can't find a way of simulating that. Alternatively, can anyone think of a way of doing something after the BeforeSave ?

    By the way, using other methods such as mapping to Ctrl-s and the toolbar Save button might not capture a File Close or Doc Close commands.

    Cheers

    GB

    Here's a copy of my code so far (without comments to save space):

    Public WithEvents Appl As Application
    Private Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)
    Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Dim strUserName As String
    Dim fOSUserName As String
    Dim lngLen As Long, lngX As Long
    Dim MyPath

    On Error GoTo error_check

    If ActiveWorkbook.Path = vbNullString Then Exit Sub

    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
    fOSUserName = Left$(strUserName, lngLen - 1)
    Else
    fOSUserName = ""
    End If
    MyPath = "cocuments and Settings" & fOSUserName & "Excel-Backups"

    Set objFS = CreateObject("Scripting.FileSystemObject")
    If Not objFS.FolderExists(MyPath) Then
    objFS.CreateFolder MyPath
    End If

    objFS.CopyFile ActiveWorkbook.FullName, MyPath & ActiveWorkbook.Name, True

    Set objFS = Nothing
    Call CoTaskMemFree(lngX)
    GoTo finish
    error_check:
    MsgBox Err.Number & " - " & Err.Description
    finish:
    End Sub
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Are you aware Excel has the option to generate backup copies itself? it then simply renames the extension to BAK. You could make sure the backup option is set to on and then use your Before_Save code to copy the backup file.

    You can have an AfterSave event!

    Just create a normal sub in a normal module :

    Public oWkbk as wWorkbook

    Sub AfterSaveCode()
    If oWkbk is nothing then exit sub
    'Do whatever needs to be done
    End Sub

    Now in the Before_SaveAs code:

    Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    set oWkbk=Wb
    Application.OnTime now,"AfterSaveCode"
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    You can set the Cancel parameter passed to the BeforeSave event routine to True, and the Save will not be done after you exit.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Thanks for the suggestions.

    I tried to set the Cancel parameter but it prevents the SaveAsUI from occuring on a new file.

    I tried the After_SaveAs idea which worked well except if the file was closed or the user cancelled the save. I can deduce the name of the file if it is closed but I haven't figure out how to tell if the user has cancelled the save or just said No to the save. I get the "Do you want to save the changes you have made..." before the SaveAsUI and if the user cancels, after it as well.

    I need to do more thinking. Perhaps I need to capture the value of the SaeAsUI and /or Cancel parameters to find out what is happening when the file is saved. I'm probably playing with both the close and save events which gets complicated !

    Cheers.
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Of course you can add a check to see if the workbook is still present in the aftersave event. You could also add a check to determine whether the owrkbk has been saved:

    Public oWkbk as wWorkbook

    Sub AfterSaveCode()
    dim sTemp as string
    on error resume next
    sTemp=owkbk.fullname
    if err.number<>0 then exit sub
    If oWkbk is nothing then exit sub
    If owkbk.saved then
    'Do whatever needs to be done
    else
    'wkbk hasn't been saved!
    End If
    End Sub

    Now in the Before_SaveAs code:

    Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    set oWkbk=Wb
    wb.saved=False
    Application.OnTime now,"AfterSaveCode"
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Two possibilities. If the file has nener bee saved, you can do the SaveAs yourself, or just don't set Cancel = True.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Many thanks, Legare and Jan Karel,

    These ideas work for me in some situations but not all. The main problem I have is if the user does a WindowClose, then saves changes. When the execution gets to my AfterSave event, the file is no longer open and so, I will have to somehow pass the filename of the previously saved file through to the AfterSave module. I tried a global variable but, couldn't get it to pass the variable value through - probably because I don't understand yet how to do it !

    There are many different possible situations I'm trying to cope with: eg. saving a new file, close-save a new file, close-no-save a new file, abort closing a new file, save an old file, close-save an old file, close-no-save an old file, abort closing an old file etc. I need to track each one through.

    I'm going to do more thinking over the weekend.

    Cheers.
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    See if this one meets your needs.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Jan Karel, struth ! Many thanks. Now, go back and get some sleep !

    I will have to wait until the weekend to get into this some more.

    Cheers.

    Garry Brooke
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Hi Garry,

    <<Now, go back and get some sleep >>

    I'd like that, it's just that over here the working day has just started...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Jan Karel,

    It was 5PM in Garry's time when he posted. But his profile says he's a public servant. So that probably explaind it <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  12. #12
    Lounger
    Join Date
    Apr 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Jan Karel,

    I've finally got the aftersave module working ! I've attached a copy.

    There is only one problem remaining that I can see - the whole thing dies if the user has a hidden workbook. For reasons best known to MS, there is no hidden property for an entire workbook - we have to use the worksheets.visible property which I tried but I got errors.

    Anyway, it seems to work fine otherwise.

    In response the the public servant crack, we tend to think everyone in Europe is asleep at least until we get home - at around 7.00pm our time. So, it's nice to know we are mistaken.

    Cheers !

    GB
    Attached Files Attached Files
    <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: Auto backup of open Workbook - again ! (Excel 2000)

    Garry,

    A Window has a Visible property, so you could use the Windows collection to check for hidden workbooks (a hidden workbook *is* part of the Windows collection).

Posting Permissions

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