使用Excel VBA获取sharepoint文件夹的内容
使用Excel VBA获取sharepoint文件夹的内容
提问于 2009-08-28 03:39:06
我通常使用这段代码来检索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编辑(在shahkalpesh的好评论之后)：
如果我在Windows资源管理器中输入地址，则可以访问sharepoint。对sharepoint的访问需要身份验证，但它是透明的，因为它依赖于Windows登录。
回答 10
Stack Overflow用户
发布于 2009-10-30 06:59:16
我发现，在拥有服务器权限的情况下处理SharePoint上的文件的唯一方法是将WebDAV文件夹映射到一个驱动器号。这里有一个实现的例子。
在VBA中添加对以下ActiveX库的引用：
用于WshNetwork
- Microsoft脚本运行时(
wshom.ocx)的 - Windows脚本主机对象模型(
wshom.ocx)-用于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 SubStack Overflow用户
发布于 2009-08-28 04:01:02
使用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若要获取要使用的UNC路径，请转到文档库中的文件夹，下拉“操作”菜单，然后选择“在Windows资源管理器中打开”。复制您在那里看到的路径并使用它。
Stack Overflow用户
发布于 2013-09-12 05:40:42
除了：
myFilePath = replace(myFilePath, "/", "\")
myFilePath = replace(myFilePath, "http:", "")还要替换空格：
myFilePath = replace(myFilePath, " ", "%20")页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/1344910
复制相关文章
相似问题
腾讯云开发者
