Get the content of a sharepoint folder with Excel VBA

Question

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.

Answer 1

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