Error 52 Bad file name or number - One Drive Business

[DECOVIOTI]

Hello

I have an Excel spreadsheet saved on my One Drive for Business called "MasterXXX.xlsm" which I am trying to run the VBA script below, however I am getting the run time error "Error 52 Bad File Name".
When I try it in my own local drive, I can run it perfectly.
I have already looked at different forum/pages, and I did not find any solution to solve this issue.

Could anyone help me, pls?

Sub consWB()

Dim wb As Workbook, sh As Worksheet, lr As Long, lc As Long, fName As String, fPath As String
fPath = ThisWorkbook.Path 'Code is run from workbook in same directory
Debug.Print fPath
If Right(fPath, 1) <> "\" Then fPath = fPath & "\" 'Be sure separator is in path string
fName = Dir(fPath & "*.xl*", vbNormal) '******Error 52 Bad File Name******* or Number 'Any Excel file
Debug.Print fName
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then 'Avoid trying to open a second instance the workbook running the code
            Set wb = Workbooks.Open(fPath & fName)
            For Each sh In wb.Sheets 'This For Next loop can be replaced with other code to do other things.
                lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
                lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
                sh.Range("A2", sh.Cells(lr, lc)).Copy ThisWorkbook.Sheets(sh.Name).Cells(Rows.Count, 1).End(xlUp)(2)
            Next
        End If
        ThisWorkbook.Save 'This is optional, but can be useful in case of catastrophic failure of the system.
        wb.Close False 'Avoids alert messages if there are auto calculations in the opened workbook, like date updates, etc.
        fName = Dir
    Loop
End Sub

Upload Image

 

[DECOVIOTI]

Perhaps, I was not able to express myself properly. If it is the case, I apologize.
The result which is showed is the path of the file

https://company-my.sharepoint.com/personal/user/Documents/Reports/Folder1/Folder2

The Windows OS file system cannot handle strings like

https://company-my.sharepoint.com/personal/user/Documents/Reports/Folder1/Folder2

In the thread below in the end the OP found a solution himself with a similar issue. See if it can help you.

Copy content from all files stored on a SharePoint Folder

Dear All, Hope you're all well and safe. I am building a dashboard that will pull data from stored files in a SharePoint Folder. All files are identical in terms of columns and rows, only the data differs. All files extension is ."xlsx". There can be only one file but could have 10 files or...

www.mrexcel.com

I do appreciate for your reply. In my context of work, that solution is not applicable. Unfortunately.

 

[GWteB]

Too bad, thanks for the feedback

 

[DECOVIOTI]

Too bad, thanks for the feedback

Thanks for the help. I will be looking / waiting for others