Get last folder from .subfolder return (folders collection)

jtdewhurst

New Member
Joined
Jan 16, 2015
Messages
26
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
 

jtdewhurst

New Member
Joined
Jan 16, 2015
Messages
26
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

Well-known Member
Joined
May 10, 2011
Messages
2,153
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

New Member
Joined
Jan 16, 2015
Messages
26
Code:
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

Well-known Member
Joined
May 10, 2011
Messages
2,153
Try this code against your directory structure ( don't change your code)

is it any faster

Code:
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

New Member
Joined
Jan 16, 2015
Messages
26
I'll test it, I made a modification and got the code down to 15 sec for finding the folder names (from ~40 sec)



Code:
 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

New Member
Joined
Jan 16, 2015
Messages
26
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

Well-known Member
Joined
May 10, 2011
Messages
2,153
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
 

Forum statistics

Threads
1,081,860
Messages
5,361,734
Members
400,652
Latest member
cortexnotion

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top