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

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

regards
FSt1

"br549" wrote:

> 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

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.

"FSt1" wrote:

> hi,
> see this site.
> http://j-walk.com/ss/excel/tips/tip29.htm
>
> regards
> FSt1
>
> "br549" wrote:
>
> > 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

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" <(E-Mail Removed)> wrote in message
news:FFF3B9A1-ACB2-4E1B-8AF4-(E-Mail Removed)...
>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

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

"Steve Yandl" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:FFF3B9A1-ACB2-4E1B-8AF4-(E-Mail Removed)...
> >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
>
>
>