0

I would like to seek help regarding on getting the last modified date of a file in a Sharepoint. What VBA code/command will I use in order to execute it. I would like to show this "Last Modified Date" of the file in a MsgBox when a command button is clicked.

Your prompt response is very much appreciated.

xtina1231
  • 115
  • 2
  • 3
  • 12

4 Answers4

1

I've been trying to figure this out for a while and I stumbled upon something in another line of inquiry that led me to a solution.

In your VBA window, go to Tools -> References, and then scroll down and check the box next to "Microsoft Scripting Runtime".

Then when you specify your link it's going to read like this:

FileDateTime("//site.com/page/file.xlsx").

No "http:" Once I did that, it worked like a charm.

Ryan Kok
  • 53
  • 1
  • 7
1

This one had me scratching my head for a bit too...

Make sure to add "@ssl" following the root URL e.g

FileDateTime("\\site.com@ssl\file.xlsx")

Docta001
  • 11
  • 1
0

You can use 

FileDateTime ( file_path )

to get the date and time of when a file was created or last modified.

For more information please visit the below link..

VBA Help

Kaustav Banerjee
  • 275
  • 1
  • 10
  • Hi,i use this command to get the last modified date of a file in a sharepoint. i place the link of my sharepoint site on the file_path. but it throws an error. can you help me with this? – xtina1231 Jan 22 '15 at 03:13
  • Hi! @Kaustav Banerjee This is the command i execute: Dim ModifiedDate as Date ModifiedDate = FileDateTime("https://site1/folder1/subfolder2/subfolder3/aaaa.xlsx") Then the error is, "Run time error '5': Invalid procedure call or argument". Please help me. thanks! :) – xtina1231 Jan 22 '15 at 05:33
  • **bold** `Dim ModifiedDate as Date ModifiedDate = FileDateTime("site1/folder1/subfolder2/subfolder3/aaaa.xlsx")` – xtina1231 Jan 22 '15 at 05:34
  • I searched for this error and got the below reference in MSDN. Can you please validate whether it is a platform related issue or not... If not then I'll try and investigate further.... https://msdn.microsoft.com/en-us/library/aa445484(v=vs.60).aspx – Kaustav Banerjee Jan 22 '15 at 06:09
  • Alternately you can also try this "Public Function gsGetLastModifiedDate(rsURL As String) As String Dim x As Object On Error GoTo ErrHandler Set x = CreateObject("MSXML2.XMLHTTP.5.0") x.Open "HEAD", rsURL, False x.send gsGetLastModifiedDate = x.getResponseHeader("Last-Modified") ExitRoutine: Set x = Nothing Exit Function ErrHandler: Resume ExitRoutine End Function" – Kaustav Banerjee Jan 22 '15 at 06:13
  • hi. can you explain the code you have given me? where will i put the URL of my Sharepoint and the name of the file? thanks a lot! – xtina1231 Jan 22 '15 at 08:37
  • i think the code, `Dim ModifiedDate as Date ModifiedDate = FileDateTime("site1/folder1/subfolder2/subfolder3/aaaa.xlsx")` is used if the file is on your local. but what if the file is in a sharepoint or a website? Thanks a lot! – xtina1231 Jan 22 '15 at 08:39
0
Sub TestWhen()

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

    MsgBox SPFileName  & " last modified on" & SPLastModified(SPFilePath, SPFileName)

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
Keith Swerling
  • 136
  • 1
  • 6