VBA to return FileDateTime for a file on Sharepoint

[DanHaines]

Hi all, I'm new here so apologise if there's any specific formatting I've missed.
To give a run down of what I'm trying to do;

We have a powerpoint running 24/7 which displays live and updated date which is retrieved through VBA.
There are some aspects to this which are updated on a manual basis so I have a userform which pulls a file from our sharepoint site, updates it, and saves it.

My code for the powerpoint runs for the slide onChange event. I currently download the file and check the current data in the workbook vs the old, if there's any changes I update the values on the powerpoint.

However what I'm after is a way to check the last modified time for the file before downloading, this way it will significantly reduce the amount of times it requires downloading.

I've yet to find a working solution, or at least one I've been able to implement.

TL;DR - I want to be able to return the last modified time for a file in Sharepoint.
I've tried all of the following:

Const URL1 As String = "\\companName.sharepoint.com\sites\siteName\Shared Documents\fileName.xlsx"
Const URL2 As String = "//companName.sharepoint.com/sites/siteName/Shared Documents/fileName.xlsx"
Const URL3 As String = "https://companName.sharepoint.com/sites/siteName/Shared Documents/fileName.xlsx"

Debug.Print FileDateTime(URL1)
Debug.Print FileDateTime(URL2)
Debug.Print FileDateTime(URL3)

URL1 & 2 times out and gives me; Run-time error '5': Incalid procedure call or argument
URL3 is instant and doesn't even timeout, but gives me the same error.

Any help is much appreciated.

 

[Confused2]

Did you ever get an answer to this?

I, also, need to retrieve file dates from SharePoint.

 

[JEC]

Try power query