directory listing in listbox

[br549]

I have a little experience manipulating data in Excel using VBA, but no
experience with userforms. Is there a relatively simple way to fill a listbox
with the contents of a folder so the user can be presented with a choice of
files that will be used for some calculations? Goal is for user to select a
file from the list box, then the VBA code will pull certain data out of that
file for a calculation.

Thanks,
Rob

[FSt1]

hi,
see this site.
http://j-walk.com/ss/excel/tips/tip29.htm

regards
FSt1

[br549]

The instructions at the referenced site are beyond my skills to understand.
It seems to be creating a custom function (something else I'm not familiar
with). Its not clear to me how I use this to get a filename to work with. If
there is not a simpler method I'll end up manually typing filenames to get
the data I need. Thanks for the reference though; maybe someday I will
figure it out.

[Steve Yandl]

Rob,

Is your plan to have the list of files available as soon as the userform is
activated or is the user going to do something else that will trigger the
listbox update (for example, selecting the folder where the files are)?

Is the user only going to be presented files in the folder or might there be
subfolders as well?

Steve Yandl

"br549" <br...@discussions.microsoft.com> wrote in message
news:FFF3B9A1-ACB2-4E1B...@microsoft.com...

[br549]

I finally found a solution that I could make work:

Sub PickFiles()
Dim lngCount As Long
' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show
' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
Worksheets("FileSelection").Select
Cells(lngCount + 8, 6).Value = .SelectedItems(lngCount)
Next lngCount
End With
End Sub

The user clicks a button that I assigned to this macro. Might not be a very
elegant solution, but works well enough for my application.
Thanks for the suggestions,
Rob