CODEWITHSUNDEEP
excelvbasharepointsave-as
MASS_PANIC
MASS_PANIC

Reputation: 21

VBA: Save to sharepoint

I am trying to build a macro that save excel file to sharepoint based on cell value.

I have two cells that:

*the sharepoint path is copied from Teams using copy link.

Below is the VBA that I used. But whenever I run it, it will show this error.

Run-time error '1004': Method 'SaveAs' of object '_Worksheet' failed

Sub filename_cellvalue()
Path1 = Range("A1").Value
myfilename = Range("A2").Value
ActiveWorkbook.SaveAs Filename:=Path1 & myfilename & ".xlsx"
End Sub

Thank you in an advance.

Upvotes: 0

Views: 3979

Answers (1)

Cornelius J. van Dyk
Cornelius J. van Dyk

Reputation: 728

Saving a worksheet to another file location in SharePoint will only work if ALL the following are true:

  1. The current user account has write access to the target location.
  2. The active document source is inside the same SharePoint environment as the target.
  3. The SharePoint environment does NOT block macro enabled files.
  4. The file is being saved as a macro enabled file considering we're literally writing the macro to do the Save As. It doesn't matter that there's no other code on the worksheet. This is a macro and therefore the worksheet will have to be saved as such.

When all these conditions are met, the following should do the trick:

Public Sub SaveToSharePoint()
targetFile = Range("A1").Value
targetPath = Range("B1").Value
ActiveWorkbook.SaveAs Filename:=targetPath & targetFile & ".xlsm"
End Sub

Upvotes: 3

Related Questions