使用 Excel VBA 获取共享点文件夹的内容

Get the content of a sharepoint folder with Excel VBA

提问by afewcc

Usually I use this piece of code to retrieve the content of a folder in VBA. But this doesn't work in the case of a sharepoint. How can I do ?

通常我使用这段代码在VBA中检索文件夹的内容。但这在共享点的情况下不起作用。我能怎么做 ？

Dim folder As folder
Dim f As File
Dim fs As New FileSystemObject

Set folder = fs.GetFolder("//sharepoint.address/path/to/folder")

For Each f In folder.Files
    'Do something
Next f

EDIT(after a good comment by shahkalpesh) :

编辑（经过 shahkalpesh 的好评）：

I can access to the sharepoint if I enter the address in Windows Explorer. Access to the sharepoint needs an authentification, but it's transparent, because it relies on the Windows login.

如果我在 Windows 资源管理器中输入地址，我就可以访问共享点。访问共享点需要身份验证，但它是透明的，因为它依赖于 Windows 登录。

回答by Chris Latta

Use the UNC path rather than HTTP. This code works:

使用 UNC 路径而不是 HTTP。此代码有效：

Public Sub ListFiles()
    Dim folder As folder
    Dim f As File
    Dim fs As New FileSystemObject
    Dim RowCtr As Integer

    RowCtr = 1
    Set folder = fs.GetFolder("\SharePointServer\Path\MorePath\DocumentLibrary\Folder")
    For Each f In folder.Files
       Cells(RowCtr, 1).Value = f.Name
       RowCtr = RowCtr + 1
    Next f
End Sub

To get the UNC path to use, go into the folder in the document library, drop down the Actions menu and choose Open in Windows Explorer. Copy the path you see there and use that.

要使用 UNC 路径，请进入文档库中的文件夹，下拉操作菜单并选择在 Windows 资源管理器中打开。复制您在那里看到的路径并使用它。

回答by Chris Hayes

The only way I've found to work with files on SharePoint while having to server rights is to map the WebDAV folder to a drive letter. Here's an example for the implementation.

我发现在拥有服务器权限的同时处理 SharePoint 上的文件的唯一方法是将 WebDAV 文件夹映射到驱动器号。这是实现的示例。

Add references to the following ActiveX libraries in VBA:

在 VBA 中添加对以下 ActiveX 库的引用：

• Windows Script Host Object Model (wshom.ocx) - for WshNetwork
• Microsoft Scripting Runtime (scrrun.dll) - for FileSystemObject

• Windows 脚本宿主对象模型 ( wshom.ocx) - 用于 WshNetwork
• Microsoft 脚本运行时 ( scrrun.dll) - 用于 FileSystemObject

Create a new class module, call it DriveMapperand add the following code:

创建一个新的类模块，调用它DriveMapper并添加以下代码：

Option Explicit

Private oMappedDrive As Scripting.Drive
Private oFSO As New Scripting.FileSystemObject
Private oNetwork As New WshNetwork

Private Sub Class_Terminate()
  UnmapDrive
End Sub

Public Function MapDrive(NetworkPath As String) As Scripting.Folder
  Dim DriveLetter As String, i As Integer

  UnmapDrive

  For i = Asc("Z") To Asc("A") Step -1
    DriveLetter = Chr(i)
    If Not oFSO.DriveExists(DriveLetter) Then
      oNetwork.MapNetworkDrive DriveLetter & ":", NetworkPath
      Set oMappedDrive = oFSO.GetDrive(DriveLetter)
      Set MapDrive = oMappedDrive.RootFolder
      Exit For
    End If
  Next i
End Function

Private Sub UnmapDrive()
  If Not oMappedDrive Is Nothing Then
    If oMappedDrive.IsReady Then
      oNetwork.RemoveNetworkDrive oMappedDrive.DriveLetter & ":"
    End If
    Set oMappedDrive = Nothing
  End If
End Sub

Then you can implement it in your code:

然后你可以在你的代码中实现它：

Sub test()
  Dim dm As New DriveMapper
  Dim sharepointFolder As Scripting.Folder

  Set sharepointFolder = dm.MapDrive("http://your/sharepoint/path")

  Debug.Print sharepointFolder.Path
End Sub

回答by Eileen

In addition to:

此外：

myFilePath = replace(myFilePath, "/", "\")
myFilePath = replace(myFilePath, "http:", "")

also replace space:

也替换空间：

myFilePath = replace(myFilePath, " ", "%20")

回答by BigD

IMHO the coolest way is to go via WebDAV (without Network Folder, as this is often not permitted). This can be accomplished via ActiveX Data Objects as layed out in this excellent articleexcellent article (code can be used directly in Excel, used the concept recently).

恕我直言，最酷的方法是通过 WebDAV（没有网络文件夹，因为这通常是不允许的）。这可以通过 ActiveX 数据对象来完成，如这篇优秀文章优秀文章中所述（代码可以直接在 Excel 中使用，最近使用了这个概念）。

Hope this helps!

希望这可以帮助！

http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/

http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/

the original link is dead, but at least the textual content is still available on archive.org: http://web.archive.org/web/20091008034423/http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic

原始链接已失效，但至少在archive.org 上仍然可以找到文本内容：http://web.archive.org/web/20091008034423/http: //blog.itwarlocks.com/2009/04/28/ accessing-webdav-in-microsoft-word-visual-basic

回答by James Garratt

I messed around with this problem for a bit, and found a very simple, 2-line solution, simply replacing the 'http' and all the forward slashes like this:

我暂时解决了这个问题，并找到了一个非常简单的 2 行解决方案，只需像这样替换“http”和所有正斜杠：

myFilePath = replace(myFilePath, "/", "\")
myFilePath = replace(myFilePath, "http:", "")

It might not work for everybody, but it worked for me

它可能不适合所有人，但对我有用

If you are using a secure site (or wish to cater for both) you may wish to add the following line:

如果您使用的是安全站点（或希望同时满足两者），您可能希望添加以下行：

myFilePath = replace(myFilePath, "https:", "")

回答by Adriaan

Drive mapping to sharepoint (also https)

驱动器映射到共享点（也 https）

Getting sharepoint contents worked for me via the mapped drive iterating it as a filesystem object; trick is how to set up the mapping: Then copy path (line with http*) (see below)

通过映射驱动器将共享点内容作为文件系统对象进行迭代，获取对我有用的共享点内容；技巧是如何设置映射： 然后复制路径（带有 http* 的行）（见下文）

Use this path in Map drive from explorer or command (i.e. net use N: https:://thepathyoujustcopied) Note: https works ok with windows7/8, not with XP.

从资源管理器或命令（即net use N: https:://thepathyoujustcopied）在地图驱动器中使用此路径注意：https 在 windows7/8 上可以正常工作，在 XP 上不可用。

That may work for you, but I prefer a different approach as drive letters are different on each pc. The trick here is to start from sharepoint (and not from a VBA script accessing sharepoint as a web server).

这可能对你有用，但我更喜欢不同的方法，因为每台电脑上的驱动器号都不同。这里的技巧是从 sharepoint 开始（而不是从将 sharepoint 作为 Web 服务器访问的 VBA 脚本）。

Set up a data connection to excel sheet

设置 Excel 表格的数据连接

• in sharepoint, browse to the view you want to monitor
• export view to excel (in 2010: library tools; libarry | export to Excel)
• when viewing this excel, you'll find a datasource set up (tab: data, connections, properties, definition)

• 在 sharepoint 中，浏览到要监视的视图
• 将视图导出到 excel（2010 年：库工具；库 | 导出到 Excel）
• 查看此 excel 时，您会发现一个数据源设置（选项卡：数据、连接、属性、定义）

You can either include this query in vba, or maintain the database link in your speadsheet, iterating over the table by VBA. Please note: the image above does not show the actual database connection (command text), which would tell you how to access mysharepoint.

您可以在 vba 中包含此查询，也可以在电子表格中维护数据库链接，通过 VBA 遍历表。请注意：上图并未显示实际的数据库连接（命令文本），它会告诉您如何访问我的共享点。

回答by Steven C. Britton

I spent some time on this very problem - I was trying to verify a file existed before opening it.

我在这个问题上花了一些时间 - 我试图在打开文件之前验证文件是否存在。

Eventually, I came up with a solution using XML and SOAP - use the EnumerateFolder method and pull in an XML response with the folder's contents.

最终，我想出了一个使用 XML 和 SOAP 的解决方案 - 使用 EnumerateFolder 方法并使用文件夹的内容提取 XML 响应。

I blogged about it here.

我在这里写了关于它的博客。

回答by Steven C. Britton

Try mapping the sharepoint library to a drive letter in windows. Then select the drive and path in your code.

尝试将共享点库映射到 Windows 中的驱动器号。然后在代码中选择驱动器和路径。