Saving Excel Files to Sharepoint using VBA

Question

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?

Timeless · Accepted Answer · 2022-08-17 05:43:22Z

0

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

answered Aug 17, 2022 at 5:43

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AKdelBosque Over a year ago

Thank you, @L'Artiste. I will need others to view the output files in Teams, so I'm not sure using Sync will help. I attempted my original code again this morning (believing this is possibly a write to Sharepoint limitation in my network) and the code failed. I used your syntax and it worked once - then failed on the second attempt.

Collectives™ on Stack Overflow

Saving Excel Files to Sharepoint using VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related