Forum Discussion

Rachel1994's avatar
Rachel1994
Copper Contributor
Jun 30, 2022

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 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

excel
Macros and VBA
  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 01, 2022
    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.

Resources