Excel 2003 - create dynamic table of filenames in a directory

[RickD]

Using Excel 2003

I'd like to create a dynamic table of file names that are located in a
certain directory when the workbook opens and then be able to display, with
the default application, any one of the folders

Thanks in advance

RickD

 

[Joel]

What are you calling the default application? You can use a workbook open
event to update the spreadsheet and read all the folders in a directory and
place the names of these folders in the workbook. You can also put the
directory names in a list box so the user can select the directory and then
do something with the directory picked.

 

[RickD]

I meant file in the last sentence.

here is an example of what I want to do:

upon opening 8121.xls
directory j:\{name of directory to search} is searched and the filename
of all files found are entered into a table as hyper-links

HTH

RickD

 

[RickD]

Thank yu for the fast response.....Joel

The default application is Adobe Reader for a *.PDF file, Windows Media
Player for *.JPG, Lotus for *.123

 

[Rick Rothstein]

I think this macro will do what you want (just change the values I have
assigned in the Const statements to those for your actual setup)...

Sub MakeHyperlinks()
Dim FN As String
Dim RowLoc As Long

Const StartRow As String = 3
Const HyperlinkCol As String = "B"
Const SheetName As String = "Sheet4"
Const DirPath As String = "C:\TEMP\Test\" 'Note the backslash

FN = Dir(DirPath & "*.*")
RowLoc = StartRow
With Worksheets(SheetName)
Do While Len(FN) > 0
.Hyperlinks.Add Anchor:=.Cells(RowLoc, HyperlinkCol), _
Address:=DirPath & FN, TextToDisplay:=FN
RowLoc = RowLoc + 1
FN = Dir
Loop
End With
End Sub

 

[RickD]

Thank you Rick,

I will test and report back

RickD