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.