none
LIST SUB FOLDER NAMES IN COMBOBOX RRS feed

  • Question

  • Been using the below code to list 'files' from a folder in a combo box - works fine.

    how do I modify to show sub-folders in the folder?

     
    Public Sub FILELIST() 'LOADS FILE DIRECTORY INTO COMBOBOX
     
    Application.ScreenUpdating = False
        ComboBox1.Clear
        MYPATH = MYPATHIS()
        
         strfolder = MYPATH
        Dim strFile As String
        Dim oDictionary As Object
        Dim vArray As Variant
        Dim v As Variant
       
        Set oDictionary = CreateObject("Scripting.Dictionary")
        strFile = Dir(strfolder & "*.xlsx")
        While strFile <> ""
            oDictionary(strFile) = strFile
            strFile = Dir
        Wend
       
        vArray = Sort(oDictionary.ITEMS)
        For Each v In vArray
      
       
           ComboBox1.AddItem v
        Next
       
        If ComboBox1.ListCount = 0 Then
        MsgBox "NO FILE FOUND"
       
        Else
     
        ComboBox1.ListIndex = 0
        ComboBox11.ListIndex = 0
       
        End If

    End Sub

    thanks for any advise

    Doug

    Thursday, December 19, 2019 6:57 PM

Answers

  • this solves my issue

    Private Sub CommandButton25_Click()
    MYPATH = MYPATHIS()
    Dim name
      For Each name In ListDirectory(Path:=(MYPATH), AttrInclude:=vbDirectory, AttrExclude:=vbSystem Or vbHidden)
     
      If name <> "." And name <> ".." Then
      Me.ComboBox16.AddItem name
      End If
      Next name
    End Sub
    Function ListDirectory(Path As String, AttrInclude As VbFileAttribute, Optional AttrExclude As VbFileAttribute = False) As Collection
      Dim Filename As String
      Dim Attribs As VbFileAttribute
      Set ListDirectory = New Collection
      ' first call to Dir() initializes the list
      Filename = Dir(Path, AttrInclude)
      While Filename <> ""
        Attribs = GetAttr(Path & Filename)
        ' to be added, a file must have the right set of attributes
        If Attribs And AttrInclude And Not (Attribs And AttrExclude) Then
          ListDirectory.Add Filename, Path & Filename
        End If
        ' fetch next filename
        Filename = Dir
      Wend
    End Function

    • Marked as answer by 6da4 Friday, December 20, 2019 2:09 PM
    Friday, December 20, 2019 2:09 PM

All replies

  • Hi

    You already using "Scripting" library. You can use FileSystemObject and File object.

    A basic code

    Dim aa As New Scripting.FileSystemObject
    Dim fil As Scripting.File
    dim fold as scripting.folder
    
    Set fold = aa.GetFolder(strfolder)
    
    For Each fil In fold.Files
    MsgBox fil.Name
    Next fil
    



    Best Regards, Asadulla Javed

    Friday, December 20, 2019 5:49 AM
    Answerer
  • this solves my issue

    Private Sub CommandButton25_Click()
    MYPATH = MYPATHIS()
    Dim name
      For Each name In ListDirectory(Path:=(MYPATH), AttrInclude:=vbDirectory, AttrExclude:=vbSystem Or vbHidden)
     
      If name <> "." And name <> ".." Then
      Me.ComboBox16.AddItem name
      End If
      Next name
    End Sub
    Function ListDirectory(Path As String, AttrInclude As VbFileAttribute, Optional AttrExclude As VbFileAttribute = False) As Collection
      Dim Filename As String
      Dim Attribs As VbFileAttribute
      Set ListDirectory = New Collection
      ' first call to Dir() initializes the list
      Filename = Dir(Path, AttrInclude)
      While Filename <> ""
        Attribs = GetAttr(Path & Filename)
        ' to be added, a file must have the right set of attributes
        If Attribs And AttrInclude And Not (Attribs And AttrExclude) Then
          ListDirectory.Add Filename, Path & Filename
        End If
        ' fetch next filename
        Filename = Dir
      Wend
    End Function

    • Marked as answer by 6da4 Friday, December 20, 2019 2:09 PM
    Friday, December 20, 2019 2:09 PM
  • decided to go with code I posted and marked as answer but I very much appreciate your reply.

    thanks

    Doug

    Friday, December 20, 2019 2:12 PM