vba - 当外部工作表托管在 Office 365 OneDrive For Business 帐户上时，如何从外部工作表中获取数据？

Question

我正在使用 Office 365 Excel Online 功能，该功能能够创建在线表单(公开共享)来收集直接保存到工作簿工作表上的表中的响应。

为了分析数据，我添加了使用 VBA 代码和表单控件的额外工作表。但是，Excel Online 不支持表单控件，所以我想我需要两个工作簿。一个用于存储表单数据，另一个用于对数据进行分析。包含表单的文件必须保留在 Office 365 Onedrive For Business 上，该功能才能正常工作。如果我保留表单控件并尝试在 Excel Online 中打开文件(以编辑/共享表单)，我必须编辑删除控件的副本 - 它不会忽略它们。

分析工作簿将由多个人在多台 PC 上打开。我已经启用了对表单文件的匿名访问，这就是 Onedrive 给我的:
https://carlisleft-my.sharepoint.com/personal/dmartin_carlisleft_onmicrosoft_com/_layouts/15/guestaccess.aspx?docid=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&authkey=XXXXXXXXXXXXXXXXXXX

我尝试使用 Get External Data 和 New Query From File 并指定此 URL，但我要么可以访问不下载任何数据的 Web 界面，要么可以访问与 URL 相关但不是我需要的实际数据的 Sharepoint 表。

我现在想用VBA下载一份文件，在后台本地打开，读取表数据，复制到分析文件中然后删除下载，关闭连接。我假设使用正确的代码，我可以正确使用 docid 和 authkey。

我已将 Application.GetOpenFilename 和 Workbooks.Open() 用于本地存储的文件，但它不适用于此。我找到了这个答案 Access Shared Files in Onedrive For Business但这不是VBA，也不是很清楚。我还发现了这个 that talks about WebDav但这是我需要做的吗？

有没有人有任何想法或找到了从 OneDrive for Business excel 文件中获取 Excel 数据的成功方法？我必须能够让它在任何 Windows 10 PC 上打开。因此，如果我必须映射网络驱动器，它们必须即时完成。

Answer 1

看起来这个问题并不是那么简单。我的解决方案是把它颠倒过来。而不是有两个必须相互通信的单独文件。只需保留一个文件即可为 Excel Online 提供所需的内容；没有表单控件保存的文件。

使用来自具有非常好的 tutorial and examples for creating form controls with VBA 的站点的信息.每次在 Excel Desktop 中启动工作表时，我都能够编写创建表单控件的程序。在保存文件之前，它们已被删除。 Excel Online 不运行 VBA 代码，因此看不到任何控件并打开文件。 Excel Desktop 运行代码并创建控件，以便用户可以在界面中使用它们。

只需使用 Workbook_Open()和 Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook 对象中的事件以使 Excel 运行您的代码。

删除 BeforeSave 的所有表单控件的代码如下所示:

Sub deleteAll()
'deletes all msoFormControl in the current Workbook
Dim shpControl As Shape
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
For Each shpControl In ws.Shapes
If shpControl.Type = msoFormControl Then
shpControl.delete
End If
Next shpControl
Next ws
End Sub