How does one obtain a folder name using VBA?

[Treygor]

I need to obtain some folder names. What code is used to obtain a folder name? Lets say the following folders exist:

Main Folder
Sub Folder 1
sub-sub folder a.1
Sub Folder 2
sub-sub folder a.2
Sub Folder 3
sub-sub folder a.3


What VBA code could be used to tell the user that within the Main Folder, there are three sub folders and these sub folder names are "Sub Folder 1", "Sub Folder 2", and "Sub Folder 3"? Any help would be much appreciated.

-Treygor

[XLGibbs]

http://vbaexpress.com/kb/getarticle.php?kb_id=284&PHPSESSID=22e3a1ba0b4aa421db2a11355307876b (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=284&PHPSESSID=22e3a1ba0b4aa421db2a11355307876b)You

may benefit from that KB entry by Ken Puls.

The function opens up a browser window where the user can select a folder and the path gets returned. Not sure why you want the users to know the information in your post unless the intent was to USE it somewhere ...

This simplifies the process a great deal if the intent is to let a user know what is where.

You might also check this one by me which uses Kens code:
http://vbaexpress.com/kb/getarticle.php?kb_id=837 (http://vbaexpress.com/forum/../kb/getarticle.php?kb_id=837)

which is a template and code which creates an excel file showing a folder and all of its files, with the full path, modified dates, last accessed etc.

[Treygor]

XLGibbs,
Thanks for the reply. I took a look at Ken's information and your information but I don't believe that is what I am looking for. Both references ask the user to specify the folder or location to look in.

I would rather just get a list of folders and subfolders names and then place these names into an array. If there is simple code that would allow me to do this, I am not aware of it. I would think that there is some kind of code similar to:
object.FolderName
but I have not found anything yet. Again, any help is appreciated.


-Treygor

[XLGibbs]

It wasn't intended to be a solution for you, but again..the real purpose of placing these names into an array becomes a question.

The FSO method I used can possibly be modified to extract only the folders and sub folders. My thought was you could use Ken's code to specify the parent folder, and the code go get any subfolders in that folder.

[Ken Puls]

How about using FSO to do it?

Sub ListFolders()
'Macro purpose: To create an array of subfolder names

Dim fso As Object, _
objMainFolder As Object, _
objSubFolder As Object
Dim x As Long
Dim aryFolderNames As Variant
Dim sDir As String

'Set folder path here
sDir = "F:\Excel Projects In Process"

'Create objects to hold the main folder object
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set objMainFolder = fso.GetFolder(sDir)

'Redimension the array to hold the folder names
ReDim aryFolderNames(1 To objMainFolder.subfolders.Count)

'Loop through all subfolders and record their names
For Each objSubFolder In objMainFolder.subfolders
x = x + 1
aryFolderNames(x) = objSubFolder.Name
Next objSubFolder
On Error GoTo 0

'Do something with the array here
For x = 1 To UBound(aryFolderNames)
Debug.Print aryFolderNames(x)
Next x
End Sub

[tstom]

Ken. At a glance, it appears that your code will only gather second level folder info. This is pretty much the same code posted here...
http://vbaexpress.com/forum/showthread.php?t=10929


Option Explicit

Dim Folders() As Object

Sub Example()
Dim Root As Object
ReDim Folders(0)
Set Root = CreateObject("Scripting.FileSystemObject").GetFolder("C:\Documents and Settings\Tom\dESKTOP")
Set Folders(0) = Root
GetFolders Root
Test
End Sub

Sub Test()
Dim x As Long

For x = LBound(Folders) To UBound(Folders)
Debug.Print Folders(x).Path
Debug.Print Folders(x).Size
Debug.Print "-------------------------------------------------------"
Next
End Sub

Sub GetFolders(Root As Object)
Dim sf As Object
For Each sf In Root.SubFolders
GetFolders sf
ReDim Preserve Folders(UBound(Folders) + 1)
Set Folders(UBound(Folders)) = sf
Next
End Sub

[Ken Puls]

Ah!

Tom, you're quite correct. I didn't read the original question close enough. My code does only gather the first level.

Thanks for posting! :)