Selecting .csv file and importing data from that .csv file on a SharePoint path into Excel with a VBA Macro

Question

I want to import the data of a .CSV file into excel with a VBA macro from SharePoint.

Using the local folder path to look-up and select the correct .csv file (the most recent file in the folder) in the VBA macro works.

Replacing the local folder path with the online sharepoint path does not work.

As advised by Copilot, I have installed the JsonConverter.bas file for parsing and checked the Microsoft Scripting Runtime.

Using Copilot, I also addressed error messages, mainly first, the VBA macro did not find the file, then it did not find the folder.

Please advise how to modify the macro that it works (Copilot has no more new answers available).

Here is the macro (for data protection, I anonymize the sharepoint path):

 

Sub ImportLatestCSV()
    Dim folderPath As String
    Dim fileName As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim fileDate As Date
    Dim ws As Worksheet
    Dim fso As Object
    Dim folder As Object
    Dim file As Object

    ' Set the SharePoint folder path
    folderPath = "https://xxxxxxx/"

    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Check if the folder exists
    If Not fso.FolderExists(folderPath) Then
        MsgBox "The specified folder was not found.", vbExclamation
        Exit Sub
    End If
    
    ' Get the folder
    Set folder = fso.GetFolder(folderPath)
    
    ' Initialize variables
    LatestFile = ""
    LatestDate = DateSerial(1900, 1, 1) ' Set to an old date
    
    ' Loop through each file in the folder
    For Each file In folder.Files
        If LCase(fso.GetExtensionName(file.Name)) = "csv" Then
            ' Get the file date
            fileDate = file.DateLastModified
            ' Check if this file is the latest
            If fileDate > LatestDate Then
                LatestDate = fileDate
                LatestFile = file.Name
            End If
        End If
    Next file

    ' Check if a file was found
    If LatestFile = "" Then
        MsgBox "No CSV files found in the folder.", vbExclamation
        Exit Sub
    End If

    ' Import the data from the latest file
    Set ws = ThisWorkbook.Sheets("1) New IB")
    ws.Cells.ClearContents ' Clear existing data

    With ws.QueryTables.Add(Connection:="TEXT;" & folderPath & LatestFile, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(1)
        .Refresh BackgroundQuery:=False
    End With

    MsgBox "Data imported successfully from " & LatestFile, vbInformation
End Sub