VBA Save Personal.XLSB to second file path if first file path is not available

[Knockoutpie]

Hi all, ran across some code a while back from reddit on how to backup the PERSONAL.XLSB file with every save, it's pretty nifty!
I'm attempting to modify the code to where if the first file path is unavailable, then it will save the backup to the second file path.
I think i'm close, but when I disconnect from wifi to detach from the network drive and save, i get an excel crash..

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   ' Location to save Personal.XLSB and backup if necessary
   Const primaryPath As String = "\\readyshare\USB_Drive\Codes\Personal.XLSB"
   Const backupPath As String = "C:\Users\User1\Documents\PersonalXLSB\Personal.XLSB"
   Application.DisplayAlerts = False
   ' Check if primary path is available
   If Dir(primaryPath) = "" Then
       ' Save to backup path if primary path is  available
       ThisWorkbook.SaveCopyAs primaryPath & Format(Now(), "_yyyymmdd.bak")
   Else
       ' Save to backup path if primary is not available
       ThisWorkbook.SaveAs backupPath & Format(Now(), "_yyyymmdd.bak")
   End If
   Application.DisplayAlerts = True
End Sub

when it does work, the end result is (image attached)

Any help would be appreciated!

 

[rollis13]

Have a try with this added code:

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' Location to save Personal.XLSB and backup if necessary
    Const primaryPath As String = "\\readyshare\USB_Drive\Codes\Personal.XLSB"
    Const backupPath As String = "C:\Users\User1\Documents\PersonalXLSB\Personal.XLSB"
    Application.DisplayAlerts = False
    On Error GoTo primaryNA                       '<-added
    ' Check if primary path is available
    If Dir(primaryPath) = "" Then
        ' Save to backup path if primary path is  available
        ThisWorkbook.SaveCopyAs primaryPath & Format(Now(), "_yyyymmdd.bak")
    Else
primaryNA:                                        '<-added
        On Error GoTo 0                           '<-added
        ' Save to backup path if primary is not available
        ThisWorkbook.SaveAs backupPath & Format(Now(), "_yyyymmdd.bak")
    End If
    Application.DisplayAlerts = True
End Sub

 

[Knockoutpie]

Hey, just had to move the on error GoTo part a little higher up and we fixed the issue, works as proper now!
IF network drive is not available, backup personal.XSLB to local directory. Perfect for when i'm on the go or in the office. Thanks a bunch!

Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error GoTo primaryNA
    ' Location to save Personal.XLSB and backup if necessary
   Const primaryPath As String = "\\readyshare\Network_Drive\Code_Backups\Personal.XLSB"
   Const backupPath As String = "C:\Users\USER1\Documents\PersonalXLSB\Personal.XLSB"
    Application.DisplayAlerts = False
                         '<-added
    ' Check if primary path is available
    If Dir(primaryPath) = "" Then
        ' Save to backup path if primary path is  available
        ThisWorkbook.SaveCopyAs primaryPath & Format(Now(), "_yyyymmdd.bak")
    Else
primaryNA:                                        '<-added
        On Error GoTo 0                           '<-added
        ' Save to backup path if primary is not available
        ThisWorkbook.SaveAs backupPath & Format(Now(), "_yyyymmdd.bak")
    End If
    Application.DisplayAlerts = True
End Sub

 

[rollis13]

just had to move the on error GoTo part a little higher up and we fixed the issue

Sure ? the line that would throw an error is: ThisWorkbook.SaveCopyAs primaryPath & Format(Now(), "_yyyymmdd.bak") so the On Error line could have been placed just before it; but if you had an issue it's okay for me too.
Thanks for the feedback, glad having been of some help.