Back up excel files to one folder

[GraemeT]

Hi All,

I work work with a lot of excel files and when closing and saving those files I want to back them up to folder of the same name as the file; and the folder saved to another folder called "Excel Backups" on the desktop.
I'm not good with VBA and would appreciate someones help.

Regards
Graeme

 

[lrobbo314]

Welcome to the forum. Let me know if this does what you are looking for. You will need to adjust the paths for where your Excel files are and where you want them copied to.

Sub BackupExcelFiles()
Dim FC      As Object: Set FC = VBA.CreateObject("Scripting.FileSystemObject")
Dim CP      As Object: Set CP = VBA.CreateObject("Scripting.FileSystemObject")
Dim Path    As String: Path = "C:\ExcelFiles\" 'Folder with files
Dim Backup  As String: Backup = "C:\ExcelBackups\" 'Folder for backups
Dim sFile   As Object


Set FC = FC.GetFolder(Path)


For Each sFile In FC.Files
    CP.CopyFile Path & sFile.Name, Backup & sFile.Name
Next sFile


End Sub

 

[GraemeT]

Thank you IROBBO314 for your prompt reply, but I think you have misunderstood what I wanted.

I want a macro to run before save so that it will backup the file I’ve been working with.
The code below works fine as it’s in every file I use. The problem is that it places a separate folder for each file on the desktop, which clutters the desktop. What I want is one folder called “Excel Backups” on the desktop so that each file I use will save a backup to the “Excel backup” folder.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.EnableEvents = False

thisPath = ThisWorkbook.Path
myName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".") - 1))
ext = Right(ThisWorkbook.Name, Len(ThisWorkbook.Name) - InStrRev(ThisWorkbook.Name, "."))
backupdirectory = myName & " backups"
Set FSO = CreateObject("Scripting.FileSystemObject")
If Not FSO.FolderExists(ThisWorkbook.Path & "/" & backupdirectory) Then
FSO.CreateFolder (ThisWorkbook.Path & "/" & backupdirectory)
End If
t = Format(Now, "mmm dd yyyy hh mm")
ThisWorkbook.SaveCopyAs thisPath & "" & backupdirectory & "" & myName & " " & t & "." & ext

Application.EnableEvents = True
End Sub

 

[yky]

change

backupdirectory = myName & " backups"

to

backupdirectory =  "Excel backup"

and see if that's what you want.

 

[GraemeT]

Thank you jky that is great and it works. Can you improve it so it places all the files in separate folders within the folder "Excel backups". For example the backed up files named "Sample" would end up in a folder named "Sample" in the folder "Excel backups".

Thank you again

 

[yky]

Try this:

backupdirectory =  "Excel backup" & "\" & myName

 

[GraemeT]

Thank you works a treat much appreciated