Opening files from a combo box

[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

 

[Dave Peterson]

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

 

[michaelberrier]

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

 

[Ivan F Moala]

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