File BackUp by Arrays

CCSlice

New Member
Joined
Feb 11, 2022
Messages
20
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I am hopeful and thankful for any help on this. I have been looking at my code and I cannot figure out why the files in column G are not being copied to their new locations. I have checked the strings for both source and destination locations, as well as the database names.

Here is the
VBA Code:
Sub dbReplicate()
Dim FileName, fDtName, dbTarget, newFile As String
Dim i As Long


dbRosterFolder = ActiveWorkbook.Sheets("Production Dbs") _
                 .Range("D1").CurrentRegion.Columns(4).Value

fDtName = Format(Now, "yyyy-mm-dd ")

' Loop through Db Roster locations

        For i = 2 To UBound(dbRosterFolder)

   
dbTargetFile = Sheets("Production Dbs").Range("F" & i).Resize(1, 3)


        Dim dbDirectoryContents() As String
        ReDim dbDirectoryContents(1000)
       
        dbTarget = Dir$(Cells(i, 4).Value)
            
              
        Dim FilesofInterest As Variant
       
        FilesofInterest = Array(dbTargetFile)
        On Error Resume Next
       
                Do While dbTarget <> ""
                   dbDirectoryContents(Counter) = dbTarget
                   dbTarget = Dir$
                  
                   ' Conditional Statement to determine if file
                   ' is found and to be copied
                   
                    If InStr(dbTarget, FilesofInterest) > 0 Then
                         newFile = fDtName & dbTarget
                         FileCopy Cells(i, 4).Value & dbTarget, _
                         Cells(i, 5).Value & newFile
                        
                    End If
                   
                   
                Loop
                     Counter = Counter + 1
                  ' When all dbFiles copied, update the "Back Up Date"
                  ' field with date and timestamp
  
                        With Cells(i, 9)
                         .Value = Format(Now, "mm-dd-yyyy hh:nn")
                         .Font.Bold = True
                        End With
        Next i
        MsgBox "Database Backup Operation complete", vbInformation
End Sub
 
Last edited by a moderator:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Hi Everyone!

My apologies. I didn't realize that my screen capture was too large to upload. I have attached the mini-sheet instead.

Prod Updt.xlsb
DEFGHI
1SourceDestinationFile 1File 2File 3Back Up Date
2T:\Development\Databases\FD Assets\T:\Development\Database BackUps\FD Assets\FD Assets_FE.accdbFD Assets_BE.accdb2022-02-11 2:57
3T:\Development\Databases\FA Accounting\T:\Development\Database BackUps\FA Accounting\FA Accounting_FE.accdbFA Accounting_BE.accdb2022-02-11 2:57
4T:\Development\Databases\FL Authorizations\T:\Development\Database BackUps\FL Authorizations\FL Authorizations_FE.accdbFL Authorizations_BE.accdbFL Authorizations_Lgcy.mdb2022-02-11 2:57
Production Dbs
 
Upvote 0
Hi, a VBA demonstration for starters :​
VBA Code:
Sub Demo1()
        Dim V, R&, C%
    With ActiveWorkbook.Sheets("Production Dbs").[A1].CurrentRegion
           .Range("I2:I" & .Rows.Count).Font.Bold = False
            V = .Columns("D:H").Value2
        For R = 2 To .Rows.Count
        For C = 3 To 5
            If Not IsEmpty(V(R, C)) Then
                If Dir(V(R, 1) & V(R, C)) > "" Then
                    FileCopy V(R, 1) & V(R, C), V(R, 2) & V(R, C)
                    With .Cells(R, 9):  .Value = Now:  .Font.Bold = True:  End With
                End If
            End If
        Next C, R
    End With
End Sub
 
Upvote 0
Solution
Hi, a VBA demonstration for starters :​
VBA Code:
Sub Demo1()
        Dim V, R&, C%
    With ActiveWorkbook.Sheets("Production Dbs").[A1].CurrentRegion
           .Range("I2:I" & .Rows.Count).Font.Bold = False
            V = .Columns("D:H").Value2
        For R = 2 To .Rows.Count
        For C = 3 To 5
            If Not IsEmpty(V(R, C)) Then
                If Dir(V(R, 1) & V(R, C)) > "" Then
                    FileCopy V(R, 1) & V(R, C), V(R, 2) & V(R, C)
                    With .Cells(R, 9):  .Value = Now:  .Font.Bold = True:  End With
                End If
            End If
        Next C, R
    End With
End Sub
Hi Marc,

Yes I know my code structure is not the greatest. I thank you for posting this solution. Any suggestions as to the best places to learn to structure your code? I will take a closer look at your code and will respond shortly.
 
Upvote 0
Thanks again for the solution. It works fine. Forgive me for the next couple of questions:

1. Can you explain how you dimensioned the variables V, R&, C%? I haven't seen this notation used before.
2. Your code shows that you are capturing the range for the entire used range whereas my code was only capturing the folders in column D. Can you tell me why you chose that route?

I appreciate your help in understanding. Thanks
 
Upvote 0
  1. According to BASIC character data type declaration as you can see in VBA help for Long for example …
    Without any character that means Variant and, as a reminder, during the execution you can check the variables via the VBE Locals window.

  2. In fact my VBA procedure captures only the necessary aka columns D:H values from the current region startin' in A1 …
 
Upvote 0
Hi Everyone!

Although the question has been answered and the code executes. I have more questions. Do I continue in this thread or start a new thread?

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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