On Feb 7, 3:25 pm, Charles Chickering
wrote:
Use this code to get you started. If you need help finishing it post back or
email me @ .

Sub TestfileSearch()
Dim i As Long
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "*.txt"
.LookIn = "C:\Test"
.Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), _
Address:=.FoundFiles(i) , TextToDisplay:= _
Right(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\"))
Next
End With
End Sub

Note that I'm using the InStrRev function so this will not work in Excel 97
and previous. (Actually I'm not sure it will work in 2000 either)
--
Charles Chickering

"A good example is twice the value of good advice."

" wrote:
Hi all,

I want to create a macro that would provide me a listing of a
directory and its sub-directories and create hyperlinks to all
the .pdf files in these dir's. I would like to capture the directory
names too .Also I would like to updates these links everytime the
spreadsheet is opened.
My directory structure is :
c:\Pending Cases
Year_2007
a
b
.
.
z
Year_2008
a
b
.
.
z
So I want to Start at the Pending Cases level and capture the dir name
Year_2007 then capture each dir name underneath it a.. z and create
hyperlinks to all the *.pdf files in each dir.

Please provide some guidance for the above task.

thanks in advance

Charles & Jim,

thanks for the reply. Jim your code is good but is too advanced for my
skill level yet ,I am still taking baby steps. thanks though it will
come in handy in mapping folders and sub folders.

Charles, on your code is there a way I can capture the dir name and
then the hyperlinks will be underaneath each dir name,

thnaks again for all your help.