Open file from sharepoint with the latest date

[samst]

Each week a file is posted to the same folder in sharepoint. The file name is the same except for the date
For example:

ABC DEF GHK - 20180217 - Sales.xlsb
ABC DEF GHK - 20180210 - Sales.xlsb
ABC DEF GHK - 20180203 - Sales.xlsb

I just want to open the most recent version of the file when I run the macro. The code I'm trying is below but for some reason it doesn't open the file I want. Hoping someone can help me figure out what I'm doing wrong. Instead of opening the file I want it opens allitems.aspx

Can you check the code below to see if you see something wrong.


Sub ABC_DEF_GHK_GET()

'---Opens a sheet based on date, searches backward from today til it finds a matching date

Dim dtTestDate As Date
Dim sStartWB As String
Dim dtEarliest As Date

Const sPath As String = "https://mycompany.sharepoint.com/teams/
dtEarliest = Date - 14 '--to stop loop if file not found by earliest valid date

dtTestDate = Date
sStartWB = ActiveWorkbook.Name

'--add this to suppress error "The Internet address... is not valid"
Application.DisplayAlerts = False

While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
On Error Resume Next
Debug.Print "Trying to open: " & _
sPath & "ABC DEF GHK - " & Format(dtTestDate, "yyyymmdd") & " - Sales.xlsb"
Workbooks.Open sPath & "ABC DEF GHK - " & Format(dtTestDate, "yyyymmdd") & " - Sales.xlsb"
dtTestDate = dtTestDate - 1
On Error GoTo 0
Wend

Application.DisplayAlerts = True

If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."
End Sub '

 

[ParamRay]

Could do it like this:

Public Sub OpenLatestFile()
  On Error GoTo ErrHandler
  Dim vntFilePath As Variant
  vntFilePath = GetLatestPath()
  
  If IsEmpty(vntFilePath) Then
    MsgBox "Unable to identify the latest file.", vbExclamation
  Else
    Workbooks.Open vntFilePath, False
  End If
  
  Exit Sub
ErrHandler:
  MsgBox Err.Description, vbExclamation
End Sub

 Private Function GetLatestPath() As Variant
  Const strFNAME_PATTERN = "ABC DEF GHK - ######## - Sales.xlsb"
  Const strPARENT_DIR = "\\ComputerName\SharedFolder\Resource\" '<-- Set the UNC path here
  Dim vntLatestPath As Variant
  Dim objFileSystem As Object
  Dim dtmLatestDate As Date
  Dim dtmFileDate As Date
  Dim objFolder As Object
  Dim objFile As Object
  
  On Error GoTo ErrHandler
  Set objFileSystem = CreateObject("Scripting.FileSystemObject")
  Set objFolder = objFileSystem.GetFolder(strPARENT_DIR)
  
  For Each objFile In objFolder.Files
    If objFile.Name Like strFNAME_PATTERN Then
      dtmFileDate = DateFromFilename(objFile.Name)
      If dtmFileDate > dtmLatestDate Then
        dtmLatestDate = dtmFileDate
        vntLatestPath = objFile.Path
      End If
    End If
  Next objFile
  
  GetLatestPath = vntLatestPath
  
ExitProc:
  Set objFileSystem = Nothing
  Set objFolder = Nothing
  Set objFile = Nothing
  Exit Function
  
ErrHandler:
  GetLatestPath = Empty
  Resume ExitProc
End Function

Private Function DateFromFilename(strFilename As String) As Date
  Dim astrFilename() As String
  Dim strFileDate As String
  
  On Error GoTo ErrHandler
  astrFilename = Split(strFilename, " - ")
  strFileDate = astrFilename(1)
  strFileDate = Format(strFileDate, "####-##-##")
  DateFromFilename = DateValue(strFileDate)
  Exit Function

ErrHandler:
  DateFromFilename = CDate(0)
End Function