如何使用 VBA 将文件上传到 OneDrive Business (Sharepoint) [英] How do I upload a file to OneDrive Business (Sharepoint) with VBA

问题描述

我正在尝试找到一种方法，将文件从 Excel 中的 VBA 上传到 Onedrive.我已经搜索了很多可接受的方法，但大多数方法都不适用于我的场景，或者建议的解决方案会出错.

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.

如果我使用本地同步的 UNC 路径或 OneDrive 位置(例如C:\Users(username)\OneDrive\File Share")，我可以很好地上传文件，但我需要一种方法来让我推送(上传)文件到共享 URL 位置(例如https://my.sharepoint.com/:f:/r/personal/(email_address)/Documents/SharedFiles?csf=1&e=6WmUIO").所有需要使用该工具的用户都可以访问 OneDrive 中的此共享位置.

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.

我在 VBA 中尝试了普通的另存为"方法，但不起作用.

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

我希望这能保存文件，但我知道由于我保存的是 URL 而不是本地文件，因此可能需要另一种方法，但我找不到可行的方法.

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.

我得到的错误是:运行时错误1004"对象_Workbook"的方法SaveAs"失败

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

推荐答案

我刚试过这个，效果很好:

I just tried this and it worked fine:

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

要获取所需的路径，请在 IE 中浏览到目标，然后使用库选项卡 >> 使用资源管理器打开在 Windows 资源管理器中打开目标:您可以从那里的地址栏中复制路径.

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.

这篇关于如何使用 VBA 将文件上传到 OneDrive Business (Sharepoint)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！