excel - VBA - Loop through folders on Onedrive

Question

I′ve the code below that loops through the folders on the path where the excel file is saved and applies a set of parameters. The code works great on a local folder on my drive. However, on a local folder saved on Onedrive it does not work and provides the error 76 "Path not found".

I believe the problem sits with the Application.ActiveWorkbook.Path that delivers a link and not a path.

Does any one have any suggestion on how to solve this problem? Thanks.

Se image below to where i′m trying to open the file

Sub getfolders()

    Dim objFSO As New FileSystemObject
    Dim objFolder As Object
    Dim objSubFolder As Object
        
    Dim i As Integer
    Dim FldName As String
      
    Set objFolder = objFSO.GetFolder(Application.ActiveWorkbook.Path)
    
    Lastrow = Cells(Rows.Count, "B").End(xlUp).Row ' guarda o indice da ultima linha com conteudo da coluna B. Mesmo havendo vazios identifca a ultima linha
    Length = Range(Range("B8"), Range("B" & Lastrow)).Rows.Count ' dimens?o da coluna C ate a ultima celula com conteudo come?ando na C7


For i = 0 To Length ' loop na coluna B

    For Each objSubFolder In objFolder.SubFolders

(rest of the code...)

question from:https://stackoverflow.com/questions/65871956/vba-loop-through-folders-on-onedrive

Answer 1

The following code obtains the names of the subfolders within the user’s OneDrive directory. Modify it to met your needs.

Sub ShowOneDriveFolderList()
    Dim fs As Object, f As Object, f1 As Variant, s As String, sf As Variant
    Dim sep As String: sep = Application.PathSeparator
    Dim userHome As String: userHome = Environ("UserProfile") & sep
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(userHome & "OneDrive")
    Set sf = f.subFolders
    For Each f1 In sf
        s = s & f1.Name
        s = s & vbCrLf
    Next
    MsgBox s
End Sub