Backing up Personal.xlsb using VBA

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573
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 :mad:).

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.


Code:
' 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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Here's what I use to save and backup personal:

Code:
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

Well-known Member
Joined
Nov 2, 2010
Messages
573
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. :whistle:

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 2, 2010
Messages
573

ADVERTISEMENT

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

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You need to call it from the close event.
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573

ADVERTISEMENT

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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
What's the code in the BeforeClose event?
 

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
573
Your code listed above - verbatim! (with my destination directory,of course)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Would you please post your code?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,206
Messages
5,594,832
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top