Getting Creative
 

Hi

I need some help.

Is it possible to have a validation drop down list that is populated by the
file names of a pre-set folder location/name?

Is it then possible to select a file from the validation list that will then
open the file for inputting information?

Thanks

Gordon

Getting Creative
 

set we have a set of files in a folder C:\test:

alpha.xls
beta.xls
gamma.xls
temp.xls
tempx.xls

Pick a column and enter:

alpha
beta
gamma
temp
tempx

Then in another cell, say A1, enter a data validation pull-down that will
select from the list. Finally, in another cell, enter:

=HYPERLINK("file:///c:\test\" & A1 & ".xls",A1)

So the user picks the file in A1 and this produces a nice clickable link in
the other cell.

--
Gary''s Student - gsnu200843


"Gordon" wrote:

Hi

I need some help.

Is it possible to have a validation drop down list that is populated by the
file names of a pre-set folder location/name?

Is it then possible to select a file from the validation list that will then
open the file for inputting information?

Thanks

Gordon

Getting Creative
 

Hi.

The solution needs to be more intuitive. The folder contents might change
without notice. Is there a way that a column can automatically update to a
folders contents?

G

"Gary''s Student" wrote:

set we have a set of files in a folder C:\test:

alpha.xls
beta.xls
gamma.xls
temp.xls
tempx.xls

Pick a column and enter:

alpha
beta
gamma
temp
tempx

Then in another cell, say A1, enter a data validation pull-down that will
select from the list. Finally, in another cell, enter:

=HYPERLINK("file:///c:\test\" & A1 & ".xls",A1)

So the user picks the file in A1 and this produces a nice clickable link in
the other cell.

--
Gary''s Student - gsnu200843


"Gordon" wrote:

Hi

I need some help.

Is it possible to have a validation drop down list that is populated by the
file names of a pre-set folder location/name?

Is it then possible to select a file from the validation list that will then
open the file for inputting information?

Thanks

Gordon

Getting Creative
 

Hi G

I think I would use a userform with a listbox populated with the files to
select form, and call the userform from a button on the worksheet.

Setup a userform with a userform with a ListBox named ListBox1 and a
CommandButton named CommandButton1 and inset the code below in the codesheet
for the userform:

Const MyPath As String = "C:\Temp\"
Private Sub CommandButton1_Click()
'If Me.ListBox1.Value < "" Then
MyFile = MyPath & ListBox1.Value
' Exit Sub
'End If
Workbooks.Open Filename:=MyFile
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Dim MyName As String

MyName = Dir(MyPath & "*.xls", vbNormal) ' Retrieve the first entry.
Me.ListBox1.AddItem MyName

Do While MyName < "" ' Start the loop.
Me.ListBox1.AddItem MyName
MyName = Dir ' Get next entry.
Loop
End Sub

Hopes this helps

---
Per


Gordon" skrev i meddelelsen
...
Hi.

The solution needs to be more intuitive. The folder contents might change
without notice. Is there a way that a column can automatically update to a
folders contents?

G

"Gary''s Student" wrote:

set we have a set of files in a folder C:\test:

alpha.xls
beta.xls
gamma.xls
temp.xls
tempx.xls

Pick a column and enter:

alpha
beta
gamma
temp
tempx

Then in another cell, say A1, enter a data validation pull-down that will
select from the list. Finally, in another cell, enter:

=HYPERLINK("file:///c:\test\" & A1 & ".xls",A1)

So the user picks the file in A1 and this produces a nice clickable link
in
the other cell.

--
Gary''s Student - gsnu200843


"Gordon" wrote:

Hi

I need some help.

Is it possible to have a validation drop down list that is populated by
the
file names of a pre-set folder location/name?

Is it then possible to select a file from the validation list that will
then
open the file for inputting information?

Thanks

Gordon