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.

user_2935378 · Answer

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

user_6548223 · Answer

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	eams\NCICDS\Testing folder for Me\Elise\Deal Docs\2022\Metro Egypt\Confectionery\*.*")

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

user_6548223 · Answer

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

Get the content of a sharepoint folder with Excel VBA

11 Answers