(Solved) – Open two last modified files in Sharepoint


I want to write a script which opens the two last modified files in a sharepoint folder called “Test”. These are Excel CSV files, so they should open in Excel. This is what I came up with, it seems to work for a local folder, but not for Sharepoint.

Sub test()

' Test Macro
    Application.ScreenUpdating = False
    Dim SummaryWB As Workbook
    Dim FileSys, objFile, myFolder, c As Object
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim strFilename, strFilename2
    FolderName = ("https://sharepoint.com/sites/Team/Shared Documents/Test")
          Set FileSys = CreateObject("Scripting.FileSystemObject")
           Set myFolder = FileSys.GetFolder(FolderName)

    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If InStr(1, objFile.Name, ".csv") > 0 Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename2 = strFilename
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    Set wb1 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename)
    Set wb2 = Workbooks.Open(FolderName & Application.PathSeparator & strFilename2)
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *