On Feb 8, 6:53 pm, Charles Chickering
wrote:
Try this modification:
Sub TestfileSearch()
Dim i As Long
Dim strFile As String
Dim strFolder As String
Dim strPF As String 'Previous Folder
Dim LineNum As Long
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "*.txt"
.LookIn = "C:\Test"
.Execute
If .FoundFiles.Count = 0 Then Exit Sub
strFolder = Left(.FoundFiles(1), InStrRev _
(.FoundFiles(1), "\") - 1)
Range("A1") = strFolder
LineNum = 2
strPF = strFolder
For i = 1 To .FoundFiles.Count
strFile = Right(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\"))
strFolder = Left(.FoundFiles(i), InStrRev _
(.FoundFiles(i), "\") - 1)
If strFolder = strPF Then
ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & LineNum), _
Address:=.FoundFiles(i), TextToDisplay:=strFile
LineNum = LineNum + 1
Else
Range("A" & LineNum) = strFolder
ActiveSheet.Hyperlinks.Add Anchor:=Range("B" & LineNum + 1), _
Address:=.FoundFiles(i), TextToDisplay:=strFile
LineNum = LineNum + 2
strPF = strFolder
End If
Next
End With
End Sub
--
Charles Chickering

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

" wrote:
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.

Charles,

You are the man!! thanks so much that works like a charm. It display
everything I wanted. I have learned a lot in this past week just by
following you guys code.

Thanks for everyone input