List All SharePoint Files In Folder

[sspatriots]

I have this code below that works to a degree. That is, I can see the correct "https" file paths in the Immediate window using the "Debug.Print" command, however, I need to get these paths to be what shows up on my worksheet. The file paths on the worksheet are the ones to my local users folder. I tried using the line that is near the bottom that is commented out, but that just gives me the "Run-time error '450': Wrong number of arguments or invalid property assignment" error

Sub ListAllFilesInFolder()
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim userName As String
    
    Dim spacePosition As Long
    spacePosition = InStr(Application.userName, " ")
    
    Length = Len(Application.userName)
    
    userName = Left(Application.userName, 1) & Right(Application.userName, (Length - spacePosition))
    
    Set ws = ActiveSheet
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Users\" & userName & "\DEM\DEM - Documents\Purchasing\PO Archive\2024") ' Replace with your desired folder path
    
    Dim oFile2 As String
    
    For Each oFile In oFolder.Files
        ws.Cells(i + 2, 1).Value = oFile.Path
        i = i + 1
        
        Dim spacePosition2 As Long
        spacePosition2 = InStr(oFile, "\2024\")
        
        Length2 = Len(oFile)
        
        oFile2 = "https://DEM.sharepoint.com/sites/DEM614/Shared Documents/Purchasing/PO Archive/2024/" & Right(oFile, (Length2 - spacePosition2 - 5))
        
        Debug.Print oFile2 'The file paths in the Immediate window are correct
        
    '    oFile = oFile2
        
    Next oFile
    
End Sub

 

[sspatriots]

Forgot to mention, I don't care for the part that has "\2024\" in the string search. I couldn't figure a way to do that with wildcards. The years are from 2019 to 2024. Thanks, SS

 

[sspatriots]

I've rearranged the original code a little and now it puts the correct file paths on the workbook. However, I cannot get them to become an active hyperlink unless I click in each cell and then select "Enter" on my my keyboard. I still have the issue of not knowing how to use a wildcard for that string search in my previous post as well.

Sub ListAllFilesInFolder()
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Long
    Dim ws As Worksheet
    Dim userName As String
    
    Dim spacePosition As Long
    Dim spacePosition2 As Long

    spacePosition = InStr(Application.userName, " ")
    
    Length = Len(Application.userName)
    
    userName = Left(Application.userName, 1) & Right(Application.userName, (Length - spacePosition))
    
    Set ws = ActiveSheet
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("C:\Users\" & userName & "\DEM\DEM - Documents\Purchasing\PO Archive\2024") ' Replace with your desired folder path
    
    Dim oFile2 As String
    
    For Each oFile In oFolder.Files
        
        spacePosition2 = InStr(oFile, "\2024\")
        
        Length2 = Len(oFile)
        
        oFile2 = "https://DEM.sharepoint.com/sites/DEM614/Shared Documents/Purchasing/PO Archive/2024/" & Right(oFile, (Length2 - spacePosition2 - 5))
        
        Debug.Print oFile2 'The file paths in the Immediate window are correct
        
        ws.Cells(i + 2, 1).Value = oFile2
        i = i + 1
        
    Next oFile
    
End Sub