Get list of files/folders *fast*

[bshbsh]

Hi,

I'm using a recursive sub to get list of files within a folder (on a network server), using FileSystemObject. It is terribly slow. On a folder ocntaining 100-150 subfolders and maybe 5-6000 files, it takes a minute or two.

I have ported the exact same code to vb.net and using FileIO.Filesystem calls, which is waaaaaaaay faster, taking about a second.

Why is it so slow from Inventor VBA? And what is the fastest way to get such a filelist?

I've tried using default VBA file commands but they have problems with UTF8 filenames, so it's not usable.

code I'm using:

Public Sub GetFileList(ByRef Fcoll As Collection, ByVal CurrentFolder As String, ByVal Mask As String, ByVal Recursive As Boolean, Optional ByRef ExcludedFolders As Variant)
    Dim Excluded As Boolean
    Excluded = False
    If Not IsError(ExcludedFolders) Then
        For Each Item In ExcludedFolders
            If InStr(CurrentFolder, Item) <> 0 Then Excluded = True
        Next
    End If
    If Excluded = False Then
        For Each File In ThisApplication.FileManager.FileSystemObject.GetFolder(CurrentFolder).Files
            For Each Item In Split(Mask, "|")
                If File.Name Like Item Then Fcoll.Add File.Path
            Next
        Next
        If Recursive Then
            For Each Folder In ThisApplication.FileManager.FileSystemObject.GetFolder(CurrentFolder).SubFolders
                Call GetFileList(Fcoll, Folder.Path, Mask, Recursive, ExcludedFolders)
            Next
        End If
    End If
End Sub

vb.net:

    Public Sub GetFileList(ByRef FColl As Collection, ByVal CurrentFolder As String, ByVal Mask As String, ByVal Recursive As Boolean, Optional ByRef ExcludedFolders As Object = Nothing)
        Dim Excluded As Boolean
        Excluded = False
        If Not IsNothing(ExcludedFolders) Then
            For Each Item In ExcludedFolders
                If Strings.InStr(CurrentFolder, Item) <> 0 Then Excluded = True
            Next
        End If
        If Excluded = False Then
            For Each File In FileIO.FileSystem.GetFiles(CurrentFolder)
                For Each Item In Split(Mask, "|")
                    If FileIO.FileSystem.GetName(File) Like Item Then FColl.Add(File)
                Next
            Next
            If Recursive Then
                For Each Folder In FileIO.FileSystem.GetDirectories(CurrentFolder)
                    Call GetFileList(FColl, Folder, Mask, Recursive, ExcludedFolders)
                Next
            End If
        End If
    End Sub

[WCrihfield]

If using vb.net (or iLogic) I usually use System.IO.Directory.GetFiles(), which returns an array of String, instead of using FileIO.FileSystem.GetFiles(), which returns a ReadOnlyCollection(Of String).  And instead of using a 'recursive' routine, I simply set the SearchOption input variable to 1, if I want it to search through all sub-directories of the specified folder.  Maybe see if this speeds anything up any more for you.  I'm curious about what type of Object you are supplying to this Sub for the input variable "ExcludedFolders"?  It seams that if something in that variable is found within the "CurrentFolder" String, to then just not do anything else with the Sub (if I'm following your code correctly).

[bshbsh]

But I need to speed up the VBA side, from within Inventor. The problem is not in vb.net.

I use this to search for Inventor files, specifically ipt and iam, those are the extensions. ExcludedFolders is mostly OldVersions, or If I want to skip entire branches on the network, or something.

Here's a example call:

Dim FCollection As Collection
Set FCollection = New Collection
Set PFunctions = New PClass
Call PFunctions.GetFileList(FCollection, SourcePath, "*.ipt|*.iam", True, Array("\OldVersions"))

or this: (this one is more specific to our file storage rules.)

Call PFunctions.GetFileList(FCollection, SourcePath, "*.i*", True, Array("\OldVersions", "-Components"))

[WCrihfield]

One thought:

I did notice that in your VBA version of the code, you are using IsError(ExcludedFolders), while in your VB.NET version of the code, you are using IsNothing(ExcludedFolders).  Is this a mistake?  Is it possible a portion of the code wasn't functioning correctly, causing your Excluded (Boolean) variable to always return False in the VBA version, and therefore processing more stuff, where the VB.NET version wasn't.

Another thought:

It looks like this GetFileList Sub is maybe defined within a custom Class.  I know in VBA you have separate modules just for defining Class blocks of code, while in VB.NET you can just include it all in one larger block of code.  Could it just be that little extra work it has to do to get the definition of this Sub from the other Class module in VBA that is causing the extra processing time?

[bshbsh]

In VBA if you omit an optional argument, it will be "Missing", i.e. not even declared, and checking it with "is Nothing" throws an error, since the object is missing. That's why I check it with IsError. In .net if the oprional parameter is not given to a call, it will be declared as nothing and can be checked that way.

No it's not the class. It is instantiated once and then recursively calls itself. It's the same slowness if I declare the function within module itself.

[WCrihfield]

OK.  I'm running out of ideas, so I did a little searching online and came across this link, which looks promising.

https://stackoverflow.com/questions/31132775/vba-list-all-files-fast-way-in-subfolders-without-files...

I haven't tried it yet, but you can give it a shot if you want.  Let me know how it works out, if you do.