I have a huge sharepoint library with ~ 50 users. I then have a consolidated spreadsheet that links to these file (I agree it should be in a database but it is not). I want to create a sheet that lists all the external links and then displays the properties of the sharepoint file (i.e. checked out to, last modified date, last modified by).

I found this code that works well for creating a listing of all the external links but that is as far as I can go. I have searched and searched but not much info on VBA in sharepoint.

Sub ListLinks()
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
Cells(i, 1).Value = aLinks(i)
Next i
End If
End Sub

This code will list all the files in format https://.....

I also have mapped the sharepoint directory to my network if that helps.