Delete file in a folder with vba or override with new file

[kelly mort]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim backupfolder As String


    backupfolder = "C:\BackupFile\"


    ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
End Sub

Hi,
The above code is what I am using to create a backup of my data. I want it to override the file inside that backupfolder but it won't and hence throws an error. With three suggested reasons of which I fall into one; file already exists

So I am thinking of two options here:
1. Override it if possible with vba
2. Delete the previous file before creating new

Thanks in advance

 

[NdNoviceHlp]

Seems like using copyfile would do what U want. HTH. Dave
https://www.mrexcel.com/forum/excel...but-permission-denied.html?highlight=copyfile

 

[kelly mort]

Seems like using copyfile would do what U want. HTH. Dave
https://www.mrexcel.com/forum/excel...but-permission-denied.html?highlight=copyfile

Nice nice, but the thing is that I don't want to hard code the filename. I have no idea how to make it copy the Activeworkbook . Will be glad if you can assist with that.

 

[DanteAmor]

I tested your code and it does not show error. But I give you the options:

Option 1:Override it if possible with vba

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    Dim backupfolder As String
    backupfolder = "C:\BackupFile\"
    ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
End Sub

Option 2: Delete the previous file before creating new

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.DisplayAlerts = False
    Dim backupfolder As String
    backupfolder = "C:\BackupFile\"
    If Dir(backupfolder & ThisWorkbook.Name) <> "" Then
        Kill (backupfolder & ThisWorkbook.Name)
    End If
    ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
End Sub

 

[kelly mort]

Great! Great!! Great!!!
Working now.

One last thing :

How do I avoid the code running when I open the file from the backup folder? That is when I have the backup opened, then don't create the backup since that will try to override that opened file. Which will result in errors.

 

[NdNoviceHlp]

I've used this to avoid errors...

Public Function NoFileEr(Flpath As String)
'check if file exists. Copy to temp file from real file
Dim FS As Object, Temp3 As String
Set FS = CreateObject("Scripting.FileSystemObject")
Temp3 = Left(Flpath, Len(Flpath) - 4) & "T.xlsm"
FS.CopyFile Flpath, Temp3
Set FS = Nothing
End Function
Public Function BackToR(Retpath As String)
'if real source file open continue (leave source file open)
'if real transfer file open, close it and continue
'copy temp file back to real transfer file & kill temp file
Dim FS As Object, Temp2 As String
Temp2 = Left(Retpath, Len(Retpath) - 4) & "T.xlsm"
Set FS = CreateObject("Scripting.FileSystemObject")
On Error GoTo Errcode
FS.CopyFile Temp2, Retpath
Set FS = Nothing
Kill Temp2
Exit Function

Errcode:
On Error GoTo 0
FS.CopyFile Temp2, Retpath
Set FS = Nothing
Kill Temp2
End Function

Use something like...

NoFileEr ("C:\foldername\filename.xlsm")
BackToR ("C:\foldername\filename.xlsm")

HTH. Dave

 

[rlv01]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim backupfolder As String
    backupfolder = "C:\BackupFile\"
    If backupfolder & ThisWorkbook.Name <> ThisWorkbook.FullName Then
        Application.DisplayAlerts = False
        ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
        Application.DisplayAlerts = True
    End If
End Sub

 

[kelly mort]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim backupfolder As String
    backupfolder = "C:\BackupFile\"
    If backupfolder & ThisWorkbook.Name <> ThisWorkbook.FullName Then
        Application.DisplayAlerts = False
        ThisWorkbook.SaveCopyAs Filename:=backupfolder & ThisWorkbook.Name
        Application.DisplayAlerts = True
    End If
End Sub

Wow! !!!

Very cooler than I ever imagined.
Thanks