Trying to save Macro enabled Excel file to Sharepoint with filename from cell

Flyin Brian

New Member
Joined
Jan 15, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
They say a little knowledge is a dangerous thing. Alas...

I have scoured this and other sites but can’t seem to find an answer to my problem:

I am trying to save a Macro-enabled Excel file to a shared folder in our Company’s Enterprise SharePoint.

The file name will be dependent on a concatenated text string found in a cell.

So far, I have managed to glean that I need to use backslashes, ie: ‘\\mysharepoint.SharePoint.com\rest_of_the_path
I need to add the & “\” after the path to separate the path from the filename,

Then for the filename, I have, filename1 = Range(“A1”)

And then I put it all together for it to say,

Dim Path As String
Dim filename1 as String
ThisWOrkbook.SaveAs filename1:=Path & “\” & filename1 & “xlsm”, FileFormat:=xLOpenXMLWorkbookMacroEnabled

But.. no matter what I do, it comes back as “Run-time error 1004 - Method ‘SaveAs’ of object ‘_Workbook’ failed.
The DeBug then takes me to the above line.

What am I doing wrong?

Any assistance would be greatly appreciated!

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
VBA Code:
Dim strPath As String
Dim strFile as String

strPath= "https://mysharepoint.SharePoint.com/rest_of_the_path/"
strFile = Range("A1")

ThisWOrkbook.SaveAs FileName:=strPath & strFile & ".xlsm", FileFormat:=xLOpenXMLWorkbookMacroEnabled
 
Upvote 0
I have now spent another chunk of time, trying various different approaches: I have changed the path, I have changed the file name. I see now that at least the editor is recognizing the values that are represented by FilePath and Thisfile.

But no matter what I do, I still come up with “Run-Time Error 1004 - Method ‘SaveAs’ of object’_Workbook’ failed” on the last line.

I’m at the end of my rope.

This is what I have in its entirety now:

VBA Code:
Sub Save_to_Sharepoint()

Dim FilePath As String
Dim Thisfile As String

FilePath = "\\[my_sharepointsite][URL='http://scbcta-my.sharepoint.com/'].sharepoint.com[/URL]\:f:\g\personal\[my_account]\gobbledegook-link"

Thisfile = Range("ai44").Text

ThisWorkbook.SaveAs filename:=FilePath & "\" & Thisfile & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, ConflictResolution:=xlLocalSessionChanges

End Sub

What am I doing wrong?
 
Upvote 0
I’ll try typing the link again:
"\\[my_sharepointsite].sharepoint.com\:f:\g\personal\[my_account]\gobbledegook-link"
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top