从 SharePoint 站点打开 Excel 文件

本文介绍了从 SharePoint 站点打开 Excel 文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试使用 VBA 从 SharePoint 打开 Excel 文件.因为每次运行宏时我要查找的文件可能都不同，所以我希望能够查看 SharePoint 文件夹并选择我需要的文件.

I'm trying to open an Excel file from SharePoint using VBA. Because the file I'm looking for might be different each time I run the macro, I want to be able to view the SharePoint folder and select the file I need.

当我想在网络驱动器上查找文件时，下面的代码工作正常，但是当我用 SharePoint 地址替换它时，我收到运行时错误 76:找不到路径".

The code below works fine when I want to look for a file on a network drive, however when I replace that with a SharePoint address I get "run-time error 76: Path not found".

Sub Update_monthly_summary() Dim SummaryWB As Workbook Dim SummaryFileName As Variant ChDir "sharepoint/my/file/path" SummaryFileName = Application.GetOpenFilename("Excel-files,*.xls", _ 1, "Select monthly summary file", , False) If SummaryFileName = False Then Exit Sub Set SummaryWB = Workbooks.Open(SummaryFileName) End Sub

当我将此地址粘贴到 Windows 资源管理器时，访问 SharePoint 文件夹没有问题，因此我知道路径是正确的.

When I paste this address into Windows Explorer I have no problems accessing the SharePoint folder, so I know the path is correct.

为什么 VBA 不喜欢它?

Why doesn't VBA like it?

推荐答案

尝试使用以下代码从 SharePoint 站点中选取文件:

Try this code to pick a file from a SharePoint site:

Dim SummaryWB As Workbook Dim vrtSelectedItem As Variant With Application.FileDialog(msoFileDialogOpen) .InitialFileName = "sharepoint/team/folder" & "" .AllowMultiSelect = False .Show For Each vrtSelectedItem In .SelectedItems Set SummaryWB = Workbooks.Open(vrtSelectedItem) Next End With If SummaryWB Is Nothing then Exit Sub

如果我没记错的话，必须启用 Microsoft Scripting Runtime 参考.此外，您的网站可能使用反斜杠，我的网站使用正斜杠.

If I remember correctly, the Microsoft Scripting Runtime reference must be enabled. Also, your site may use backslashes, mine uses forward slashes.