Forum Discussion
VBA Open Latest File From Sharepoint
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 re...
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.
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.