Date Modified for Hyperlinked file

meijhana · Jul 11th 2018

I have an excel file with a list of projects (~500). Every active project has a hyperlinked status report. I want to add a column showing the date that the hyperlinked status report was last modified (so I can keep track of who is not updating their status reports).
Not every project in the list is active so not every row has a hyperlink.

Project Names are in Column A along with a bunch of other information in subsequent columns.

The Hyperlink to the Status report is listed in Column AI.

I want to put into Column AJ the date that the hyperlinked file was last modified.

Hyperlinks exist in Column AI between rows 4 and 500 (though not every row in column AI has a hyperlink)

I have found a few things online but have not been able to cobble together a functioning code.
Any help would be appreciated.

Thank you
Meijhana

chirayuw · Jul 12th 2018

Try this. I've left some code segments in incase you wanna use those too. Its a modified version of an old macro of mine

Code

Sub Get_File_Details()


Dim Fname As String
'Dim CB As String 'Created By
'Dim CD As String 'Create Date
'Dim LMB As String 'Last Modified By
Dim LMD As String 'Last Modified Date
Dim LstRow As String 'Current Row


Do While Not IsEmpty(Range("A" & ActiveCell.Row))
    If IsEmpty(Range("AI" & LstRow)) Then
    Else
        LstRow = ActiveCell.Row
        Fname = Range("AI" & LstRow)
        Workbooks.Open Filename:=FPath & Fname, UpdateLinks:=False, ReadOnly:=True
        With Workbooks(Fname)
            'CB = .BuiltinDocumentProperties("Author")
            'CD = .BuiltinDocumentProperties("Creation Date")
            'LMB = .BuiltinDocumentProperties("Last Author")
            LMD = .BuiltinDocumentProperties("Last Save Time")
        End With
        Workbooks(Fname).Close savechanges:=False
       
        'Range("A" & LstRow) = Fname
        'Range("B" & LstRow) = CB
        'Range("C" & LstRow) = CD
        'Range("D" & LstRow) = LMB
        Range("AJ" & LstRow) = LMD
    End If
        ActiveCell.Offset(1, 0).Select
Loop


End Sub

Display More

meijhana · Jul 13th 2018

Thank you for the help.

I tried the above code but received an error.

Run-time error '1004':
Application-defined or object-defined error

Walking through the code, it seems to get through the If line but then generates the error.

I also realized I did not state that the hyperlink goes to a SharePoint file. I am not sure if that makes a difference.

Again, thank you for any help
Meijhana

chirayuw · Jul 16th 2018

refer to below links to reference sharepoint

https://stackoverflow.com/ques…file-from-sharepoint-site
https://www.mrexcel.com/forum/…-another-destination.html

Participate now!

Share