VBA Code to Back-Up Files with specified directories in MS Excel

[OilEconomist]

Thanks in advance for your assistance. How would I modify my code to back-up files from a specified directory to another? If the files exist, I would just like to replace them/merge with the latest one. The key here is would like to copy the entire directory path and its contents.

For example:

I would like to copy the following directory and its contents: C:\MPF\Screen Saver

to the specified directory: G:\01 HP C Drive

The final result for the back-up directory will be: G:\01 HP C Drive\MPF\Screen Saver

If that directory already exists, it would just merge the files with updated versions and any new files and directories.

This is my code, where I get the error: “Compile error Expected: =” on the following line:

Microsoft.VisualBasic.FileIO.FileSystem.CopyDirectory(DirPth_Src, DirPth_DstNew)

Option Explicit

Public Sub MC_XX_Backup_Folders()

    '_________________________________________________________________________________________________
    'Turn off alerts, screen updates, and automatic calculation
        'Turn off Display Alerts
            Application.DisplayAlerts = False
 
        'Turn off Screen Update
            Application.ScreenUpdating = False

        'Turn off Automatic Calculations
            Application.Calculation = xlManual


    '_________________________________________________________________________________________________
    'Dimensioning
        
        Dim i As Long
        Dim LastRowCol As Long
        
        Dim DirPth_Src As String
        Dim DirPth_Dst As String
        Dim DirPth_DstNew As String

    '_________________________________________________________________________________________________
    'Code
    
        Sheets("Folders.Back.Up").Activate
    
        LastRowCol = Cells(Rows.Count, 1).End(xlUp).Row
    
        If LastRowCol < 5 Then
            MsgBox "No directories to be backed-up are listed!"
            Exit Sub
        End If
 
        For i = 5 To LastRowCol
            DirPth_Src = Range("A" & i).Value
            DirPth_Dst = Range("B" & i).Value
            
            DirPth_DstNew = System.IO.Path.Combine(DirPth_Dst, Path.GetFileName(Path.GetDirectoryName(DirPth_Src)))
            
                If DirPth_Src = "" Or DirPth_Dst = "" Then
                    MsgBox "The source or destination directory is missing in one or more lines. Please correct. This program will terminate with no changes!"
                    Exit Sub
                End If
                
                If Not (Directory.Exists(DirPth_DstNew)) Then
                    Directory.CreateDirectory (DirPth_DstNew)
                End If
            
            Microsoft.VisualBasic.FileIO.FileSystem.CopyDirectory(DirPth_Src, DirPth_DstNew)
            
        Next i


    '_________________________________________________________________________________________________
    'Turn on alerts, screen updates, and calculate
        'Turn On Display Alerts
            Application.DisplayAlerts = True

        'Turn on Screen Update
            Application.ScreenUpdating = True

        'Turn off Automatic Calculations
            Calculate

    '_________________________________________________________________________________________________

End Sub

 

[rlv01]

Is this code that you have copied from a Visual Basic .NET application? I that call looks like something attempting to access a .NET library. You would do better to abandon that and recode the subroutine to use the VBA FileSystemObject which has a CopyFolder method.

 

[OilEconomist]

Is this code that you have copied from a Visual Basic .NET application? I that call looks like something attempting to access a .NET library. You would do better to abandon that and recode the subroutine to use the VBA FileSystemObject which has a CopyFolder method.

Hi @rlv01 and thanks for your response and guidance. It's a code I got off another post asking a similar question. I went through a lot of different sources (via a Google search) and pieced together what I have written in Post 1. The issue with using copy folders is that it just does that. Copy the folder only and not the path. I may try to see what happens when I use the combination of copying the directory and adding the folder to it. I just thought there might be a simpler method.

With what you have mentioned, I might be able to copy the directory to the destination and then copy the folder to the directory in the destination. What I have to test is what happens if the directory and folder already exist. Will it run into an issue? What about if the files and the directory were not changed at all. Will it want to recopy and replace everything or just update the added folders and files, and update the changed files? Would need to test all this.

 

[rlv01]

<<The issue with using copy folders is that it just does that. Copy the folder only and not the path.>>

Not sure where you are getting that from. The FileSystemObject CopyFolder method will copy contents. You will specify the path for both the source and destination folders.

<<Will it run into an issue? What about if the files and the directory were not changed at all. Will it want to recopy and replace everything or just update the added folders and files, and update the changed files?>>

This is the kind of thing you should refer to the help documentation for. Here's a starting point.

FileSystemObject object

Office VBA reference topic

docs.microsoft.com