Hi, I use the below code, to open the latest file from a folder or subfolder. Works great, easy to adapt to different folder situations and file name components.
But the MyFile step throws an error if I want to use it for a SharePoint folder. [Bad file name or number]
Google gave me two possible reasons in regard to the path: "/" and "https:"
I can change one or both of them, but it does not change the error on the MyFile step. So I commented it out here.
Where is my mistake? Is there any other adaptation necessary? Or is there an alternative approach available? Thanks!
'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/blah/abcd/Team Documents/whatever/details/2021/"
'MyPath = Replace(MyPath, "/", "") 'adaptation attempt for SharePoint folder
'MyPath = Replace(MyPath, "https:", "") 'adaptation attempt for SharePoint folder
MyFile = Dir(MyPath & "XYZ *.xlsm")
If Len(MyFile) = 0 Then
MsgBox "No XYZ 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
Hi Matthias,
Dir works on local file system. An online sharepoint folder is not local, it's online (obviously).
From web, you have to download the file.
Easiest way is to go online to that folder, and sync it to local computer, you should find a Sync command in the menu for this. Then you can use dir to look into the location on your computer where the sync'd folder is. (best option)
To download from web, search the web for Function URLDownloadToFile, you will find what you need.
Or, you can map the SP web folder with your local computer, see image attached.
Hi Catalin,
thanks for the answer, that makes as always a lot of sense!
What would be the benefit of Sync (going through OneDrive) against mapping?
Thanks again,
Matthias
Hello,
With synced folder you still have the data available even when not online, with mapped folder you need to be online to get the data.
Br,
Anders
Hello Anders,
Thanks for the answer. Generally that is an advantage. I did not perceive it as advantage as I normally work online and if I "Open Latest File from SharePoint" I need to be online to be sure that I get the latest file.
[=> I'd have to spent quite some disk space on my local machine for data which is most likely not up to date.]
Thanks,
Matthias
Hello Catalin,
mapping the sharepoint showed me that adding @SSL was the missing step:
MyPath = Replace(MyPath, "https:", "")
MyPath = Replace(MyPath, "/", "")
MyPath = Replace(MyPath, Split(MyPath, "")(2), Split(MyPath, "")(2) & "@SSL")
With this it just works. Thanks for the hint!
Regards,
Matthias
