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?


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


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.

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
        Do While .busy: DoEvents: Loop

        Do Until .readyState = 4
        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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.