Excel macro opening file from sharepoint online

I was recently working with a set of excel spreadsheets that have macros to consolidate data from multiple diffrent files into a single consolidated file. I needed to move these to sharepoint online, and it took me a long time to find the correct URL format to pass to the Workbooks.Open Filename parameter in order to open the sharepoint URL. What worked for me was the following URL format:
\\tenant.sharepoint.com@SSL\sitename\path\filename

You can find the correct URL for a specific file by opening \\tenant.sharepoint.com@SSL\ in an explorer window and browsing.

The code to open a file and copy and paste is as follows:

Sub ClearBI()
    'Quick search in destination for last non blank row
    Sheets("BI").Select
    Skiprows = 32768
    ThisRow = 2
    Do While Skiprows >= 1 And Cells(ThisRow, 1) <> ""
        If Cells(ThisRow + Skiprows, 1) <> "" Then
            ThisRow = ThisRow + Skiprows
        Else
           Skiprows = Skiprows / 2
        End If
    Loop
    Range(Cells(2, 1), Cells(ThisRow, 4)).Select
    Selection.ClearContents
End Sub

Sub CopyData()
    ConsolidatedWindowName = Application.ThisWorkbook.Name
    Windows(ConsolidatedWindowName).Activate

    ClearBI
    
    ActiveWorkbook.PrecisionAsDisplayed = False
    Application.ScreenUpdating = False
    Row_Counter = 12
    
    Windows(ConsolidatedWindowName).Activate
    
    Do While Sheets("FileNames").Cells(Row_Counter, 1) <> ""
        Windows(ConsolidatedWindowName).Activate
        
        ThisFileName = Sheets("FileNames").Cells(Row_Counter, 2)
        
        'Show active file name in status bar (so you can see progress)
        Application.StatusBar = "Transferring Data from " & ThisFileName & "...Please be patient..."
        
        OpenFileName = Sheets("FileNames").Cells(Row_Counter, 3)
        PathName = OpenFileName
        
        Workbooks.Open Filename:=PathName, UpdateLinks:=0
        
        ' Copy 1st set of Data from each location source file
        Windows(ConsolidatedWindowName).Activate
        Sheets("Dbase").Select
        PositionCell_1
        
        ' Copy the BI data from source
        Windows(ThisFileName).Activate
        Sheets("BI").Select
        Skiprows = 32768
        ThisRow = 2
        
        'Quick search for first non blank row
        Do While Skiprows >= 1 And Cells(ThisRow, 1) <> ""
            If Cells(ThisRow + Skiprows, 1) <> "" Then
                ThisRow = ThisRow + Skiprows
            Else
               Skiprows = Skiprows / 2
            End If
        Loop
        
        Range(Cells(2, 1), Cells(ThisRow, 4)).Select
        Selection.Copy
        
        SourceRows = ThisRow - 1
        
        'Quick search in destination for last non blank row
        Windows(ConsolidatedWindowName).Activate
        Sheets("BI").Select
        Skiprows = 32768
        ThisRow = 2
        'If there is data, find the first blank row
        If Cells(ThisRow, 1) <> "" Then
            Do While Skiprows >= 1 And Cells(ThisRow, 1) <> ""
                If Cells(ThisRow + Skiprows, 1) <> "" Then
                    ThisRow = ThisRow + Skiprows
                Else
                   Skiprows = Skiprows / 2
                End If
            Loop
            'Increment to the first blank row
            ThisRow = ThisRow + 1
        End If

        Application.Goto ("R" & ThisRow & "C1")

        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        ' Close Source File
        Windows(ThisFileName).Activate
        Application.CutCopyMode = False
        Workbooks(ThisFileName).Close SaveChanges:=False
        Row_Counter = Row_Counter + 1
    Loop
    
    MsgBox ("Consolidation Complete!")
    
    Sheets("FileNames").Select
    Range("B6").Select
End Sub