How to scan documents and put it in document folder?

Outputting a hyperlinked list of documents in a folder with relative filepaths in Excel 97

• 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.

• Answer:

  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 >