Error 52 Bad file name or number - One Drive Business

DECOVIOTI

New Member
Joined
Dec 11, 2020
Messages
22
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Attachments

  • Capture22.PNG
    Capture22.PNG
    9.5 KB · Views: 29
  • Capture1.PNG
    Capture1.PNG
    6.1 KB · Views: 31
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

The Windows OS file system cannot handle strings like
Rich (BB code):
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.

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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top