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...)

That kind of path is returned only if you **open the file from online OneDrive and then you open it on local computer from there**. In such a scenario, the returned path is correct, too, but not useful to iterate between the folder items in the way you try. If you open it directly form your local folder, you will receive an ordinary path and be able to do what you requested. — FaneDuru, Jan 24 '21 at 15:22
Hi thanks for the reply. I´m not opening it online. Its being opened from a folder on onedive that is being syinced. I´ve added an image on the main post for clarity — user2292821, Jan 24 '21 at 15:42
I am afraid you do not... Otherwise, you should obtain the path of your local OneDrive folder. How do you do it? — FaneDuru, Jan 24 '21 at 15:45
Please see the image i´ve added on the main post :) that where i´m opening up the file and running the macro. — user2292821, Jan 24 '21 at 15:47
You can use [this solution](https://stackoverflow.com/a/73577057/12287457) to convert the return value of `ActiveWorkbook.Path` to a local path. By the way, I would highly recommend using `ThisWorkbook.Path` instead of `ActiveWorkbook.Path`. — GWD, Oct 23 '22 at 00:06

score 2 · Answer 1 · answered Jan 24 '21 at 17:26

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

VBasic2008 · Answer 2 · 2021-01-25T07:41:22.890

Loop Through OneDrive Folders

Adjust the values in the constants section.
When done testing, you can remove or out-comment the Debug.Print lines.

Features (Microsoft Docs)

The Code

Option Explicit

Sub getFoldersTest()

    ' Define constants.
    Const wsName As String = "Sheet1"
    Const FirstRow As Long = 8
    Const Col As String = "B"
    
    ' Define workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' Workbook containing this code.
    Debug.Print "Workbook Path: " & wb.Path
    
    ' Define FolderPath (OneDrive-specific).
    Dim Path1 As String: Path1 = Environ("OneDrive")
    Debug.Print "Path1:         " & Path1
    Dim SubStrings() As String: SubStrings = Split(wb.Path, "/", 5)
    Dim Path2 As String: Path2 = Replace(SubStrings(4), "/", "\")
    Debug.Print "Path2:         " & Path2
    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    Dim FolderPath As String: FolderPath = fso.BuildPath(Path1, Path2)
    Debug.Print "Folder Path:   " & FolderPath
    
    ' Validate FolderPath.
    If Not fso.FolderExists(FolderPath) Then
        MsgBox "The folder '" & FolderPath & "' does not exist.", vbCritical
        Exit Sub
    End If
    
    ' Calculate Last Row and Length.
    Dim LastRow As Long
    Dim Length As Long
    With wb.Worksheets(wsName) ' or 'wb.ActiveSheet' - not recommended.
        ' Guarda o indice da ultima linha com conteudo da coluna B.
        ' Mesmo havendo vazios identifca a ultima linha.
        LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
        Debug.Print "Last Row:      " & LastRow
        ' Dimensao da coluna C ate a ultima celula com conteudo começando na C7.
        Length = LastRow - FirstRow + 1
        Debug.Print "Length:        " & Length
    End With
    
    ' Declare additional variables.
    Dim fsoFolder As Object
    Dim i As Long
    
    ' Loop...
    For i = 0 To Length ' Loop na coluna B.
        For Each fsoFolder In fso.GetFolder(FolderPath).SubFolders
            ' e.g.
            Debug.Print i, fsoFolder.Name, fsoFolder.Path
        Next fsoFolder
    Next i

End Sub

VBA - Loop through folders on Onedrive

2 Answers2

Loop Through OneDrive Folders