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.

Solution 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

Solution 2

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.

Solution 3

In addition to:

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

also replace space:

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

Solution 4

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

Solution 5

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

View more solutions

Comments

[Adriaan]

can be tricky to map network drive; I found how-to-map-sharepoint useful to resolve this.

[armstrhb]

As of 02/07/2013, this article link seems to be dead. Does anyone know if this article is still around else where?

[enderland]

See this for enabling the FileSystemObject.

[Shrout1]

This code looks very promising but Sub test() is throwing a "User-defined type not defined" compile error for the Dim dm as New DriveMapper line. Perhaps I am missing a DLL reference? I've added Microsoft Scripting Runtime and also Windows Script Host Object Model. Using Excel 2010.

[Shrout1]

The link referenced above appears to be dead as of 9/10/2013. Any chance there is an updated version?

[Adriaan]

The linked document wasn't mine. I've added the missing info in the article.

[MostlyHarmless]

@armstrhb: there is a copy on www.archive.org, but there seems to be only the text available, not the graphics.

[Chris Hayes]

sorry for responding so late @shrout1, maybe you made a module instead of a class?

[Trm]

Thanks for great workaround. By connecting directly I had to manually "visit" the sharepoint site before getting active connection, but mapping drive seems to solve that issue. One question: how can I avoid unmapping drive before my code, that calls DriveMapper class, finishes? I'm not very familiar with how they work yet.

[Chris Hayes]

@Trm someone edited my post to wrap the code in a class with a dispose method "Class_Terminate" which unmaps the drive. When the class goes out of scope then the drive get's unmapped. You could just remove the "Class_Terminate" method...

[Trm]

@ChrisHayes yea, I figured as much. I removed it and then was stuck with bunch of mapped drives every-time I opened connection. I did some workaround by saving mapped drive letter to memory and unmapping at the end of sub in module. Not sure if it's the most elegant solution.

[Ralph]

Actually, the above seems to be incomplete for me and should read for https: (in addition to the above code) myFilePath = Replace(myFilePath, Split(myFilePath, "\")(2), Split(myFilePath, "\")(2) & "@SSL") this will convert https://my.SharePoint.com/ eventually to \\my.SharePoint.com@SSL\ (after all the replaces).

[John]

Doesn't work with SharePoint online. You can only access via HTTP or WebDav

[Chadee Fouad]

Thanks, but this answer is incomplete. It does not list all of the files in the sharepoint folder?

[user2465349]

Of all the answers on this post, this one actually works for Modern SharePoint sites and is simple to setup. I don't have to worry about mapping a network drive or create another surrogate process to refresh an expired connection. I also don't have to enable some special VBA reference library either. Verified to work as of April 2022.

[Chadee Fouad]

@user2465349 Thanks! Glad that this helped. However, Microsoft recommends syncing files to your computer as a more stable and more reliable solution. Once that is done you can scan it just like any other directory on your harddisk. Check this out: support.microsoft.com/en-us/office/…