Getting a list of Excel files in a folder on OneDrive using VBA

Question

In Excel I recorded a macro to open a file on OneDrive for Business and the code it generated was something below which works fine:

Workbooks.Open Filename:= "https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/SDM%20Rebates%20v30.xlsm"

The problem is that for this to work the program must know exactly the file name. What I was hoping is that the VBA would scan that specific folder and open each file so I just removed the file name and used the same URL and used the following code:

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder("https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/")
For Each oFile In oFolder.Files
    Debug.print(oFile.Name)
Next

This gives me path not found error. Please note that I do not want to use a local C: path since the idea is that the users will put files in the shared folder and will run the macro on their end (i.e. my local path might be different than their local path).

Thanks!

See: https://stackoverflow.com/questions/1344910/get-the-content-of-a-sharepoint-folder-with-excel-vba — Tim Williams, Jun 10 '21 at 00:50

score 1 · Answer 1 · answered Jun 10 '21 at 00:59

Referencing SharePointURLtoUNC from the link above, you could try this:

Sub TT()
    Dim f As String, oFSO, oFolder, oFile
   
    f = "https://mycopmanymy.sharepoint.com/personal/john/Documents/John/Shared/Support/"
    Debug.Print "URL", f
    f = SharePointURLtoUNC(f)
    Debug.Print "UNC", f
   
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(f)
    
    For Each oFile In oFolder.Files
        Debug.Print oFile.Name
    Next
End Sub

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

Thanks but unfortunately I'm still getting the same error. I can see the contents on the browser but when I copy the URL to the code I am still getting Path Not Found error. — Chadee Fouad, Jun 11 '21 at 16:47
I think that may work for SharePoint but maybe not for OneDrive... — Tim Williams, Jun 11 '21 at 17:25

score 1 · Accepted Answer · answered Jul 17 '21 at 17:05

I found the solution in this link:
https://officeaccelerators.wordpress.com/2015/01/29/vba-code-to-download-list-of-files-and-folders-from-sharepoint/

It might need a little bit of tweaking but it lists all the files in the specified sharepoint folder

Note You must change this line of code to fit the url of your company:

 `SharepointAddress = "https://abc.onmicrosoft.com/TargetFolder/"`


Sub DownloadListFromSharepoint()
    Dim SharepointAddress As String
    Dim LocalAddress As String
    Dim objFolder As Object
    Dim objNet As Object
    Dim objFile As Object
    Dim FS As Object
    Dim rng As Range
    SharepointAddress = "https://abc.onmicrosoft.com/TargetFolder/"

    Set objNet = CreateObject("WScript.Network")
    Set FS = CreateObject("Scripting.FileSystemObject")
    objNet.MapNetworkDrive "A:", SharepointAddress
    
    Set objFolder = FS.getfolder("A:")
    
    Set rng = ThisWorkbook.Worksheets(1).Range("a1")
    rng.Value = "File Name"
    rng.Offset(0, 1).Value = "Folder/File"
    rng.Offset(0, 2).Value = "Path"
    GetAllFilesFolders rng, objFolder, "" & strSharepointAddress
    objNet.RemoveNetworkDrive "A:"
    Set objNet = Nothing
    Set FS = Nothing

End Sub

Public Sub GetAllFilesFolders(rng As Range, ObjSubFolder As Object, strSharepointAddress As String)
    Dim objFolder As Object
    Dim objFile As Object
    
    For Each objFile In ObjSubFolder.Files
        rng.Offset(1, 0) = objFile.Name
        rng.Offset(1, 1) = "File"
        rng.Offset(1, 2) = Replace(objFile.Path, "A:\", SharepointAddress)
        Set rng = rng.Offset(1, 0)
    Next
    For Each objFolder In ObjSubFolder.subfolders
        rng.Offset(1, 0) = objFolder.Name
        rng.Offset(1, 1) = "Folder"
        rng.Offset(1, 2) = Replace(objFolder.Path, "A:\", SharepointAddress)
        Set rng = rng.Offset(1, 0)
        GetAllFilesFolders rng, objFolder, strSharepointAddress
    Next
End Sub

score 0 · Answer 3 · answered Dec 17 '22 at 04:57

The easiest way is to Sync your SharePoint folder to your PC (In SharePoint on the web there should be an icon that says Sync). Once that is done you should see a new folder on your Windows Explorer (File Manager). Once that happens, you can scan those files just like you scan any other folder on your pc as those files are being viewed as if they are on your harddisk. This solution avoids all complications related to URL, permissions, drivers, etc.

Getting a list of Excel files in a folder on OneDrive using VBA

3 Answers3