How to get SaveCopyAs to work on Sharepoint

SaveCopyAs? Sharepoint? What?

I was trying to backup an Excel sheet to a folder on Sharepoint, using VBA.

SaveCopyAs kept giving me a runtime error 1004.

Sub BackUp() 'Creates a back of the file before prepping for next week
Dim relativePath As String

relativePath = ThisWorkbook.Path & "/" & "test.xlsm"
ActiveWorkbook.SaveCopyAs (relativePath)

End Sub

My macro would be used by multiple people, so I had to use ThisWorkbook.Path, couldn’t exactly specify it. Kept getting the error. SaveAs worked. SaveCopyAs worked when manually specified the the path. But my code didn’t.

SaveAs was also annoyingly slow to save on Sharepoint, whereas SaveCopyAs instantly did it.

Solution?

Not really, but i got past it.

You use the manual path, but really the only thing different between multiple people will be the username. Excel knows the username.

Sub BackUp() 'Creates a back of the file before prepping for next week


Dim weeknumber As String
Dim username As String

    weeknumber = Worksheets("Macro Settings").range("C2").Value
    username = Application.username
    
    ThisWorkbook.SaveCopyAs Filename:="C:\Users\" & username & "\Company\Folder - Documents\Technical\Projects\Forecasting\Records\" & weeknumber & " Test " & "(Backup) " & Format(Now(), "(yyyy-mm-dd hhmm)") & ".xlsm"

End Sub

This allowed me to use SaveCopyAs and seems to work for all the users I have tried.