use vba to save a file in SharePoint

Question

Office365 for Business, all apps are up to date as is Windows 11

I have an Excel workbook in a SharePoint folder that runs some VBA code. When the code finishes I want to save the workbook in a different SharePoint folder. What would the VBA code look like?

Application.ActiveWorkbook.Path & "\new_file.xlsx" returns the following string

https: //abc.sharepoint.com/sites/abcShared/Shared Documents/General/Company Shared Directory/Users/Sam (aka EA)/Bob Automate\new_file.xlsx

Answer 1

Try this one.

===================================

Sub MakeBackup()

    Dim Home As String 

    Dim HomePath As String 

    Dim BackupPath As String    

    Home = ThisWorkbook.Path 

    HomePath = Home & "/BOB\_Finalv5a.xlsm" 

    BackupPath = Home & "/Copies/BOB\_Finalv5a\_backup.xlsm" 

    On Error GoTo ErrorHandler 

    'Save this file to the current folder and the new file to a new location 

    ActiveWorkbook.SaveAs Filename:=BackupPath, FileFormat:=xlOpenXMLWorkbookMacroEnabled 

    ActiveWorkbook.SaveAs Filename:=HomePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled    

ErrorHandler:

MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Save Error" 

End 

End Sub

Answer 2

To save an Excel workbook in a different SharePoint folder using VBA, you can use the following code:

Sub SaveToSharePoint()

    Dim newFilePath As String

    'Specify the new file path in SharePoint

    newFilePath = "https://abc.sharepoint.com/sites/abcShared/Company Shared Directory/Users/Sam (aka EA)/Bob Automate/new\_file.xlsx"

    'Save the file to the new location

    ActiveWorkbook.SaveAs Filename:=newFilePath, FileFormat:=xlOpenXMLWorkbook

End Sub

Answer 3

https://learn.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0#sharepoint-api-root-resources

Answer 4

Snow,

I modified your snippet so that it now looks like this

Sub MakeBackup()

    Dim Home As String 

    Dim HomePath As String 

    Dim BackupPath As String 

    Home = ThisWorkbook.Path 

    HomePath = Home & "/BOB\_Finalv5a.xlsm" 

    BackupPath = Home & "/Copies/BOB\_Finalv5a\_backup.xlsm" 

    On Error GoTo ErrorHandler 

    'Save this file to the current folder and the new file to a new location 

    ThisWorkbook.SaveCopyAs fileName:=BackupPath 

    ActiveWorkbook.SaveAs fileName:=HomePath, FileFormat:=xlOpenXMLWorkbookMacroEnabled 

ErrorHandler:

MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Save Error" 

End 

End Sub

When I run the workbook code this is the error message I get

Even though the folder exists

Any thoughts on why the code can't find the folder? The only thing I can think of is that it is a cloud based folder. I just started to have all files downloaded to my computer. This will take a while so I'll be checking it in the morning.

Thank you for your help

Answer 5

This worked perfectly, thank you.

There was one issue in that the way the code is written it will generate a message indicating that there was a error 0. Fixed that with a simple if statement.