List Files as HyperLink through VBA

J

James

Hi

I am not sure if it is poosible, but I'll try to explain.
Is there a way in xl2002,vba to list all the files in a
foleder & its subfolders in a tree sort of format so that
its shows names of the file (not the whole location)
Hyperlinked. When user clicks on it it opens up the files.

Thanks for your help
-James
 
J

James

Actually I meant list files & folders as ToC , Table of
content, i.e Folder name & list of all files under it
Hyperlinked
Thanks
 
F

Frank Kabel

Hi
try the following macro:

----
Sub FindFiles()
Dim myPath As String
Dim lLen As Long, i As Long
myPath = "D:\temp" 'change to your needs
With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = True
.filename = "*.*"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Cells(i, 2).FormulaR1C1 = "=Hyperlink(R[0]C[-1])"
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
 
J

Jim Cone

James,

My Excel add-in "List Files" should do what you want.
It finds files on your computer meeting criteria you
specify. It generates a list on a new worksheet
showing file location, name, type ,size and
last save date. A hyperlink is created for each file.
It comes with a one page Word.doc install/use file.

It is available - free - upon direct request.
Remove "xxx" from my email address.

Regards,
Jim Cone
San Francisco, CA
[email protected]
 
Top