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