使用ExcelVBA获取SharePoint文件夹的内容

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (41)

通常,我使用这段代码来检索VBA中文件夹的内容。但在SharePoint的情况下,这是行不通的。我该怎么办?

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
提问于
用户回答回答于

除了:

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

还可替换空间:

myFilePath = replace(myFilePath, " ", "%20")
用户回答回答于

我发现处理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

扫码关注云+社区