CODEWITHSUNDEEP
excelvbasharepoint
AKdelBosque
AKdelBosque

Reputation: 105

Saving Excel Files to Sharepoint using VBA

I am attempting to run a query in Excel and update the data to Sharepoint (accessible via Teams) on a set schedule.

Desired folder destination:

https://rxsafeway.sharepoint.com/:f:/r/sites/EXELiveProductionTool/Shared%20Documents/General/Portland/Individual%20Performance%20Update?csf=1&web=1&e=oFwj6i

VBA code used:

ThisWorkbook.SaveAs Filename:="https://rxsafeway.sharepoint.com/sites/EXELiveProductionTool/Shared%20Documents/General/Portland/Individual%20Performance%20Update/" & ".xlsm"

Details:

-The above VBA code has been used successfully while connected to VPN and on the company network.

-It works inconsistently... Potentially an upload limit?

-Notice that the "/:f:/r/" has been removed, a "/" has been added after "Update", and everything passed "Update" has been removed in the working code

Question:

Sometimes the code works and sometimes I get a Run-time error '1004': Method 'SaveAs' of object '_Workbook' failed

Is there a syntax error, or a smarter way to save the file?

Upvotes: 1

Views: 5819

Answers (1)

Timeless
Timeless

Reputation: 37827

You probably getting this error due to the MAX_PATH 256 limitation.
Your filename is too long and therefore will have a longer Windows path as well.

I suggest you to view your SharePoint files in File Explorer (see the documentation here). By doing so, every file/folder in your sharepoint will have a normal path and you'll not have anymore to use your file's url.

After that, you can use this formula to save your xlsm in some location in sharepoint :

Path="C:\Users\AKow\Sharepoint_Name\Path_to_your_file"
Filename = "Individual Performance Update"
ThisWorkbook.SaveAs Filename:= Path & Filename, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Upvotes: 0

Related Questions