0

I´m using a code in VBA (Excel file) to read all the folders in a path and accessing such folders.
The program is crashing with error 53: File not found.

After some debug I realized that this happens when the folder name has special character (like 'é' or 'ó').
Apparently when forming a string with such characters the VBA replaces then by other things (like '´o'), this is leading to the subsequent crash because a folder with such modified name does not exist.
Is there any way to work this around?

Function GetFoldersIn(Folder As String) As Collection
  Dim F As String
  Set GetFoldersIn = New Collection
  F = Dir(Folder & "\*", vbDirectory)
  Do While F <> ""
    If (Right(F, 4) <> ".doc" And Right(F, 4) <> ".pdf") Then
        c = Folder + "\" + F
        a = GetAttr(c)
        b = vbDirectory
        If a And b Then GetFoldersIn.Add F
    End If
    F = Dir 'here is where the name of the folder is messed !!!!
  Loop
End Function

@userMT, Thanks for the reply! With the example you sent I went far. Now I'm stuck in another issue. I improved the code to perform a recursive search at the folder. It's working fine for small folders, i.e., folders with approximately fifty folders within (including all levels).

When I tried it in a quite big directory I have (approx. 3000 folders), I got this error:

Run-time error Method ‘Count’ of object ‘IFolderCollection’ failed.

After clicking “debug” on the error window, I watched the variable xFolder.SubFolders.Count, this is what I saw:

<Application-defined or object-defined error>.

Please note that the error appeared even after the code running successfully through several folders.

Function GetFoldersIn(Path As String) As Collection
    'Localiza todas
    Dim myFSO As FileSystemObject
    Dim xFolder As Scripting.Folder
    Dim xSubFolder As Scripting.Folder
    
    Set GetFoldersIn = New Collection
    Set myFSO = New FileSystemObject
    Set xFolder = myFSO.GetFolder(Path)
    
    GetFoldersIn.Add Path
    If xFolder.SubFolders.Count <> 0 Then 'Error is in this line !!!!!!!!!!
        For Each SubFolder In xFolder.SubFolders
            Set aux = GetFoldersIn(SubFolder.Path)
            For Each Item In aux
                GetFoldersIn.Add Item
            Next Item
        Next SubFolder
    End If
End Function
GSerg
  • 76,472
  • 17
  • 159
  • 346
nel_junior000
  • 45
  • 1
  • 1
  • 3
  • 1
    Have you tried the FilesystemObject for this (there is a usage example here: https://stackoverflow.com/questions/43367671/iterating-through-files-in-folder-via-filesystemobject)? – tinazmu Jan 05 '22 at 11:55
  • In testing I don't see any problem with that code. – Tim Williams Jan 05 '22 at 17:13
  • @TimWilliams, maybe the name of your folders have no special character. Please test it with a folder with “ó” for example). – nel_junior000 Jan 05 '22 at 19:15
  • When testing I copy-pasted both of those characters from your question into folder names and saw no problems in running the code. – Tim Williams Jan 05 '22 at 19:19

0 Answers0