Backing up Personal.xlsb using VBA

[Dr. Demento]

I'm attempting to have Excel backup Personal.xlsb everytime I close Excel; I make so many changes each day (thanks to the hard work that y'all are doing, there's plenty to borrow from) and if I want to VPN into work, I can't get to my actual hard drive, whereas I have full access to the shared drive.

I've attempted to modify Jimmy Pena's work (shout out!), but I'm getting sporadic results; it worked once and now there are no errors to tell me what went wrong (even after commenting out the error suppression). After I got it to work once (not by closing Excel, but by running the macro (before making it _BeforeClose)), it wouldn't overwrite the existing file. After that, it stopped working altogether (although further futzing may have something to do with that ).

I have it in the ThisWorkbook module. I'd like it to overwrite previous backup files without prompting (hence, DisplayAlerts), but I don't know if that's affecting it somehow.

Also, I tried including the filetype:=50 as suggest by RdB, but I have no idea how/where to insert that tidbit.

Any insights would be most appreciated.

And, as a random aside, is it normal when I save Personal that I have to save it twice? Personal.xlsb is hidden; if I save it once, the "cursor" disappears from the visible worksheet and doesn't re-appear until I save Personal a second time. Just curious.

' API to make a copy of a currently open file
Private Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _
(ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String, _
ByVal bFailIfExists As Long) As Long

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' [URL]http://www.jpsoftwaretech.com/updated-vba-backup-code/[/URL]

  On Error GoTo ErrorHandler
  
  Dim todaysDate As String

  Const fileName As String = "PERSONAL"
  Const fileType As String = ".xlsb"
  Const SourceFolder As String = "\\Shared Drive\AppData$\win7\User\AppData\Roaming\Microsoft\Excel\XLSTART\"
  Const DestinFolder As String = "\\Shared Drive\Documents\Computer Hacks\MS Office Hacks\Excel\Personal_xlsb\"
  Application.DisplayAlerts = False

    
  ' get current date and time
  todaysDate = Format(Now, "YYYYMMDD")
  ThisWorkbook.Save
 
  Call apiCopyFile(SourceFolder & fileName & fileType, DestinFolder & fileName & "_" & todaysDate & fileType, False)

  Application.DisplayAlerts = True

ErrorHandler:
  MsgBox Err.Number & " - " & Err.Description
  Resume ProgramExit

End Sub

 

[shg]

In poking around, I found this article about creating application-level event handlers[/URL], as someone suggested is the route to go.

That's what the last code I posted attempted to do.

 

[Dr. Demento]

That's a good idea for home, but not at work; even the built-in add-ins are disabled (Analysis Tool-Pak, etc). *grr*

Thanks for your help, shg & Rory.

 

[RoryA]

In that case I suggest you use the BeforeSave event.

 

[Dr. Demento]

Rory,

BeforeSave works! Thanks much,sir!