vba 从 SharePoint 站点打开 Excel 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19505513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Open an Excel file from SharePoint site
提问by Pocahontas
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.
我正在尝试使用 VBA 从 SharePoint 打开 Excel 文件。因为每次运行宏时我要查找的文件可能都不同，所以我希望能够查看 SharePoint 文件夹并选择我需要的文件。
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".
当我想在网络驱动器上查找文件时，下面的代码工作正常，但是当我用 SharePoint 地址替换它时，我收到“运行时错误 76：找不到路径”。
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
When I paste this address into Windows Explorer I have no problems accessing the SharePoint folder, so I know the path is correct.
当我将此地址粘贴到 Windows 资源管理器时，访问 SharePoint 文件夹没有问题，因此我知道路径是正确的。
Why doesn't VBA like it?
为什么 VBA 不喜欢它？
采纳答案by ARich
Try this code to pick a file from a SharePoint site:
尝试使用以下代码从 SharePoint 站点选择文件：
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
If I remember correctly, the Microsoft Scripting Runtimereference must be enabled. Also, your site may use backslashes, mine uses forward slashes.
如果我没记错的话，Microsoft Scripting Runtime必须启用引用。此外，您的网站可能使用反斜杠，我的网站使用正斜杠。
回答by Shrout1
I transform the URL into a WebDAV address using the following function I created. This function also returns regular system paths and UNC paths unscathed.
我使用我创建的以下函数将 URL 转换为 WebDAV 地址。此函数还返回正常系统路径和 UNC 路径完好无损。
Call this function by adding it into a module in your VBA project and entering MyNewPathString = Parse_Resource(myFileDialogStringVariable)just after your file dialog command and before using the path selected by the file dialog. Then reference "MyNewPathString" when using the target file location.
通过将其添加到 VBA 项目的模块中并MyNewPathString = Parse_Resource(myFileDialogStringVariable)在文件对话框命令之后和使用文件对话框选择的路径之前输入来调用此函数。然后在使用目标文件位置时引用“MyNewPathString”。
Public Function Parse_Resource(URL As String)
'Uncomment the below line to test locally without calling the function & remove argument above
'Dim URL As String
Dim SplitURL() As String
Dim i As Integer
Dim WebDAVURI As String
'Check for a double forward slash in the resource path. This will indicate a URL
If Not InStr(1, URL, "//", vbBinaryCompare) = 0 Then
'Split the URL into an array so it can be analyzed & reused
SplitURL = Split(URL, "/", , vbBinaryCompare)
'URL has been found so prep the WebDAVURI string
WebDAVURI = "\"
'Check if the URL is secure
If SplitURL(0) = "https:" Then
'The code iterates through the array excluding unneeded components of the URL
For i = 0 To UBound(SplitURL)
If Not SplitURL(i) = "" Then
Select Case i
Case 0
'Do nothing because we do not need the HTTPS element
Case 1
'Do nothing because this array slot is empty
Case 2
'This should be the root URL of the site. Add @ssl to the WebDAVURI
WebDAVURI = WebDAVURI & SplitURL(i) & "@ssl"
Case Else
'Append URI components and build string
WebDAVURI = WebDAVURI & "\" & SplitURL(i)
End Select
End If
Next i
Else
'URL is not secure
For i = 0 To UBound(SplitURL)
'The code iterates through the array excluding unneeded components of the URL
If Not SplitURL(i) = "" Then
Select Case i
Case 0
'Do nothing because we do not need the HTTPS element
Case 1
'Do nothing because this array slot is empty
Case 2
'This should be the root URL of the site. Does not require an additional slash
WebDAVURI = WebDAVURI & SplitURL(i)
Case Else
'Append URI components and build string
WebDAVURI = WebDAVURI & "\" & SplitURL(i)
End Select
End If
Next i
End If
'Set the Parse_Resource value to WebDAVURI
Parse_Resource = WebDAVURI
Else
'There was no double forward slash so return system path as is
Parse_Resource = URL
End If
End Function
This function will check if your file path is a URL and if it is secure (HTTPS) or not secure (HTTP). If it is a URL then it will build the appropriate WebDAV string so that you can link directly to the target file in SharePoint.
此函数将检查您的文件路径是否为 URL，以及它是否安全 (HTTPS) 或不安全 (HTTP)。如果它是一个 URL，那么它将构建适当的 WebDAV 字符串，以便您可以直接链接到 SharePoint 中的目标文件。
The user will likely be prompted for credentials each time the file is opened especially if they are not sitting on the same domain as your SharePoint farm.
每次打开文件时，可能会提示用户输入凭据，尤其是当他们与您的 SharePoint 场不在同一个域中时。
PLEASE NOTE:I have not tested this with an http site, however I am confident that it will work.
请注意：我没有用 http 站点测试过这个，但是我相信它会起作用。
回答by Jacek
From you script do not use http://sharepoint/my/fileas path but rather
\\sharepoint\my\fileand then is should work. It works fo my programs done in C#.
从你的脚本不要http://sharepoint/my/file用作路径，而是
\\sharepoint\my\file然后应该工作。它适用于我在 C# 中完成的程序。
回答by Ed van Harmelen
Please note there is a typo in your initial code
请注意您的初始代码中有一个错字
MyNewPathString = ParseResource(myFileDialogStringVariable)
should be replaced with
应该替换为
MyNewPathString = Parse_Resource(myFileDialogStringVariable)
The underscore was missing.
下划线不见了。
回答by FreeSoftwareServers
While this might not work exactly for OP's need to open file dialogue box, this is how I hard-coded opening a workbook stored via SharePoint/Teams which matches the title and possibly what many people end up here looking for.
虽然这可能无法完全满足 OP 打开文件对话框的需要，但这就是我硬编码打开通过 SharePoint/Teams 存储的工作簿的方式，该工作簿与标题匹配，并且可能是许多人最终在这里寻找的内容。
Get the URL by hitting "Copy Link" and stripping the needed part after "ObjectURL" and before "baseURL".
通过点击“复制链接”并在“ObjectURL”之后和“baseURL”之前剥离所需的部分来获取 URL。
Sub Test()
Dim URL As String
'Get URL By Coping Link and getting between "ObjectUrl" and "&baseUrl"
'Eg: objectUrl=https%3A%2F%2Fdomain.sharepoint.com%2Fsites%2FName_Teams%2FShared%20Documents%2FGeneral%2FDocuName.xlsx&baseUrl
URL = "https%3A%2F%2Fdomain.sharepoint.com%2Fsites%2FName_Teams%2FShared%20Documents%2FGeneral%2FDocuName.xlsx"
URLDecoded = URLDecode(URL)
'Debug.Print URLDecoded
Set WB = Workbooks.Open(URLDecoded)
End Sub
Public Function URLDecode(StringToDecode As String) As String
Dim TempAns As String
Dim CurChr As Integer
CurChr = 1
Do Until CurChr - 1 = Len(StringToDecode)
Select Case Mid(StringToDecode, CurChr, 1)
Case "+"
TempAns = TempAns & " "
Case "%"
TempAns = TempAns & Chr(Val("&h" & _
Mid(StringToDecode, CurChr + 1, 2)))
CurChr = CurChr + 2
Case Else
TempAns = TempAns & Mid(StringToDecode, CurChr, 1)
End Select
CurChr = CurChr + 1
Loop
URLDecode = TempAns
End Function
回答by psychicebola
You can use my approach to map SharePoint folder as network drives. Then you can proceed as you did so far.
您可以使用我的方法将 SharePoint 文件夹映射为网络驱动器。然后你就可以像之前一样继续了。
Excel VBA Up-/Download from multiple SharePoint folders
Excel VBA 从多个 SharePoint 文件夹上传/下载
Then you can also browse through the files with Diror File System Object
然后您还可以使用Dir或 文件系统对象浏览文件
回答by Steve
Try something like this:
尝试这样的事情：
Shell ("C:\Program Files\Internet Explorer\iexplore.exe http://sharepoint/my/file/path")
It worked for me.
它对我有用。
