Excel 2003 - create dynamic table of filenames in a directory

  • Thread starter Thread starter RickD
  • Start date Start date
R

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
 
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.
 
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
 
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
 
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
 
Back
Top