Make BackUps of files Using Excel List

[Slicemahn]

Hello VBAX Nation!

I am a user of Excel 2016 and I have to backup databases that have been listed in an Excel worksheet. The challenge is that I need to identify the right Dbs to backup in the directories listed and I have to rename the file using standardized naming convention of backup date (ie. 2022-01-27_filename).

I have tried to use VBA to do this but it won't do I want.

Sub BackUpEnMasse()Dim filelist As Variant
Dim i As Long
Dim filetocopy As String
Dim copysource As String
copysource = "C:\copyfrom\"
filelist = ActiveWorkbook.Sheets("Prod Dbs").Range("E1").CurrentRegion.Value
For i = 2 To UBound(filelist)
    fname = Dir$(copysource & "*.*")
    While fname <> ""
        If UCase(fname) = UCase(filelist(i, 6)) Then
            FileCopy copysource & fname, "C:\Copyto\" & fname
        End If
        fname = Dir$()
    Wend
Next i


End Sub

Thank you for your help on this my coding is rusty so I hope that this will jumpstart my learning once again!

Prod Updt.xlsb

[p45cal]

In cell E3 (for example) you have:
T:\Development\Databases\FA Accounting
Is that purely a folder name, or (as you code suggests) is it the folder name and the start of the file name(s) (all filenames have to start with FA Accounting before you back them up)

Similarly for column F (Destination); are these pure folder names or folder names and the start of filenames?

Finally, is column D relevant?

[Slicemahn]

Hi P45cal,

Thank you for responding. Yes, Column D would be redundant but that is how the sheet is structured. So the paths would be for example T:\Development\Databases\ for the directory for the dbs and then T:\Development\Database BackUps\ for the backups. But the twist is that within the directory of the dbs there are FEs and BEs with the same name as column D along with other dbs in that directory. So it wouldn't be hard to choose which db should be backed up but I am challenged as to how I would go about choosing the right dbs and backing them up using the naming convention as I writtened earlier. So to answer your question, yes the dbs have to start with FA Accounting or FD Assets or FL Authorizations but again these dbs have FEs and BEs that both need to be backed up.

[p45cal]

What are

FEs and BEs

?

[Slicemahn]

Hi p45cal,
I apologize for the delayed response. These are front ends and back ends of dbs.

[p45cal]

To be honest, I'm really no clearer from your answers, so this is a guess:

Sub BackUps2()
Dim filelist1, filelist2 As Variant
Dim i As Long
Dim filetocopy As String
Dim copysource As String, FileDt As Date
filelist1 = ActiveWorkbook.Sheets("Production Dbs").Range("E1").CurrentRegion.Columns(5).Value
filelist2 = ActiveWorkbook.Sheets("Production Dbs").Range("F1").CurrentRegion.Columns(6).Value

FileDt = Now
dte = Format(FileDt, "yyyy-mm-dd_")
For i = 2 To UBound(filelist1)
  fname = Dir$(filelist1(i, 1) & "*.accdb")
  While fname <> ""
    cFrom = filelist1(i, 1)
    cFrom = Left(cFrom, InStrRev(cFrom, "\", , vbTextCompare))
    cFrom = cFrom & fname
        
    cTo = filelist2(i, 1)
    cTo = Left(cTo, InStrRev(cTo, "\", , vbTextCompare))
    cTo = cTo & dte & fname
    
    FileCopy cFrom, cTo
    fname = Dir$()
  Wend
Next i
End Sub

It could be a lot shorter, but it's still in development.

[Slicemahn]

Hi p45cal,

I will give this a try! There are a lot iterations of database development in the folders in which need to be kept but the "production" databases are the active databases in which need to be backed up. So these active databases have an Access front end (replete with forms, reports, etc.) and a backend in which the front end connects to the data stored in tables. These have been backed up weekly but with the number of users increased we desire that this be performed on a daily basis. It takes a while to copy and paste these databases and rename them so I am hopeful that this script would cut that time down considerably.