Display a documents properties from its hyperlink

Hi All,
Just joined and I've got a question. I'm putting together an Excel sheet which contains links to other files. I was wondering if I can display the properties of those files in an adjacent cell? Basically I want to show the date the document being pointed to in a hyperlink was last changed. The documents are contained on a local drive.

Anyone out there know how to do this?

I'd expect you would need to write a custom function in the vbaeditor. You could use this:

Please Login or Register  to view this content.

To use it:
-Right click on the worksheet and select "view code"
-click insert module
-copy the code above into that module
-on the worksheet type (for example), "=hyperlinkdate(A1)" to get the date of the hyperlink in A1
-note, it will return the date as a dateserial number so you may need to format the cell as a date.

Thanks,
Put a hyperlink into A1 to a file on the c drive. Right clicking the worksheet didn't bring up the "view code" option so pressed the visual basic button. Right clicked on worksheet 1 and inserted module which appeared under VBAproject. Pasted the code into the Module 1 code window, formatted B1 as date and entered the "=hyperlinkdate(A1)". B1 now just says #NAME?. I take it I've cocked something up.

Sorry if I'm being an idiot.

Seems like what you have done should work to me, check:
-the code is in the module 1 window, not the sheet 1 window
-that the module created is in the workbook that you are using the code in (just close any other workbooks?)
-that you typed the formula after creating the function, it might not update automatically.

Sorry,
It does work. I just needed to click B1 then click on the formula bar then the date comes up. Don't suppose you know how to make it auto update (he asked feeling somewhat cheeky)?

Not really, you could try adding application.volatile at the start of the macro but not sure that changing the hyperlink would trigger that. Once you change one cell it should update all of them though.

Yes,
That worked to. The date updates when I open the project after I've messed around with the hyperlinked files and saved them. Thanks a lot. Now if I only understood what you'd done...

Many Thanks,
Chris