fastest way to copy files/folders vba

Question

I've written a recursive script with a couple of if statements to append all files/folders in in vba using FSO but it takes forever, and I'm looking for other methodologies, or faster ways to append files. Is using the DIR or Call shell a faster way? Any reasoning would be appreciated.

Option Explicit

Sub BackUpEverything()

Dim Sourcefolder As String
Const DestinationFolder As String = "C:\Users\Person1\FolderX"


Dim i As Long
Dim copyfolders(3) As String
copyfolders(0) = "C:\Users\FolderA"
copyfolders(1) = "C:\Users\FolderB"
copyfolders(2) = "C:\Users\FolderC"
copyfolders(3) = "C:\Users\FolderD"


For i = 0 To 3

Sourcefolder = copyfolders(i)
backupfiles Sourcefolder, DestinationFolder

Next i

Mgsbox "Done"

End Sub
Sub backupfiles(Sourcefolder As String, DestinationFolder As String)


Dim FSO As filesystemobject
Dim oFile As File
Dim oFolder As Folder

Set FSO = New filesystemobject

If Not FSO.folderexists(DestinationFolder) Then FSO.Createfolder DestinationFolder
On Error Resume Next
For Each oFile In FSO.Getfolder(Sourcefolder).Files

If FSO.getextensionname(oFile.Path) <> "pdf" Then

FSO.copyfile oFile.Path, DestinationFolder & " \ " & oFile.Name

Else


End If

Next oFile

On Error Resume Next

For Each oFolder In FSO.Getfolder(Sourcefolder).SUbfolders

backupfiles oFolder.Path, DestinationFolder & " \ " & oFolder.Name

Next oFolder


End Sub

If you want help with your code, you need to show us your code. — Nicholas Hunter, May 02 '21 at 02:58
The code you posted has several problems including illegal characters, undefined variables, and who knows what else. I suggest you (1) put Option Explicit at the top of the module, select Debug / Compile VBA Project from the menu, and keep fixing the errors until there are no more errors. (2) Remove the two **On Error Resume Next** statements from the code. (3) Run the macro and let me know what happens. I can probably help you fix any remaining problems. — Nicholas Hunter, May 02 '21 at 13:36
@NicholasHunter What illegal characters? The reason for the ```On Error Resume Next``` was because I had unknown folders/files that gave me permission denied error. This was the only reason but I haven't found a better way to avoid these issues. — , May 02 '21 at 16:20
You'll find the syntax errors--the illegal characters, the unclosed quotations, etc--pretty quickly if you paste the code you posted above into a VBA module. You can identify the undefined variables by putting Option Explicit at the top of the module and select Debug / Compile VBA Project from the menu. The On Error Resume Next statements mean you are ignoring all the errors in all the code following that statement. Even if that's what you want to do, you only need do it once. If you want to ignore a specific error in a specific line, there is a much better way to do that. — Nicholas Hunter, May 02 '21 at 16:30
Oh okay I see.. I think those are mostly typos when I copied/reformated it here. It doesn't show an error when I run it..not sure if thats any different tho. I fixed one or 2 in the script here. More improtnatly, how do you go about skipping over a folder or file if you get error 70 permission denied? — , May 02 '21 at 16:40
I will be able to help you further when you've fixed up the code in your question as I suggested. — Nicholas Hunter, May 02 '21 at 17:02
I actually beleive that its getting stuck in the folder in which I had the permission denied, so not sure if the on error resume next is not doing the job really. But I'm not sure how to verify that either. Reason I say this is because you can see it quickly copies the first folder, then appears to have some trouble copying the other folder, then quickly copies the rest — , May 02 '21 at 17:54
@Philip `then appears to have some trouble copying the other folder` - Just how large is the file size of this folder? — Raymond Wu, May 03 '21 at 07:57
Its not very large I think its just one with permissions being deined so I need a custom error handler. 1st) to skip to next file in loop if error/permission denied 2nd) if continuing file is denied as well, back out of subfolder to next subfolder. But I don't know how to ```Goto``` and call next file or to backout to next subfolder or folder — , May 03 '21 at 12:32

Nicholas Hunter · Answer 1 · 2021-05-02T22:35:28.737

This turned out to be trickier than I thought and this may not be a complete solution, but you can give it a try and see if it works for you. I think part of the problem was creating the file system object every time you called the function. I moved the fso to the module level so the same one is used over and over again. That means you can't recurse while you're in the middle of iterating the subfolders, so instead I use fso to create a collection of subfolder paths and names. The error handling is focused on a single error in a single block of code.

Option Explicit
    
Private fso As New FileSystemObject

Sub backupFiles(ByVal sourceFolder As String, ByVal destinationFolder As String)

    Dim oFile As File
    Dim oFolder As Folder
    Dim subfolders As Collection
    Dim var As Variant
    
    ' Create destination folder if it does not already exist.
    If Not fso.FolderExists(destinationFolder) Then fso.CreateFolder destinationFolder

    '** COPY FILES IN THIS FOLDER
    
    ' Set custom error handler.
    On Error GoTo GetFolder_Error
    
    ' Copy everything except pdf files.
    For Each oFile In fso.GetFolder(sourceFolder).Files
        If fso.GetExtensionName(oFile.Path) <> "pdf" Then
            fso.CopyFile oFile.Path, fso.BuildPath(destinationFolder, oFile.Name)
        End If
    Next oFile
    
    ' Resume default error handling.
    On Error GoTo 0

    '** BACK UP SUBFOLDERS

    Set subfolders = New Collection
    
    ' Set custom error handler.
    On Error GoTo GetFolder_Error
    
    ' Add all subfolders paths and names to collection.
    For Each oFolder In fso.GetFolder(sourceFolder).subfolders
        subfolders.Add Array(oFolder.Path, oFolder.Name)
    Next oFolder
    
    ' Resume default error handling.
    On Error GoTo 0
    
    ' Iterate collection.
    For Each var In subfolders
        backup var(0), fso.BuildPath(destinationFolder, var(1))
    Next var
    
Exit_Sub:
    Exit Sub
    
GetFolder_Error:
    ' If permission denied, print message and exit sub.
    If Err.Description = "Permission denied" Then
        Debug.Print Err.Description
        Resume Exit_Sub
    Else
        ' Default VBA error handler.
        Err.Raise Err.Number
    End If

End Sub

If I read this correctly you exit sub after error? I'm looking for it to skip the file or folder and then continue on to the next file or folder — , May 02 '21 at 21:15
The Sub BackupEverything does not change so I didn't bother copying it. Yes the sub exits if it can not traverse the subfolder. Execution continues in the parent folder, exactly as in your original code. — Nicholas Hunter, May 02 '21 at 22:31
it doesn't like the err.raise err.number, I'm not really sure what that does. Also if you exit the sub wont that just skip all the other potential files within the respective folder that could have been copied? — , May 03 '21 at 01:18

fastest way to copy files/folders vba

1 Answers1