Solved: Select File from Form

[kathyb0527]

I'm in the "planning" stage of a project in which I need to grab data from 4 different workbooks. However, before I get the data , each workbook needs to be "fixed". I was wondering if it is be possible and eaiser to use a form and have the user select the workbooks first rather than have the scripts asking the user to open the workbook as it comes up. I've only done some very simple forms so I'm thinking it would be a combo box and the code would be application.getfilename? Any help and opinions would be appreciated.

[mancubus]

hi,

below procedure enables you to populate Combobox1 in a userform with all excel files' names from specified folder.


Private Sub UserForm_Initialize()
'http://www.mrexcel.com/forum/showthread.php?t=4137

Dim cb As MSForms.ComboBox
Dim F
Dim x As Integer

Set cb = Me.ComboBox1
cb.Clear

F = Dir("C:\Users\me\Documents\xlFiles\*.XLS*") 'change to suit
If F = "" Then MsgBox "Directory doesn't exist!": GoTo Ex

Application.ScreenUpdating = False

x = 1
On Error GoTo FileErr
Do While Len(F) > 0
cb.AddItem F
x = x + 1
F = Dir()
Loop

GoTo Ex
Exit Sub
FileErr:

MsgBox Err.Number & Chr(13) & _
Err.Description, _
vbMsgBoxHelpButton, _
"File load Error", _
Err.HelpFile, _
Err.HelpContext

Ex:
Application.ScreenUpdating = True

End Sub

[mdmackillop]

You could use a Listbox with a MultiSelect option. That way the user can select all 4 workbooks and you could "fix" them in turn.

[kathyb0527]

Thanks for the quick responses! Mancubus, is there a way to modify the code to allow the user to select the folder (like a browse)? mdmackillop, I haven't used a Listbox with Multiselect, can you explain a little more on how it can be used?

Thanks!

[mdmackillop]

A simple example

[mancubus]

you are wellcome.


http://www.vbaexpress.com/kb/getarticle.php?kb_id=246
download the file here and copy the Module1 to your project.

then replace
F = Dir("C:\Users\me\Documents\xlFiles\*.XLS*")

with
F = BrowseFolder("Please Select a Folder")

[kathyb0527]

Great! Thank you so much. This helps tremendously!