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!

Answer 1

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