I would like Excel to find the folder/file names in a folder on my hard drive
& use them as items in a dropdown list, i.e., the base folder's address might
be C:\artists. The artists folder has subfolders named "Doobie Bros", "Steely
Dan", etc. The dropdown list would show:
Doobie Bros
Steely Dan
etc.
as selectable items. I'd also like these items to be hyperlinks but that'd
just be icing on the cake, the initial list generation is the main goal.

This should get you started. I have a sheet that includes the
following ranges:
"Path" - a single cell where I type in the path to the files
"FileSpec" - a single cell where I type in the qualifier such as
"*.xls"
"FileList" - a dynamic range where the list of file names is placed.

The macro creates a list of the files in the folder and then creates
hyperlinks to each of them. Not exactly what you are looking for but a
start.

- John Michl
www.JohnMichl.com

==================================================
Sub ListFiles()

Dim p As String ' path
Dim s As String ' specifier
Dim r As Integer ' row number
Dim i As Integer 'index number
Dim l As Integer ' length of path


p = ActiveSheet.Range("Path").Value
s = ActiveSheet.Range("FileSpec").Value
r = 8
l = Len(p) + 2

' Clear previous file list if any - cell A8 is top of list
If ActiveSheet.Range("A8") < "" Then
ActiveSheet.Range("FileList").Clear
ActiveSheet.Range("FileList").Hyperlinks.Delete
End If

With Application.FileSearch
.NewSearch
.LookIn = p
.Filename = s

If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(r, 1).Value = Mid(.FoundFiles(i),
l, 100)
r = r + 1
Next i

For Each cell In ActiveSheet.Range("FileList")
cell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=p & "\" & cell.Value _
, TextToDisplay:=cell.Value
Next cell
Else
MsgBox "There were no files found matching the criteria."

End If
End With
End Sub

Thanks, John.

I guess I should have asked if ther was an EASY way. It just seems like a
fairly common need; I guess I expected the answer to be a bit simpler. Not
being Macro savvy, I'm in the dark as far as understanding your solution goes.

I wonder if the "DIR" command could be used with the (filename) modifier to
get the names into a text file? It might be simpler to import the filenames
that way. What do you think?

Terp

"John Michl" wrote:

This should get you started. I have a sheet that includes the
following ranges:
"Path" - a single cell where I type in the path to the files
"FileSpec" - a single cell where I type in the qualifier such as
"*.xls"
"FileList" - a dynamic range where the list of file names is placed.

The macro creates a list of the files in the folder and then creates
hyperlinks to each of them. Not exactly what you are looking for but a
start.

- John Michl
www.JohnMichl.com

==================================================
Sub ListFiles()

Dim p As String ' path
Dim s As String ' specifier
Dim r As Integer ' row number
Dim i As Integer 'index number
Dim l As Integer ' length of path


p = ActiveSheet.Range("Path").Value
s = ActiveSheet.Range("FileSpec").Value
r = 8
l = Len(p) + 2

' Clear previous file list if any - cell A8 is top of list
If ActiveSheet.Range("A8") < "" Then
ActiveSheet.Range("FileList").Clear
ActiveSheet.Range("FileList").Hyperlinks.Delete
End If

With Application.FileSearch
.NewSearch
.LookIn = p
.Filename = s

If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
ActiveSheet.Cells(r, 1).Value = Mid(.FoundFiles(i),
l, 100)
r = r + 1
Next i

For Each cell In ActiveSheet.Range("FileList")
cell.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:=p & "\" & cell.Value _
, TextToDisplay:=cell.Value
Next cell
Else
MsgBox "There were no files found matching the criteria."

End If
End With
End Sub