Load folder names into ComboBox. My code is loading file names.

[jmpatrick]

Good morning!

I'm using this code to load Folder names into a ComboBox:

    Dim MyFile As String
    Dim MyPath  As String
    MyPath = "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\"
    MyFile = Dir(MyPath)

    Do While MyFile <> ""
    Archive_Open_Year.AddItem MyFile
    MyFile = Dir
    Loop

Unfortunately it's only loading File names that exist in \\GLC-SERVER\Pulte\Z_Pulte Master Archive\. Can I make this load only Folder names that exist in \\GLC-SERVER\Pulte\Z_Pulte Master Archive\?

jp

 

[Domenic]

I would suggest using FileSystemObject instead. It has the FolderExists method to check whether a folder exists, and you can easily loop through subfolders within a folder. And, unlike the Dir function, it won't error out when the specified network doesn't exist. Try something like this...

    Dim fso As Object
    Dim MySubFolder As Object
    Dim MyPath  As String
    
    MyPath = "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    If Not fso.FolderExists(MyPath) Then
        MsgBox "'" & MyPath & "' does not exist!", vbExclamation
        Exit Sub
    End If
    
    For Each MySubFolder In fso.GetFolder(MyPath).SubFolders
        Archive_Open_Year.AddItem MySubFolder.Name
    Next MySubFolder

Hope this helps!

 

[jmpatrick]

I would suggest using FileSystemObject instead. It has the FolderExists method to check whether a folder exists, and you can easily loop through subfolders within a folder. And, unlike the Dir function, it won't error out when the specified network doesn't exist. Try something like this...

    Dim fso As Object
    Dim MySubFolder As Object
    Dim MyPath  As String
   
    MyPath = "\\GLC-SERVER\Pulte\Z_Pulte Master Archive\"
   
    Set fso = CreateObject("Scripting.FileSystemObject")
   
    If Not fso.FolderExists(MyPath) Then
        MsgBox "'" & MyPath & "' does not exist!", vbExclamation
        Exit Sub
    End If
   
    For Each MySubFolder In fso.GetFolder(MyPath).SubFolders
        Archive_Open_Year.AddItem MySubFolder.Name
    Next MySubFolder

Hope this helps!

Works great. Thanks!

 

[Domenic]

You're very welcome, cheers!

 

[JEC]

Write your data into your listbox in 1 go, using an array

Private Sub UserForm_Initialize()
 Dim xp, fld, x As Long
 xp = "C:\Users\xxxx\Documents\"
 With CreateObject("scripting.filesystemobject")
    If .folderexists(xp) Then
      With .getfolder(xp)
         ReDim ar(.subfolders.Count)
         For Each fld In .subfolders
            ar(x) = fld.Name
            x = x + 1
         Next
      End With
     Archive_Open_Year.List = ar
    End If
 End With
End Sub