Combine recursive listing with excluded code

**Paul_Hossler** · 05-20-2021, 05:00 PM

Open the Immediate Window and enter

?p.Name+Enter

The fact that it works on my PC and not on yours makes me wonder if there's something about the data

**anmac1789** · 05-20-2021, 07:10 PM

Well what I did was change the drive letter of D into C to suit my computer path that's it. Also, why is there a list in column I, K and M...looking at the code the exclude list was in column C, D and E.

?p.Name+Enter

returns an error run time error 424: object required. It doesn't work for any parent folder regardless where it's located even other parent folders with shorter folder paths

**Paul_Hossler** · 05-21-2021, 05:46 AM

what I did was change the drive letter of D into C to suit my computer path

I recall that some user folders can't be in the root of C:

Also, why is there a list in column I, K and M...looking at the code the exclude list was in column C, D and E.

I just moved them out of the way

**anmac1789** · 05-21-2021, 11:25 AM

I recall that some user folders can't be in the root of C:

The problem is that it doesn't work for any folder even within the Downloads folder

**Paul_Hossler** · 05-21-2021, 01:19 PM

Can you attach the failing workbook?

Are you using the same 'very long folder path.zip' from post 91?

I used

\\?\c:\Users\Daddy\Downloads\very long folder path

without problems

**anmac1789** · 05-21-2021, 02:10 PM

I am getting the same error message again. I downloaded the exact same workbook that you have attached in the previous post.

Are you using the same 'very long folder path.zip' from post 91?

I am also using the same zip file and testing it out on that and also on smaller folders on my computer and still i am getting the same runtime error 5 msg

run time error.PNG
highlight.jpg

**Paul_Hossler** · 05-21-2021, 05:36 PM

1. For now, remove the UCase( ) around p.path and see if the error goes away

2. Try this also

https://newtonexcelbach.com/2014/03/...-lcase-in-vba/

So mosey on over to Tools > References, and look for any library prefixed with “MISSING”.

That’s the real culprit.

If this doesn’t fix the problem, then close Excel, clean out the temp directory, clean out %appdata%\Roaming\Microsoft\Forms, then restart Excel. If that doesn’t help you probably need to repair/reinstall Office.

**anmac1789** · 05-21-2021, 05:46 PM

1. For now, remove the UCase( ) around p.path and see if the error goes away

I've removed that from isFolderExcluded sub, isFileExcluded sub and isSpecificFileExcluded sub. Still getting the same error

**Paul_Hossler** · 05-21-2021, 06:31 PM

Well ...

put the UCase (..) back and try the second suggestions

If that doesn't work, then I'm really out of ideas since it works here.

**anmac1789** · 05-21-2021, 07:57 PM

I've tried pre-fixing vba before Ucase and it still doesn't work just like the website suggests.. so im guessing im going to re-install office. Because I was hit with a bad virus yesterday and I was scanning for 6 hrs and it turned up like 105 trojans and alot of files were affected in the temp folder. So im going to try that now.

**anmac1789** · 05-22-2021, 06:47 PM

So i re-installed excel 365 and tried to run Excludes_20.xlsm again but still I am getting run time error 5. I was looking on this website: https://www.mrexcel.com/board/thread...gument.825523/

it says

the worksheet function Match requires a range object as the second argument, not an array

Could this have anything to do with it ?

**snb** · 05-23-2021, 02:08 AM

Sub M_snb()
   sn = Split("That is not correct")
   MsgBox Application.Match("not", sn, 0)
End Sub

Sub M_snb()
  MsgBox Application.Match("not", Split("this is not correct"), 0)
End Sub

**anmac1789** · 05-23-2021, 07:52 AM

Sub M_snb()
sn = Split("That is not correct")
MsgBox Application.Match("not", sn, 0)
End Sub

By itself, doesn't work because I have Option Explicit. But if I add Dim sn As Variant then the sub works. The second one also works without having to define split with dim

**Paul_Hossler** · 05-23-2021, 01:24 PM

Originally Posted by anmac1789

So i re-installed excel 365 and tried to run Excludes_20.xlsm again but still I am getting run time error 5. I was looking on this website: https://www.mrexcel.com/board/thread...gument.825523/

it says Could this have anything to do with it ?

Doubt it

Match works with arrays and it files an excluded folder

Capture.JPG

**anmac1789** · 05-23-2021, 07:54 PM

which directory path are you testing this on ? I have done ?isarray(aryFoldersToExclude) and it returns false the error message. I also tested aryFoldersToExclude in the watch window and the value returned "out of context" and type - "empty".

So I've tested this on another computer and it seems to work. It seems that even after re-installing excel the code on my laptop doesn't work. What is going on here....

Also, it seems that in the sub isSpecificFileExcluded and isFolderExcluded the arguement inside UCase() should be changed to p.Path not p.name because paths are not being excluded properly, they were only excluding subfolder name and filename

here is the problem workbook so you can see for yourself if there are any changes compared to your workbook

**Paul_Hossler** · 05-24-2021, 05:43 AM

The way it (is intended) to work is below

It does seem to do what it's supposed to

1. If isFolderExcluded(oPath) returns True, then the recursion down that folder path stops

2. If isFileExcluded(oFile) or isSpecificFileExcluded(oFile) return True, then the file is not added


Sub getFiles(oPath As Object)
    Dim oFolder As Object, oSubFolder As Object, oFile As Object


    If isFolderExcluded(oPath) Then Exit Sub  '   stops recursion
        
    Call addFileFolder(oPath)
    
    For Each oFile In oPath.Files
        If Not isFileExcluded(oFile) Then
            If Not isSpecificFileExcluded(oFile) Then Call addFileFolder(oFile)
        
        Else
            If Not isSpecificFileExcluded(oFile) Then Call addFileFolder(oFile)
        End If
    Next
    
    For Each oSubFolder In oPath.SubFolders
        Call getFiles(oSubFolder)
    Next
End Sub

The way you described what you wanted to do, the 2 'File' excludes were based on the file .Name so I think that it's correct

because paths are not being excluded properly, they were only excluding subfolder name and filename

I was excluding folders from 'hell yeah' and below (ver 20a)

**Paul_Hossler** · 05-24-2021, 05:50 AM

Originally Posted by anmac1789

which directory path are you testing this on ? I have done ?isarray(aryFoldersToExclude) and it returns false the error message. I also tested aryFoldersToExclude in the watch window and the value returned "out of context" and type - "empty".

So I've tested this on another computer and it seems to work. It seems that even after re-installing excel the code on my laptop doesn't work. What is going on here....

Also, it seems that in the sub isSpecificFileExcluded and isFolderExcluded the arguement inside UCase() should be changed to p.Path not p.name because paths are not being excluded properly, they were only excluding subfolder name and filename

here is the problem workbook so you can see for yourself if there are any changes compared to your workbook

Correcting / changing the two marked lines and entering

A1 = \\?\c:\users\daddy\downloads\very long folder path
C1 = hell yeah

seems to work

Private Function isFolderExcluded(p As Object) As Boolean
    Dim i As Long
    
    i = -1
    On Error Resume Next
    i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0)  '   <<<<<<<<<<<<<<<<<<<<<<<<<
    On Error GoTo 0


    isFolderExcluded = (i <> -1)
End Function


Private Function isFileExcluded(p As Object) As Boolean
    Dim i As Long
    
    i = -1
    On Error Resume Next
    i = Application.WorksheetFunction.Match(UCase(p.Name), aryFilenamesToExclude, 0)
    On Error GoTo 0


    isFileExcluded = (i <> -1)


End Function


Private Function isSpecificFileExcluded(p As Object) As Boolean
    Dim i As Long
    
    i = -1
    On Error Resume Next
    i = Application.WorksheetFunction.Match(UCase(p.Name), arySpecificFilesToExclude, 0)    '   <<<<<<<<<<<<<<<<<<<<<<
    On Error GoTo 0


    isSpecificFileExcluded = (i <> -1)


End Function

**anmac1789** · 05-24-2021, 09:43 AM

I dont know why the above code was re-written I did not see any change. It is the same exact code in Excludes_20a and I also did a test just to list a parent folder and all it's files without any excludes, I am still getting run-time error 5: invalid procedure call or argument on

i = Application.WorksheetFunction.Match(UCase(p.Name), aryFoldersToExclude, 0)

**Paul_Hossler** · 05-24-2021, 01:06 PM

Again, it works for me

One thing to check ...

In the VBE go to Tools, Options, and make sure "Unhandled Errors" is selected

Capture.JPG

Also I added a check to fail softly if there was no top level folder specified

In your 20 (Problem file) it was blank and generated an ugly error

**anmac1789** · 05-24-2021, 01:56 PM

In the VBE go to Tools, Options, and make sure "Unhandled Errors" is selected

Ok so, it seems like now it searches. What does "break on unhandled errors" mean? does it mean that it's ignoring run time error 5 and just forcing itself to proceed to complete the code?

Thread: Combine recursive listing with excluded code

Thread Tools

Display

Posting Permissions