VBA code to save a backup of an existing Excel file

[GVK]

Hi, I am using the below VBA code to save a time- and date-stamped backup copy of the Active excel file in a separate folder then reopen the Master (initial file) and close the backup copy, which is saved in a designated path. When I 'step in' the code it seems to be working fine. When I however execute the code from an Form Control button from within the Master excel file the code runs non-stop, giving the impression it has entered into an endless loop. Can you please help with what might be wrong with the below code?

------------------------------------------

Sub Backup()
    Dim ws As Worksheet
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim filepath As String
   
    If ActiveWorkbook.Name = "[Active Excel Filename]" Then
        If MsgBox("This action will save a copy of the file. Do you want to proceed?", vbYesNo) = vbYes Then
       
            Application.ScreenUpdating = False
            Application.DisplayAlerts = False
            Application.CalculateBeforeSave = False
           
            filepath = "[path at which the backup file will be saved ]"
                With ActiveWorkbook
                    .SaveAs filename:=filepath & Format(Now(), "ddmmyyyy hhmmss") & ".xlsb"
                End With
            Set wb1 = ThisWorkbook
           
            Application.CalculateBeforeSave = True
           
            Workbooks.Open "[path at which the master file is located]"
           
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
           
            wb1.Close
                       
            Else
        End If
    Else
    End If
End Sub

 

[dmt32]

Hi welcome to forum

if you are just making a copy of the workbook then use the SaveCopyAs method

See if this update to your code does what you want

Sub Backup()
    Dim strFileName     As String
    
    Const strFilePath   As String = "[path at which the backup file will be saved ]"
    
    If MsgBox("This action will save a copy of the file." & Chr(10) & _
                "Do you want to proceed?", 36, "BackUp File") = vbYes Then

        With ThisWorkbook
            strFileName = Left$(.Name, InStrRev(.Name, ".") - 1) & " " & Format(Now(), "ddmmyyyy hhmmss")
            .SaveCopyAs Filename:=strFilePath & strFileName
        End With
        
        MsgBox "File BackUp Completed", 64, "File BackUp"
    
    End If
End Sub

ensure you enter a valid filepath where shown

Dave

 

[GVK]

Thank you Dave! A quick one - the saved file comes up with a space just before the file name, which apparently prevents the saved file from syncing. Is there a quick way to avoid this space being added at the beginning of the filename of the backup file? (The master file's name does not contain any spaces)

Also in the Left formula do I need the '$' sign - just curious as I often use the 'Left' instead of 'Left$'

Thank you again!

 

[GVK]

Sorry Dave - I fixed the "space" issue (it was my fault in the filepath I included an unnecessary space). Thank you again for your help!

 

[dmt32]

the Left formula do I need the '$' sign - just curious as I often use the 'Left' instead of 'Left$'

if you have a variable where data is returned to declared as a string, then both the Left and the Left$ function return the same answer.
Left function can though, return NULL values whereas the Left$ function can only return string values.

Glad update resolved your issue & appreciate feedback

Dave

 

[GVK]

Thank you!