directory listing

[akrashid]

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

 

[Jim Cone]

The free Excel add-in List Files will do all that, except
it will not automatically update (you have to click a couple of buttons).
Download from... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA



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

 

[akrashid]

Use this code to get you started. If you need help finishing it post back or
email me @ (e-mail address removed).

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 & 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.

 

[Guest]

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

 

[Chip Pearson]

See http://www.cpearson.com/excel/FolderTree.htm for some code to get you
started.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[akrashid]

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,

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