Outputting a hyperlinked list of documents in a folder with relative filepaths in Excel 97
November 30, 2010 3:03 PM   Subscribe

So I am stuck at work with Excel 97. I am trying to do something that seems simple enough in my head but just isn't. Basically, I have a workbook filled with a bunch of worksheets. In some of those worksheets, I would a list of documents in a given folder with hyperlinks to those documents. I've managed to sort out a way to create a list of documents in a folder which updates each time the whole book is opened, but I really, really want that hyperlinked capability. More inside.

So I am stuck at work with Excel 97. I am trying to do something that seems simple enough in my head but just isn't.

Basically, I have a workbook filled with a bunch of worksheets. In some of those worksheets, I would a list of documents in a given folder with hyperlinks to those documents. I've managed to sort out a way to create a list of documents in a folder which updates each time the whole book is opened, but I really, really want that hyperlinked capability.

Out-of-date-Excel-VB-guru-filter?

I need to:

1) Create a list of documents in a given folder
2) Make items on that list hyperlink to the actual document
3) Have the list update either dynamically or at least on worksheet opening

Important Bonus Question:
1) I will have other information that will be added to the table that will not be updated dynamically. How can I protect the table structure while still updating the list? Perhaps the most elegant solution would be to simply replace a file that used to be listed with a "missing" note or something so the table structure stays put while I see what happened to the file, ie:

file.txt | August 21, 2010 | Scanned
missing | August 22, 2010 | Scanned

Notes:
1) The relative paths of folders containing documents are are always consistent between project folders - this is important.
2) I have the update-on-open thing figured out by putting the listing macros into the individual sheets, then calling them from Workbook_Open(). I welcome more elegant solutions, if any.
posted by neksys to Computers & Internet (1 answer total)
 
I don't think VBA has changed all that much. The following works on my (current) office setup. I'd mostly worry that the "HYPERLINK" worksheet fuction doesn't work...

Sub listinsheet()
Const sDir As String = "C:\"
Dim sFile As String
Dim i As Integer

Cells.ClearContents

sFile = Dir(sDir)

While sFile <> ""
i = i + 1
Cells(i, 1).FormulaR1C1 = "=HYPERLINK(""" & sDir & sFile & """)"
sFile = Dir
Wend

End Sub

posted by pompomtom at 3:57 PM on November 30, 2010


« Older Better Android Calendar App/Widget?   |   what to get a disaster-obsessed kid for Christmas? Newer »
This thread is closed to new comments.