Cell value = lastmodifieddate from hyperlink path

Question

Difficult to explain but my picture will make it clear.

My Excel sheet has hyperlinks in column a that links to the file for opening but how to get the lastmodifieddate from that file in column l

Example : a22 is linked to file vimco, so what is the last modifieddate from that file has to come in l22

the picture here

also not all rows in column a has hyperlinks and contains also a empty or not linked cell so if that is so than nothing has to show in column l it has to be blank if a is blank or not linked

can it make a formula? so i type in cell =moddate and then shows the date is ok now

ok got the code now

Function GetDateTime(myCell As Range) As Date
    Dim myHyperlink As Hyperlink
    Dim Filename As String
    Application.Volatile
    On Error Resume Next
        Set myHyperlink = myCell.Hyperlinks(1)
    On Error GoTo 0
    If Not myHyperlink Is Nothing Then
        Filename = myHyperlink.Address
            'If it is a relative address insert this workbook's path
        If Not (Filename Like "\\*" Or Filename Like "[A-Z]:\*") Then
            Filename = ThisWorkbook.path & "\" & Filename
        End If
        If Dir(Filename, vbNormal) <> "" Then
            GetDateTime = FileDateTime(Filename)
        Else
            GetDateTime = ""
        End If
    Else
        GetDateTime = ""
    End If
End Function

but now second thing if the link changes or I open my workbook or swap sheets is not updating the value of getdatetime - can that be fixed?

As far as I know, you'll need VBA to create an UDF that returns the last modified that of a file. — Foxfire And Burns And Burns
no matter how it is a way is good enough , yeah and also if i close the workbook and a week after i open again it keeps up last dates modified. its a personel sheet so i copy that sheet to add a new personel in the warehouse — Jürgen Lambrecht
About calculation. check if calculation is turned to manual. — Foxfire And Burns And Burns

Alex de Jong Alex de Jong · Accepted Answer · 2019-03-05T10:23:30

You can create a custom function in VBA, and use that in your Excel sheet. For this,you need the FileDateTime function in VBA, to create something like this:

Function GetDateTime(r As Range) As Date
GetDateTime = FileDateTime(r.Hyperlinks(1).Address)
End Function

Now you can use this function as a normal Excel function and fill in the cell with the hyperlink as parameter. Like this: =GetDatetime(A1). If there is a hyperlink in A1, it will return the date.

Cell value = lastmodifieddate from hyperlink path

1 Answers