Opening files from a combo box

  • Thread starter Thread starter michaelberrier
  • Start date Start date
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
 
Without looking at your code at all, is there a reason you don't want to use
application.getopenfilename?

Option Explicit
Sub testme01()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename("Excel Files, *.xls")

If myFileName = False Then
Exit Sub
End If

'your code to open that file and do what you want.
End Sub
 
Dave,
Thanks for looking. The files the user will need will always be in the
same folder, and to simplify things I only want them only to open the
intended files.

Chip Pearson helped me with the code.

Thanks again.
mb
 
Try

[userform] code

Private Sub ComboBox1_Change()
If MsgBox("Open.." & ComboBox1.Value, vbYesNo) = vbYes Then
Workbooks.Open (DirToSearch & ComboBox1.Value)
End If
End Sub



[Amended code]

Public Const DirToSearch = "C:\Manifest\Manifest Archive\"


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
 
Back
Top