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