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]

Here's what I use to save and backup personal:

Sub BackupPersonal()
  Const sBak        As String = "C:\Users\shg\Documents\Docs - shg\Tech Notes\misc - Templates\Personal.xls."

  Application.DisplayAlerts = False
  With Workbooks("Personal.xls")
    .SaveCopyAs sBak & Format(Now(), "yyyy-mmdd-hhmm.bak")
    .Save
  End With
  Application.DisplayAlerts = True
End Sub

 

[Dr. Demento]

That works for me, shg! Thank you! I like the tight code; I'm just getting my feet wet with VBA, trolling thru the Google on the interwebs.

I'm assuming that saving as an .xls or .xlsb doesn't make a difference.

Out of curiosity, why does Ron de Bruin advocate using filetypes? Is it a backward/forward compatibility issue?

Thanks again.

 

[shg]

SaveCopyAs always saves in the same format as the file, which is xls for me (my Personal is not an xlsm or xlsb, for Excel 2003 compatibility). That's obscured to Windows by the extension, but I strip that off if I want to restore a prior version.

 

[Dr. Demento]

Any reason it won't execute when I close Excel? What am I missing?

 

[shg]

You need to call it from the close event.

 

[Dr. Demento]

That's what so confusing - I have it as a Workbook BeforeClose event in the ThisWorkbook module. Does it need to be in a different module?

 

[shg]

What's the code in the BeforeClose event?

 

[Dr. Demento]

Your code listed above - verbatim! (with my destination directory,of course)

 

[shg]

Would you please post your code?