How do I upload a file to OneDrive Business (Sharepoint) with VBA

Question

I'm trying to find a method to upload a file to Onedrive from VBA in Excel. I've done a lot of searching for acceptable methods, but most methods will not work for my scenario or the proposed solution will give an error.

I can upload files just fine if I'm using UNC paths or OneDrive locations that are synced locally (e.g. "C:\Users(username)\OneDrive\File Share") but I need a method that lets me push(upload) files to a shared URL location (e.g. "https://my.sharepoint.com/:f:/r/personal/(email_address)/Documents/SharedFiles?csf=1&e=6WmUIO"). All the users that will need to use the tool will have access to this shared location in OneDrive.

I tried the normal "SaveAs" method in VBA but that won't work.

Set Excelwb = ThisWorkbook

Excelwb.SaveAs fileName:="https://my.sharepoint.com/:f:/r/personal/(email_address)/Documents/SharedFiles?csf=1&e=6WmUIO" _
, FileFormat:=xlOpenXMLWorkbook, ConflictResolution:=xlLocalSessionChanges

Excelwb.Saved = True
Excelwb.Close SaveChanges:=False
Application.DisplayAlerts = True

I expected this to save the file, but I understand that since I'm saving to a URL and not a local file that another method is probably required, but I can't find one that will work.

The error I'm getting is: Run-time error '1004' Method 'SaveAs' of object '_Workbook' failed

I am not too familiar with saving to OneDrive via VBA (I am actually curious myself now that I see this question). I wonder if it may have to do with dealing with login credentials since you are saving to a server. I am at work right now and OneDrive is blocked here, but perhaps try recording a macro and saving and see what the macro does. — K.Dᴀᴠɪs
K.Davis. There isn't a way that I can see to upload the document to the OneDrive shared folder from within Excel's main interface. The only OneDrive Business data I have access to is through the OneDrive app on my computer that lets me access MY DATA, but not the Shared Folders that I'm trying to save a copy of the Excel document to. — BuffaloX
@TimWilliams I appreciate the suggestion. I have run across the solution before to map OneDrive to a UNC path but I'm worried that this may fail when trying to do it for other people who run my tool. I'd like to find a more universal way, if possible (like uploading it through an API call). — BuffaloX

Tim Williams Tim Williams · Accepted Answer · 2019-07-11T19:23:17

I just tried this and it worked fine:

Activeworkbook.SaveAs "https://myComany.sharepoint.com/Departments/dept1/Documents/FGH/Text.xlsx"

To get the required path, browse to the destination in IE, then use Library tab >> Open with Explorer to open the destination in Windows Explorer: you can copy the path from the address bar there.

How do I upload a file to OneDrive Business (Sharepoint) with VBA

2 Answers