从SharePoint网站打开Excel文件 [英] Open an Excel file from SharePoint site
问题描述
我正在尝试使用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 "http://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不喜欢?
推荐答案
从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 = "https://sharepoint.com/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.
这篇关于从SharePoint网站打开Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!