Back up excel files to one folder

GraemeT

New Member
Joined
Sep 22, 2016
Messages
4
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.

Code:
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
 
Upvote 0
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
 
Upvote 0
change
Code:
backupdirectory = myName & " backups"
to
Code:
backupdirectory =  "Excel backup"
and see if that's what you want.
 
Upvote 0
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
 
Upvote 0
Try this:
Code:
backupdirectory =  "Excel backup" & "\" & myName
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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
Back
Top