Issue:

You want to list, and create a hyperlink to, a list of files of a certain type.

Resolution

Sub List_Files()
'Declare your variables
Dim I, J, c, fPath, fExt
'Get your search extension
fExt = InputBox("Please enter the extension for the files you request:", _

"File Search Extension", "txt")
'Get your search path
fPath = InputBox("Please enter the path and directory you wish to search", _
"File Search Path", "C:\My Documents\")
'Select the first cell of your worksheet
ActiveSheet.Range("A1").Select
'Get your first file name and create it's hyperlink
Selection.Value = Dir(fPath & "*." & fExt)
Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
fPath & Selection.Value
'Cycle through the directory listing the remainder of the files
I = Dir
Set c = Range("A2")
Do While I <> ""
c.Select
c.Value = I
I = Dir
Selection.Hyperlinks.Add Anchor:=Selection, Address:= _
fPath & Selection.Value
Set c = c.Offset(1, 0)
Loop
'Let your user know how many files were added to the list
J = c.Offset(-1, 0).Row
MsgBox "There were " & J & " files added to this list"
Range("A1").Select

End Sub

� 2002 Dan Herrera
 These functions may be distributed freely, but please give credit where credit is due.
And if you feel you must give credit, or have a request for a custom function/macro,
please send me a note DataMasterFla

Sign Guestbook View Guestbook

Back.

Hosted by www.Geocities.ws

1