Return last modified date/time in VBA

Question

Been really struggling to figure out how to return the last modified date/time in VBA, a lot of posts suggest using the already existing call to FileDateTime modifying the URL to look as follows:

\\companName.sharepoint.com\sites\siteName\Shared Documents\fileName.xlsx

However I seem to always be met with a timeout and an error:

Run-time error '5': Invalid procedure call or argument

Is there a way to return this date & time in vba?

Answer 1

So turns out whilst removing the https: it seems to fails due to a lack of the secure socket layer, adding

@ssl

to the url adds this in and connection is made.

\\companName.sharepoint.com@ssl\sites\siteName\Shared Documents\fileName.xlsx

If anyone else has any further input feel free.

Answer 2

Sub TestWhen()

    SPFilePath = "http://teams.MyCompany.com/sites/PATH/PATH/Fulfillment/Forms/AllItems.aspx"

    Debug.Print SPLastModified(SPFilePath, "2021_MyFileName.xlsx")

End Sub


Function SPLastModified(SPUrl As String, SPFName As String)
    Dim IE As Object

    Dim PagesHTML As String
    Dim Dadate As String
    Dim DaDateEnd As String
    Dim arr() As String
    arr = Split(OutString, " ")
 
    Dim LastChange As Variant

    Set ie = CreateObject("InternetExplorer.Application")

    With ie
        .Visible = True
        .navigate SPUrl
        Do Until .readyState = 4
            DoEvents
        Loop
    
        Do While .busy: DoEvents: Loop

        Do Until .readyState = 4
            DoEvents
        Loop
        PagesHTML = ie.document.DocumentElement.outerHTML
    End With

' Get to File
    Dadate = InStr(PagesHTML, "FileLeafRef" & Chr(34) & ": " & Chr(34) & SPFName)

' Get to Modified Date
    ModifiedText = "Modified" & Chr(34) & ": "
    Dadate = Dadate + InStr(Mid(PagesHTML, Dadate), ModifiedText)

    OutString = Mid(PagesHTML, Dadate + Len(ModifiedText), 27)

    arr = Split(OutString, " ")
   
    LastChange = arr(1) & " " & arr(2)    
    LastChange = arr(0) & "/" & Mid(arr(1), 6) & "/" & Mid(arr(2), 6, 4) & " " & LastChange

    SPLastModified = LastChange

End Function