使用Excel VBA获取sharepoint文件夹的内容 [英] Get the content of a sharepoint folder with Excel VBA

问题描述

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

  Dim文件夹作为文件夹
 Dim f As File 
 Dim fs As New FileSystemObject 
 
设置文件夹= fs.GetFolder（// sharepoint.address / path / to / folder）
 
对于每个f在folder.Files 
'东西
下一个f 
  

编辑 shahkalpesh）：

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

解决方案

已发现与SharePoint上的文件一起工作，而服务器权限是将WebDAV文件夹映射到驱动器号。这是一个实现的例子。

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

• Windows Script Host对象模型（ wshom.ocx ） - 对于WshNetwork


• Microsoft脚本运行时（ scrrun.dll ） - 对于FileSystemObject

创建一个新的类模块，称为 DriveMapper 并添加以下代码：

  Option Explicit 
 
私有oMappedDrive作为Scripting.Drive 
私有oFSO作为新Scripting.FileSystemObject 
私有网络作为新的WshNetwork 
 
私有子类_Terminate（）
 UnmapDrive 
 End Sub 
 
 Public Function MapDrive（NetworkPath As String）As Scripting.Folder 
 Dim DriveLetter As String，i As Integer 
 
 UnmapDrive 
 
对于i = Asc（Z ）To Asc（A）步骤-1 
 DriveLetter = Chr（i）
如果不是oFSO.DriveExists（DriveLetter）然后
 oNetwork.MapNetworkDrive DriveLetter& ：，NetworkPath 
设置oMappedDrive = oFSO.GetDrive（DriveLetter）
设置MapDrive = oMappedDrive.RootFolder 
退出
结束如果
下一个i 
结束函数
 
私有子UnmapDrive（）
如果没有oMappedDrive是没有
如果oMappedDrive.IsReady然后
 oNetwork.RemoveNetworkDrive oMappedDrive.DriveLetter& ：
如果
设置oMappedDrive = Nothing 
如果
 End Sub 
  

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

  Sub test（）
 Dim dm As New DriveMapper 
 Dim sharepointFolder As Scripting.Folder 
 
设置sharepointFolder = dm.MapDrive（http：// your / sharepoint / path）
 
调试.Print sharepointFolder.Path 
 End Sub 
  

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 ?

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) :

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.

解决方案

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.

Add references to the following ActiveX libraries in VBA:

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

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

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

这篇关于使用Excel VBA获取sharepoint文件夹的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！