Show last modified date or even better history of modifications of listed hyperlinks (also excel books)

[Dave Grimwood]

Hi guys

I have a list of H&S risk reports and I need a formula to help me track the last modified date. I.e I need a column next to the hyperlink to show the modifed date of that hyperlink.

If this could be explained in quite simple terms that would be great.

Cheers! - Dave

 

[Richard Schollar]

David

Apologies for the delay.

OK, the following is a UDF that you can use as a formula in a cell in your workbook - just point it at the cell containing the HYPERLINK function. It is reliant on the format of your function being:

=HYPERLINK("Server Path & filename")

so it won't work if you build up the Path & Filename by referencing other cells.

Function ReturnLastModified(r As Range) As String
Dim temp
Application.Volatile True
temp = Split(r.Formula, Chr(34))(1)
If Dir(temp) <> "" Then ReturnLastModified = FileDateTime(temp)
End Function

To use: open up the Visual Basic Editor using Alt+F11 whilst your spreadsheet is open on the Excel screen. Insert a module (Insert>Module) and paste the code into the module window which will open.

Back in your cell, assuming cell A1 contains your HYPERLINK function, place the following formula in a cell:

=ReturnLastModified(A1)

this will return the last modified date of the file named in the hyperlink.

 

[Dave Grimwood]

Thanks for the reply!

I have given it a go and come up with a #name? error.

I am certainly not linking it to other cells and also understand what you asked me to do.
I also had some other crap in the visual basic from trying things last week which I deleted and re opened.

Can you think of anything that may be wrong.

Thanks guys your great

 

[Richard Schollar]

Dave

Can you post exactly what you are typeing in to your cell, and also confirm that you have pasted the code into a standard mdoule in your workbook (not into a worksheet module or ThisWorkbook module).

The #NAME? error means that Excel can't resolve the function name you have entered into the cell. Now this could be because the function is entered incorrectly (eg misspelt) or something more fundamental such as the code has been enetred into the wrong place (such as the worksheet/thisworkbook module) or into a module in the wrong workbook altogether).

 

[Dave Grimwood]

This is the code I am using that I have pasted into a (general) module.

Function ReturnLastModified(r As Range) As String
Dim temp
Application.Volatile True
temp = Split(r.Formula, Chr(34))(1)
If Dir(temp) <> "" Then ReturnLastModified = FileDateTime(temp)
End Function


The hyperlink reads as follows..
\\AlphaStorage\L\OPERATIONS\Projects\AJ Jobs\AJ11139 Goldman Sachs MCR.CR\Project Management\Risk Registers\Risk Register Issue 2.xls

The hyperlinks work fine, and they are renamed in the cell to say risk register.

Thanks for all the help

 

[Richard Schollar]

Dave I've sent you a PM because it would help me greatly if I could see an example of your hyperlink where it's failing (doesn't matter that I won't have access to the network location it's pointing to).

 

[robawrenson]

I'm going to try revive this thread ...

Richard - I'm having a similar problem to Dave circa 2010.

I'm using Excel 2013.

In VBA I have a module: Module1

Function ReturnLastModified(r As Range) As String
Dim temp
Application.Volatile True
temp = Split(r.Formula, Chr(34))(1)
If Dir(temp) <> "" Then ReturnLastModified = FileDateTime(temp)
End Function

In cell A1 I have this hyperlink:

J:\Development\Job 01307 - XX\11 Health & Safety\01 Site Risk Assessment\1307_20141114_General Risk Assessment_FM-HAS-10 Risk Assessment form.doc

In cell B1 I have this formula:

=ReturnLastModified(A1)

But I am getting #VALUE!

J: is a networked drive.

Thanks,

Rob

 

[RoryA]

Is it a HYPERLINK formula or just a static hyperlink? I suspect the latter, in which case the function would error as it's looking for " in the formula.

 

[robawrenson]

Hero. Rookie error!