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

Answer 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.

Answer 3

In addition to:

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

also replace space:

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

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

Answer 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

Answer 6

I spent some time on this very problem - I was trying to verify a file existed before opening it.

Eventually, I came up with a solution using XML and SOAP - use the EnumerateFolder method and pull in an XML response with the folder's contents.

I blogged about it here.

Answer 7

Drive mapping to sharepoint (also https)

Getting sharepoint contents worked for me via the mapped drive iterating it as a filesystem object; trick is how to set up the mapping: Then copy path (line with http*) (see below)

Use this path in Map drive from explorer or command (i.e. net use N: https:://thepathyoujustcopied) Note: https works ok with windows7/8, not with XP.

That may work for you, but I prefer a different approach as drive letters are different on each pc. The trick here is to start from sharepoint (and not from a VBA script accessing sharepoint as a web server).

Set up a data connection to excel sheet

• in sharepoint, browse to the view you want to monitor
• export view to excel (in 2010: library tools; libarry | export to Excel)
• when viewing this excel, you'll find a datasource set up (tab: data, connections, properties, definition)

You can either include this query in vba, or maintain the database link in your speadsheet, iterating over the table by VBA. Please note: the image above does not show the actual database connection (command text), which would tell you how to access my sharepoint.

Answer 8

Mapping the WebDAV folder is my preferred method of creating an easily accessible, long-term connection to SharePoint. However, you'll find—even when properly mapped—that a file will return a URL when selected (especially via Application.FileDialog) due to changes in Windows 10 1803.

To circumvent this, you can map the drive using DriveMapper (or an equivalent) and then combine the resulting Application.FileDialog.SelectedItems with a URL to UNC converter function:

Public Function SharePointURLtoUNC( _
  sURL As String) _
As String
  Dim bIsSSL As Boolean

  bIsSSL = InStr(1, sURL, "https:") > 0
  sURL = Replace(Replace(sURL, "/", "\"), "%20", " ")
  sURL = Replace(Replace(sURL, "https:", vbNullString), "http:", vbNullString)
  
  sURL= Replace(sURL, Split(sURL, "\")(2), Split(sURL, "\")(2) & "@SSL\DavWWWRoot")
  If Not bIsSSL Then sURL = Replace(sURL, "@SSL\", vbNullString) 
  SharePointURLtoUNC = sURL
End Function

Answer 9

Here's a code that works for me:

Note...to get the URL part that has @SSL what you need to do is to copy the url of the sharepoint folder from Microsoft Edge/Chrome into Windows file explorer...then right click the current folder -> Properties and that should show you the path that has @SSL that you should use. That's the most difficult part!

Sub GetAllFileNamesInSharePointFolder()

FileName = Dir("\\mycompany.sharepoint.com@SSL\DavWWWRoot\teams\NCICDS\Testing folder for Me\Elise\Deal Docs\2022\Metro Egypt\Confectionery\*.*")

Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir()
Loop
End Sub

Answer 10

A better and more stable solution is to sync sharepoint to your hardsisk. Once that is done you can use DIR function to scan files just like other files on your hardisk. Check out this video from Microsoft regarding how to do this: https://support.microsoft.com/en-us/office/sync-sharepoint-files-and-folders-87a96948-4dd7-43e4-aca1-53f3e18bea9b

Answer 11

Try mapping the sharepoint library to a drive letter in windows. Then select the drive and path in your code.