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

 

[GWteB]

Your code contains this line:

Debug.Print fPath

What does the fPath variable return in the Immediate window (press Ctrl+G) in the VBE?

 

[DECOVIOTI]

Hello
I am just tried to see the results of the path.
It was not causing the problem.
Thanks

 

[GWteB]

It was not causing the problem.

This suggests that your problem has been solved. If so, would you mind sharing your solution with us?

 

[DECOVIOTI]

This suggests that your problem has been solved. If so, would you mind sharing your solution with us?

The issue was not solved.
I am still having the the error when I run the macro

I m still need for assistance

 

[GWteB]

Ok, then how did you find out that the string assigned to fPath is not the problem?

 

[DECOVIOTI]

I have added a picture as attachment as well. I have just run t he macro again wihout the Debug.Print. line and I am still have the error as a i mentioned at the beginning of my post. I am trying to run it in the ONE DRIVE.

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
If Right(fPath, 1) <> "\" Then fPath = fPath & "\" 'Be sure separator is in path string
fName = Dir(fPath & "*.xl*") 'Any Excel file
    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

 

[GWteB]

I've seen your attachments pointing out the line of code the error occurs. The error is thrown by the DIR statement, which uses the fPath variabele, that's why I asked you what that variable looks like in the Immediate window after you've pressed the Debug button. But nevermind, just as everyone is free to ask questions, everyone is also free not to answer them, like you do.

 

[DECOVIOTI]

I've seen your attachments pointing out the line of code the error occurs. The error is thrown by the DIR statement, which uses the fPath variabele, that's why I asked you what that variable looks like in the Immediate window after you've pressed the Debug button. But nevermind, just as everyone is free to ask questions, everyone is also free not to answer them, like you do.

I do appreciate your assistance @GWteB .
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

 

[GWteB]

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