PDA

View Full Version : Solved: Select File from Form



kathyb0527
12-01-2011, 01:50 PM
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
12-01-2011, 02:25 PM
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
12-01-2011, 02:28 PM
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
12-01-2011, 04:21 PM
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
12-01-2011, 04:36 PM
A simple example

mancubus
12-02-2011, 12:38 AM
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
12-02-2011, 09:59 AM
Great! Thank you so much. This helps tremendously!