hyperlink to a folder

[Ferntown]

I've looked for a similar topic but I can't find one so here it goes. I would like to have a command button on a form that opens a folder that contains a bunch of excel files. I would like the user to be able to open any one of the files from here. Would I use a hyperlink? Is this possible or is there a better way.
Thanks,
Ruby

 

[luceze]

You can use this code in the on click event of your command button:

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

Dim FileXL As Variant
With fd
.Filters.Add "Excel Files", "*.xls", 1
.InitialFileName = "C:\"
If .Show = -1 Then
For Each FileXL In .SelectedItems
Workbooks.Open Filename:=FileXL
Next FileXL
Else
End If
End With

Change the path of the InitialFileName to the path of your folder.
You will need a reference to the microsoft office x.x object library.

HTH,
Eric

 

[luceze]

Sorry. That only works within Excel itself. Try this instead:


Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)

Dim FileXL As Variant
With fd
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xls", 1
.InitialFileName = "C:\"
If .Show = -1 Then

For Each FileXL In .SelectedItems

stAppName = "C:\Program Files\Microsoft Office\Office10\excel.exe " & FileXL
Call Shell(stAppName, 1)
Next
End If
End With



Eric