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

 

[Dr. Demento]

I've left work, but I'm pretty sure this is it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  Const sBak As String = "H:\something\something\Personal.xlsb"


  Application.DisplayAlerts = False
  With Workbooks("Personal.xlsb")
    .SaveCopyAs sBak & Format(Now(), "_yyyymmdd.bak")
    .Save
  End With
  Application.DisplayAlerts = True
End Sub

If I change it from a BeforeClose event to a regular macro, it works fine. Otherwise, the program closes but doesn't backup.

 

[shg]

This is in the ThisWorkbook module for Personal, correct? Try this, and single-step when you hit the Stop statement:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
  Const sBak        As String = "H:\something\something\Personal.xlsb"
  
  Stop

  If Not Me.Saved Then
    Application.DisplayAlerts = False
    Me.SaveCopyAs sBak & Format(Date, "_yyyymmdd.bak")
    Me.Save
    Application.DisplayAlerts = True
  End If
End Sub

 

[Dr. Demento]

Correct. I'll give it a whirl on Monday.

Thanks much.

 

[shg]

It appears that the BeforeClose event doesn't fire in Personal. Let me invite someone smarter than me into the thread to explain that, and suggest a workaround.

Also, this didn't work:

Option Explicit

Dim WithEvents appXl As Application

Private Sub Workbook_Open()
  Set appXl = Application
End Sub

Private Sub appXl_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
  Stop
  If Wb Is Me And Not Me.Saved Then BackupPersonal
End Sub

appXl doesn't persist past the Open event.

 

[Dave Patton]

Hello Shg
I tried your initial suggestion from a regular xlsm file and it worked fine.

When I opened Excel today, both the Personal.xlsb and the .bak file opened in the VBA list of open files.
I do not know why the Bak file shows.
Should I change something so the Bak is not active?

Thanks Dave

 

[shg]

Hi, Dave,

The backup needs to be someplace other than where Personal lives.

 

[RoryA]

I believe it's a bug though I can't recall an official KB article about it. I would suggest you change the process slightly and use the BeforeSave event instead. You'll only be prompted to save if there's actually something changed, and it should work to run the SaveCopyAs code too - just don't forget to disable events, then run the SaveCopyAs and then re-enable (just in case).

 

[shg]

Rory, thank you.

What about appXl not persisting?

 

[Dr. Demento]

In poking around, I found this article about creating application-level event handlers, as someone suggested is the route to go. Not that I know what that really means

 

[RoryA]

What you might do is create a separate add-in that handles backing up the Personal macro workbook.