VBA Open Latest File From Sharepoint

[Rachel1994]

Hello,

I am writing a macro and want to include some data from a file on Sharepoint. The macro is run daily, but a new Sharepoint file is added weekly.

So I want the macro to find the most recent file in the sharepoint folder. I have tried the code below but keep getting a runtime error (52: Bad file name or number). Can anyone point out what the issue is? Thanks!

Sub OpenSharepointRecentFile()

'find & open latest XYZ File
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date

MyPath = "https://company.sharepoint.com/sites/group/Shared%20Documents/General/folder/"

MyPath = Replace(MyPath, "https:", "") 'adaptation attempt for SharePoint folder
MyPath = Replace(MyPath, "/", "\") 'adaptation attempt for SharePoint folder
MyPath = Replace(MyPath, Split(MyPath, "\")(2), Split(MyPath, "\")(2) & "@SSL")

MyFile = Dir(MyPath & "*.xlsm")

If Len(MyFile) = 0 Then
MsgBox "No Supply Headline files were found...", vbExclamation
Exit Sub
End If

Do While Len(MyFile) > 0
LMD = FileDateTime(MyPath & MyFile)
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
MyFile = Dir
Loop

Workbooks.Open MyPath & LatestFile

End Sub

[#2]

From bad memory, having grappled with similar issues, I believe VBA cannot interact with SP. It can only interact with local files. So the way to interact with SP it to synch the directory to your OD.

Word of warning. The method to sample FileDateTime seems entirely reasonable to me. Just until we talk of M365 and its god-awful habit of AutoSave. AutoSave is wreaking havoc with all our old XLS. People consult old XLS just to verify a number and - boom! - there goes your bandwidth (and timestamp). That said, the phenomenon that timestamps are not reliable predates AutoSave. We prefix each file in our archives with yymmxx and go only by that.

Lastly, it is gratifying - in some perverse sense - to see a fellow programmer grapple with the same REPLACE nonsense I hit my head against. Have you come across this:

Function GetFileName(ByVal aPath As String) As String
Dim FSO As New FileSystemObject
GetFileName = FSO.GetFileName(aPath)
End Function

Might help to restore some sanity. Basically, SP-integration into Excel VBA isn't where you want it to be.