M
michaelberrier
I use the rather verbose code below to list .xls files in a specific
folder in a combo box. How do I format the combo box and/or Userform
to allow users to open the selected file?
Thanks for the help.
Sub Start()
Dim DirToSearch As String
UserForm2.ComboBox1.Clear
DirToSearch = "C:\Manifest\Manifest Archive\" 'THE FOLDER
GetFilesInDirectory DirToSearch
'remove ' in next line to include subfolders:
'LookForDirectories (DirToSearch)
UserForm2.Show
End Sub
Sub LookForDirectories(ByVal DirToSearch As String)
Dim counter As Integer
Dim i As Integer
Dim Directories() As String
Dim Contents As String
counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents <> ""
If Contents <> "." And Contents <> ".." Then
If (GetAttr(DirToSearch & Contents) And vbDirectory) =
vbDirectory Then
counter% = counter% + 1
ReDim Preserve Directories(counter)
Directories(counter) = DirToSearch & Contents
End If
End If
Contents = Dir
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
GetFilesInDirectory Directories(i)
LookForDirectories Directories(i)
Next i
End Sub
Sub GetFilesInDirectory(ByVal DirToSearch As String)
Dim NextFile As String
NextFile = Dir(DirToSearch & "\" & "*.xls")
Do Until NextFile = ""
UserForm2.ComboBox1.AddItem NextFile
NextFile = Dir()
Loop
End Sub
folder in a combo box. How do I format the combo box and/or Userform
to allow users to open the selected file?
Thanks for the help.
Sub Start()
Dim DirToSearch As String
UserForm2.ComboBox1.Clear
DirToSearch = "C:\Manifest\Manifest Archive\" 'THE FOLDER
GetFilesInDirectory DirToSearch
'remove ' in next line to include subfolders:
'LookForDirectories (DirToSearch)
UserForm2.Show
End Sub
Sub LookForDirectories(ByVal DirToSearch As String)
Dim counter As Integer
Dim i As Integer
Dim Directories() As String
Dim Contents As String
counter = 0
DirToSearch = DirToSearch & "\"
Contents = Dir(DirToSearch, vbDirectory)
Do While Contents <> ""
If Contents <> "." And Contents <> ".." Then
If (GetAttr(DirToSearch & Contents) And vbDirectory) =
vbDirectory Then
counter% = counter% + 1
ReDim Preserve Directories(counter)
Directories(counter) = DirToSearch & Contents
End If
End If
Contents = Dir
Loop
If counter = 0 Then Exit Sub
For i = 1 To counter
GetFilesInDirectory Directories(i)
LookForDirectories Directories(i)
Next i
End Sub
Sub GetFilesInDirectory(ByVal DirToSearch As String)
Dim NextFile As String
NextFile = Dir(DirToSearch & "\" & "*.xls")
Do Until NextFile = ""
UserForm2.ComboBox1.AddItem NextFile
NextFile = Dir()
Loop
End Sub