Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

Hey Rick, thanks for the quick response. Your suggestion is a good one, but
not quite what I want to do. This user needs to look for specific files that
are exported out of Mainframe so I wanted to make it as easy as possible for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.

You could try this... Assuming your Mainframe files are all delivered to a
fixed directory (assumed to be "c:\temp\" for my example below; change it as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as possible
for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Rick, this code does work in both Excel 2000 and Excel 2002. However, I'm
getting an error when the selected file tries to open. I don't think the
path is being included so the file can't be found. The error is on
Workbooks.Open FileName. I added a messagebox prior to that line and I get
only the file name and not the path. I tried making a few changes but they
didn't work. Help? This is exactly what I need and it's very slick. If you
can help me make it work, this would be awesome!

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me

"Rick Rothstein (MVP - VB)" wrote:

You could try this... Assuming your Mainframe files are all delivered to a
fixed directory (assumed to be "c:\temp\" for my example below; change it as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as possible
for
her. Any ideas how I can display selected file names in an Input Box and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Did you copy/paste the code I gave you exactly as I posted it? If you did,
the path **had** to be attached to the filename because it was prepended in
the ListBox1_Click event. The key to making this work is to have the Dim
statements for the Path and FileName be declared **only** in the
(General)(Declarations) section of the UserForm's code window and **not**
within any other procedures. If you declared them inside a procedure, then
that declaration for them (inside that procedure) would override the
"global" declaration that occurred by placing the Dim statements in the
(General)(Declarations) section.

To see how the code should work, open up a **new** copy of Excel, add the
UserForm, put the ListBox and CommandButton on it, and copy/paste this into
the UserForm's code window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Don't forget to change the Path assignment to that for your actual
worksheets. Then open up the code window for one of the worksheets and
copy/paste this into its code window...

Sub Test()
UserForm1.Show vbModeless
End Sub

Just place your cursor on one of the statements above and click the Run
button. Everything should work as I originally described (the code above is
the same as I originally posted). If, for some reason, it still doesn't
work, put a MessageBox in front of the Workbooks.Open statement and have it
show you what is in the Path variable. If you can see the Path as you
originally set it, you can do a quick fix by changing the Workbooks.Open
statement to this...

Workbooks.Open Path & FileName

but that would mean something was still screwed up somewhere else and you
really ought to find and correct it.

Rick


"cottage6" wrote in message
...
Rick, this code does work in both Excel 2000 and Excel 2002. However, I'm
getting an error when the selected file tries to open. I don't think the
path is being included so the file can't be found. The error is on
Workbooks.Open FileName. I added a messagebox prior to that line and I
get
only the file name and not the path. I tried making a few changes but
they
didn't work. Help? This is exactly what I need and it's very slick. If
you
can help me make it work, this would be awesome!

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me

"Rick Rothstein (MVP - VB)" wrote:

You could try this... Assuming your Mainframe files are all delivered to
a
fixed directory (assumed to be "c:\temp\" for my example below; change it
as
needed), add a UserForm to your project and put a ListBox and a
CommandButton on it. Then copy/paste this code into the UserForm's code
window...

Dim Path As String
Dim FileName As String

Private Sub UserForm_Initialize()
' Note the required trailing back slash
Path = "c:\temp\"
FileName = Dir(Path & "*.xls")
Do While Len(FileName) 0
ListBox1.AddItem FileName
FileName = Dir()
Loop
End Sub

Private Sub CommandButton1_Click()
Workbooks.Open FileName
Unload Me
End Sub

Private Sub ListBox1_Click()
FileName = Path & ListBox1.Text
End Sub

Now, to use this, simply execute this statement...

UserForm1.Show vbModeless

from within your own code whenever you want the user to open a file.

Rick


"cottage6" wrote in message
...
Hey Rick, thanks for the quick response. Your suggestion is a good
one,
but
not quite what I want to do. This user needs to look for specific files
that
are exported out of Mainframe so I wanted to make it as easy as
possible
for
her. Any ideas how I can display selected file names in an Input Box
and
then have the file she selects open?

"Rick Rothstein (MVP - VB)" wrote:

You could do something like this...

Sub OpenFile()
Dim lngCount As Long
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
.Execute
End With
End Sub

Look up the FileDialog Object in the help files as there are many
properties
you can use to customize how it functions.

Rick


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file
names,
which would then go ahead and open the selected file. Do I need to
start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a
good
day.

Does this functionality exist in Excel 2000?

I'm not sure... I only have XL2003 and XL2007 here. Hopefully someone with
earlier versions than I have will respond.

Rick


"dan dungan" wrote in message
...
Does this functionality exist in Excel 2000?

Try this

ifilenumber = FreeFile()
sfilename = Application.GetOpenFilename(, , "Enter name of file:")
If sfilename = False Then Exit Sub
msgbox sfilename


"cottage6" wrote in message
...
Hello everyone,
I'd like an Input Box that lets a user choose from a list of file names,
which would then go ahead and open the selected file. Do I need to start
with
an input validation list box? I'm really unsure how to put this all
together, and any help would be very appreciated! Thanks and have a good
day.