Get last folder from .subfolder return (folders collection)

[jtdewhurst]

I'm trying to find a way to get the last subfolder in a folder object without out using a “For Each” Loop. It seems to me that if the Folders Collection is stored in the memory so that in can be accessed through iteration, than there has to be a way to simply call one without looping through. I have code that works by looping through, but it takes a long time, and the code it is part of is already going to take a long time to execute. What I really want to do is to use the count function to get the number of folders then call the last folder like an entry in an array.
I’m sorry that this is likely a little confusing and atypical.
Thanks

 

[CharlesChuckieCharles]

are the folders numbered? or in some order that says the last folder is a specific name?

 

[jtdewhurst]

are the folders numbered? or in some order that says the last folder is a specific name?

The names of the folders are numbers starting somewhere around 0040 and going into the thousands, but I'm doing this for 27 different directories, so I don't know each time what the number of the last folder is. I have code that gets the last folder by looping through, but it takes a decent amount of time, and I'll be doing this 27 times, with a lot more to do than just loop through.

Thanks

 

[CharlesChuckieCharles]

maybe you should post your code that carries out the looping search, since a folder item itself is tiny a couple of hundred bytes at most

 

[jtdewhurst]

For Each objSubFolder In objFolder.SubFolders
                STRT_FLDR_NME = objSubFolder.Name
                Exit For
            Next objSubFolder
             
            For Each objSubFolder In objFolder.SubFolders
                If objSubFolder.Name <> "obs" Then
                    LAST_FLDR_NME = objSubFolder.Name
                End If
            Next objSubFolder

 

[CharlesChuckieCharles]

Try this code against your directory structure ( don't change your code)

is it any faster

Sub MyTest()
Dim sHoldLastDir As String
Dim TestForDir As String
MyPath = "c:\temp\"
TestForDir = Dir(MyPath, vbDirectory)
While Not TestForDir = ""
     
     If GetAttr(MyPath & TestForDir) = vbDirectory Then
            Debug.Print TestForDir
            sHoldLastDir = TestForDir
    End If
    TestForDir = Dir
   
Wend
MsgBox "Finisihed on dir " & sHoldLastDir
End Sub

This avoids the use of Objects because the mass creation of Objects has a certain overhead that older methods don't

Obviously this will give you a list of sub directories and a message giving the last one found ( but will it be the actual last one ?)

 

[jtdewhurst]

I'll test it, I made a modification and got the code down to 15 sec for finding the folder names (from ~40 sec)

 FLDRS = objFolder.SubFolders.Count
            ReDim PRT_FLDRS(FLDRS - 1) As Long
            n = 0
            For Each objSubFolder In objFolder.SubFolders
                n = n + 1
                If FLDRS - n > 0 Then
                    PRT_FLDRS(n) = objSubFolder.Name
                End If
            Next objSubFolder

            STRT_FLDR_NME = PRT_FLDRS(1)
            LAST_FLDR_NME = PRT_FLDRS(n - 1)

 

[jtdewhurst]

I just found out, I'll be getting a loaner laptop for running my code, so it is no longer worth (company money wise) my trying to shave off this time.
Thanks for the help though (when I get a chance I'll run that code, in case someone comes later looking for an answer)

 

[CharlesChuckieCharles]

Just to say

MsgBox objFolder.SubFolders(objFolder.SubFolders.Count).Name

will give you the last folder name
( since you don't appear to be sorting them, they must be in the right order )

There is no need to loop

 

[CharlesChuckieCharles]

My Last post was rubbish looks like it should work but doesn't !!