Merge Excel Files Saved in SharePoint

[JVRamoso02]

Hi,

I have a macro in my local drive which allows me to merge multiple workbooks saved in a specific folder into one workbook. I need these files to be stored in SharePoint instead of local drive but when I try to change the path to a destination in SharePoint, the macro returns an error for "FileName = Dir(Path & "\*.xlsm", vbNormal)" and I cannot figure out how I can fix it. Could anyone please help me how I can rewrite the codes to make it work? Appreciate your help.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">Option Explicit
 
Sub CallMacros()
     
    Dim Path            As String
    Dim FileName        As String
    Dim Wkb             As Workbook
    Dim Ws              As Worksheet
    Dim Ary             As Variant
     
    'merge workbooks into one
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Path = "https:\\*****group.sharepoint.com\teams\InnovationSupportAPACBCR\Shared Documents\TEST MACRO\Countries\" 'Change as needed
   [COLOR=#FF0000]FileName = Dir(Path & "\*.xlsm", vbNormal)[/COLOR]
    Do Until FileName = ""
        Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
        For Each Ws In Wkb.Worksheets
            Ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
        Next Ws
        Wkb.Close False
        FileName = Dir()
    Loop
    Application.EnableEvents = True
    Application.ScreenUpdating = True</code>