Excel macro stopped copying folders. Getting error that path cannot be found?

user_16249257 0
Viewed 160

I have been troubleshooting for a few hours what should be a simple bit of code. I have a sub (CopyPasteTemplate) that is called from another sub on a userform within Excel. It is designed to check for a local folder (fromFolder) and copy the folders contained within to another folder (toFolder) on the same drive. I have run it hundreds of times with no problems until today. I did add a bit of code today to the userform, but did not modify the sub in question...at least not intentionally. Two variables are passed to the sub, but I verified they are being passed correctly. I inserted a bit of troubleshooting code to see if the file paths are being stored correctly. See below.

Private Sub CopyPasteTemplate(path As String, iPos As String)
   '--------------------
   'Copies folders from "0_Template Job Folder" ("fromFolder") to project folder ("toFolder") being created
   '--------------------
    Dim FSO
    Dim sFolder As String
    Dim fromFolder As String
    Dim toFolder As String
                
    sFolder = "0_Project Folder Template" 'This is the folder name with the contents you want to copy
                
    fromFolder = Left(LocalFullName(ActiveWorkbook.FullName), iPos) & sFolder & "\*" 'Location of folder(s) you want to copy"
                
    toFolder = path 'Change to match the destination folder path
                
    Set FSO = CreateObject("Scripting.FileSystemObject") 'Create Object
            
'-------------this section created to determine which path cannot be found----------------------
    Debug.Print "fromFolder->" & fromFolder
    Debug.Print "toFolder->" & toFolder
    
    If FSO.FolderExists(fromFolder) = False Then
            Debug.Print "fromFolder found->" & fromFolder
    Else
            Debug.Print "fromFolder found->" & "not found"
    End If
    
    If FSO.FolderExists(toFolder) = False Then
            Debug.Print "toFolder found->" & toFolder
    Else
            Debug.Print "toFolder found->" & "not found"
    End If
'----------------------------------------------------------------------------------------------
            
    If Not FSO.FolderExists(Left(LocalFullName(ActiveWorkbook.FullName), iPos) & sFolder) Then 'Checking If File Is Located in the Source Folder
         MsgBox "Specified Folder Not Found", vbInformation, "Not Found"
    Else
         FSO.CopyFolder (fromFolder), toFolder, True
         MsgBox "Specified Folder Copied Successfully", vbInformation, "Done!"
    End If
            
End Sub

As you can see by the debug printout the file path for the toFolder and fromFolder are correct, but FSO.FolderExists cannot find the toFolder path.

enter image description here

What am I missing? Is there anything aside from modifying the code that could have changed? Frankly I am stumped. Any advice would be appreciated.

filesystemobject path vba excel
1 Answers

You say

If FSO.FolderExists(fromFolder) = False Then
    Debug.Print "fromFolder found->" & fromFolder
'…

that means: "If the folder does NOT exist" then print that you found it. But it has to be the other way around. If it exists then print it. So you need to check for True not False.

If FSO.FolderExists(fromFolder) = True Then
    Debug.Print "fromFolder found->" & fromFolder
Else
    Debug.Print "fromFolder found->" & "not found"
End If

If FSO.FolderExists(toFolder) = True Then
    Debug.Print "toFolder found->" & toFolder
Else
    Debug.Print "toFolder found->" & "not found"
End If

So that means that actually your toFolder was found but your fromFolder was not found. And that is because it contains \* in the end. Remove that and it will be found too.

Investigations

I created 2 folders in C:\Temp\test folder:

  • "C:\Temp\test folder\1" (containing some data to copy)
  • "C:\Temp\test folder\2 -" (empty destination, the path ends right after the dash!)

Then I set up the following test

Sub test()
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Dim FromFolder As String
    FromFolder = "c:\temp\test folder\1"
    
    Dim ToFolder As String
    ToFolder = "c:\temp\test folder\2 -   "  'the path has trailing spaces after the dash so it should not exist on the disk!
    
    Debug.Print "Found FromFolder:", FSO.FolderExists(FromFolder), """" & FromFolder & """"
    Debug.Print "Found ToFolder:  ", FSO.FolderExists(ToFolder), """" & ToFolder & """"
    
    FSO.CopyFolder FromFolder, ToFolder, True
End Sub

The outcome of this is

Found FromFolder:           Wahr          "c:\temp\test folder\1"
Found ToFolder:             Wahr          "c:\temp\test folder\2 -   "

So actually FSO.FolderExists(ToFolder) returns True even if this folder does actually not exist. Looks like it is ignoring the trailing spaces and finds "c:\temp\test folder\2 -" (without the spaces) that does exist!

At the same time FSO.CopyFolder FromFolder, ToFolder, True fails with "Path not found". Looks like it does not ignore the trailing spaces!

So I recommend to do the following ToFolder = Trim(ToFolder) to trim off any spaces at the end or beginning of the path. Or in your code toFolder = Trim(path).

user_32196130
Related