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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
 

Flyin Brian

New Member
Joined
Jan 15, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
Thanks, AlphaFrog. Sadly, it’s still not working.
 

Flyin Brian

New Member
Joined
Jan 15, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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?
 

Flyin Brian

New Member
Joined
Jan 15, 2021
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
I’ll try typing the link again:
"\\[my_sharepointsite].sharepoint.com\:f:\g\personal\[my_account]\gobbledegook-link"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

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
Top