Get the content of a sharepoint folder with Excel VBA

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

Use the UNC path rather than HTTP. This code works:

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

To get the UNC path to use, go into the folder in the document library, drop down the Actions menu and choose Open in Windows Explorer. Copy the path you see there and use that.

In addition to:

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

also replace space:

myFilePath = replace(myFilePath, " ", "%20")

I messed around with this problem for a bit, and found a very simple, 2-line solution, simply replacing the 'http' and all the forward slashes like this:

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

It might not work for everybody, but it worked for me

If you are using a secure site (or wish to cater for both) you may wish to add the following line:

myFilePath = replace(myFilePath, "https:", "")

IMHO the coolest way is to go via WebDAV (without Network Folder, as this is often not permitted). This can be accomplished via ActiveX Data Objects as layed out in this excellent article excellent article (code can be used directly in Excel, used the concept recently).

Hope this helps!

http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic/

the original link is dead, but at least the textual content is still available on archive.org: http://web.archive.org/web/20091008034423/http://blog.itwarlocks.com/2009/04/28/accessing-webdav-in-microsoft-word-visual-basic

Get the content of a sharepoint folder with Excel VBA

Related

Recent Posts