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

通常，我使用这段代码来检索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

编辑(在shahkalpesh的好评后):

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

最佳答案

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

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

Windows脚本宿主对象模型(wshom.ocx)-用于WshNetwork

Microsoft脚本运行时(scrrun.dll)-用于FileSystemObject

创建一个新的类模块，将其称为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

然后，您可以在代码中实现它:

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