vba getting latest file from sharepoint

[dwpang]

hi, i am trying to retrieve the latest file from sharepoint folder every week via vba.

so, the files are named as XXX XXX XXX 20210614.xlsx as you see in the picture.
i got the url by opening one of them and copied the link to input as the url in the vba.

mypath = "https://xxx.sharepoint.com/sites/xxx-xxx-xxx-xxx/Shared Documents/xxx xxx/Weekly reports/"
mypath = Replace(mypath, "/", "\")
mypath = Replace(mypath, "https:", "")

myfile = Dir(mypath & "*.xlsx", vbNormal)
If Len(myfile) = 0 Then
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
Set wb = Workbooks.Open(mypath & latestfile)

problem is whenever i come to --- myfile = Dir(mypath & "*.xlsx", vbNormal)
i will have a run-time error 52: bad file name or number

any experts or advice?
p.s. mapping to drive is not an option for me currently

 

[Domenic]

Assuming that you have access privileges and the path is correct, it should work. Also, make sure that the number of characters in the path does not exceed the 260 character limit (see Naming Files, Paths, and Namespaces - Win32 apps). And, lastly, check the file itself to see whether the filename contains Unicode characters. If so, use the FileSystemObject object instead, which supports Unicode.