VBA Run-time error '52' : Bad file name or number - save file on Sharepoint

[alantse2010]

Hello

I have an Excel spreadsheet saved on my Sharepoint called "SRQ.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?

Sub Generate_296699_CCA()
Dim wb As Workbook
Dim wsw As Worksheet
Dim y As Workbook
Dim sDirectory As String
Dim sFilename As String
Dim sheet As Worksheet
Dim lastRow, lastRow2 As Long
Dim maxRwoNo As Long
Dim sImportFile As String
Dim readsheetName As String
Dim destsheetName As String
Dim SourceRange, SourceRange2, SourceRange3, SourceRange4 As Range
Dim fso As Object, FolDir As String, FileNm As Object, NumStr As Integer, MaxNum As Integer
Dim NewName As String, StrNum As String, MaxStr As String
Dim FolderStr As String 'Object
MaxNum = 1
FolderStr = "https://abcde.sharepoint.com/sites/VBA%20Trial/SRQ%20VBA%20Test"

FolDir = Dir(FolderStr)
MsgBox (FolDir)
readsheetName = "2011-2019"
destsheetName = "Cable Collection Advices (2)"
Set wb = ThisWorkbook
Set wsw = wb.Sheets(readsheetName)

wsw.Activate

Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Filtered Data").Delete


On Error GoTo 0
Application.DisplayAlerts = True
 Application.ScreenUpdating = False
Set wsDest = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
 wsDest.Name = "Filtered Data"
MM1
wsw.Range("A1:U1").AutoFilter Field:=7, Criteria1:="296699"
wsw.Range("A1:U1").AutoFilter Field:=14, Criteria1:="Available", Operator:=xlOr, Criteria2:="="
If wsw.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
 
  wsw.Cells.SpecialCells(xlCellTypeVisible).Copy
   wsDest.Activate
   wsDest.Range("A1").PasteSpecial xlPasteFormulasAndNumberFormats
  
   wsDest.Columns("N:U").Delete
   wsDest.Columns("A:B").Delete
   wsDest.Columns("F").Delete
   wsDest.Rows(1).Delete
    lastRow = wsDest.Range("A" & Rows.Count).End(xlUp).Row
   'SRQ C to F
   Set SourceRange = wsDest.Range("A1:D" & lastRow)
   Set SourceRange2 = wsDest.Range("F1:G" & lastRow)
   Set SourceRange3 = wsDest.Range("E1:E" & lastRow)
   Set SourceRange4 = wsDest.Range("J1:J" & lastRow)
   SourceRange.Copy
  
   'wsDest.Range("A1:D" & lastRow).Copy
   'wsDest.Columns(9).Delete
   Set y = Workbooks.Open("https://abcde.sharepoint.com/sites/SRQ%20VBA%20Test/Cable%20Collection%20Advices%20-%2011.xls")
   y.Sheets(destsheetName).Range("C8").PasteSpecial xlPasteValues
   SourceRange2.Copy
   y.Sheets(destsheetName).Range("G8").PasteSpecial xlPasteValues
   SourceRange3.Copy
   y.Sheets(destsheetName).Range("I8").PasteSpecial xlPasteValues
   SourceRange4.Copy
   y.Sheets(destsheetName).Range("J8").PasteSpecial xlPasteValues
   lastRow2 = wsDest.Range("C" & Rows.Count).End(xlUp).Row
   y.Sheets(destsheetName).Range("A8:A" & lastRow2 + 7).Value = Format(Now(), "dd.mm.yyyy")
   y.Sheets(destsheetName).Range("B5").Value = Format(Now(), "dd.mm.yyyy")
   Application.DisplayAlerts = False
   Do While Len(FolDir) > 0
   If FolDir Like "Cable Collection Advices - " & "*" & ".xlsx" Then
   StrNum = Right(Left(FolDir, 32), 5)
   'MsgBox "StrNum" & StrNum
    NumStr = CInt(StrNum)
    If NumStr > MaxNum Then
    MaxNum = NumStr
    End If
    End If
    FolDir = Dir
    'Next FileNm
    Loop
    MaxStr = CStr(Format(MaxNum + 1))
    NewName = FolderStr & "Cable Collection Advices - " & MaxStr & ".xlsx"
 
    y.SaveAs Filename:=NewName, FileFormat:=51, CreateBackup:=False

   y.Close SaveChanges:=False
   ActiveWorkbook.Worksheets("Filtered Data").Delete
   wsw.Activate
   MM1
   'Application.DisplayAlerts = True
Else
  MsgBox ("296699 No data")
  Application.DisplayAlerts = False
  ActiveWorkbook.Worksheets("Filtered Data").Delete
   Application.DisplayAlerts = True
   wsw.Activate
   MM1
End If


End Sub
Sub MM1() 'close all the worksheet autofilter
Dim ws As Worksheet
For Each ws In Worksheets
 'ws.AutoFilterMode = ShowAllData
    With ws
        If .AutoFilterMode Then
            If .FilterMode Then
            .ShowAllData
            End If
        End If
    End With
Next ws
End Sub

 

[nemmi69]

The file path and file name cannot have % in them when saved to a drive like C:\ . You will need to remove these 1st.

 

[alantse2010]

The file path and file name cannot have % in them when saved to a drive like C:\ . You will need to remove these 1st.

Thank you for your reply, i try to remove %20 in the file path and file name, it not work and still has Run-time error '52' : Bad file name or number .

The file path is copied on the sharepoint

Thank you.

 

[nemmi69]

A local save will look something like "C:\foldername\subfoldername\filename.xlsx"

 

[alantse2010]

A local save will look something like "C:\foldername\subfoldername\filename.xlsx"

A local save is work but i need to share with others to use this VBA file

 

[daverunt]

See if any of this is useful.

Open an Excel file from SharePoint site

I'm trying to open an Excel file from SharePoint using VBA. Because the file I'm looking for might be different each time I run the macro, I want to be able to view the SharePoint folder and select...

stackoverflow.com

 

[nemmi69]

Just a note. With Sharepoint, a save back may not happen if 1) you are not the owner or have permissions 2) IT has blocked this from happening. In our case here it was 2). In both cases it may say it cannot find the file.

 

[alantse2010]

Just a note. With Sharepoint, a save back may not happen if 1) you are not the owner or have permissions 2) IT has blocked this from happening. In our case here it was 2). In both cases it may say it cannot find the file.

OIC, thank you for your note