Search code examples
excelvbaonedrive

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

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

Solution

  • 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.