Excel macro erases cell VBA

Question

I am trying to make an excel macro that lists each folder name and all of its subfolders on the next column, similar to the command prompt tree command. What I'm expecting to see is something like this:

Note, folders 2 4 and 5 have no folders in them, only files and I don't want this to display files. I've turned screen updating back on so I can see what it does and it does list the folders with no subfolders in the correct place, but then immediately overwrites them so I'm left with this:

Here's the code

Sub Example2()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objSubFolder As Object
    Dim i As Integer, j As Integer

    'Create an instance of the FileSystemObject
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    'Get the folder object
    Set objFolder = objFSO.GetFolder("Y:filepath")

    i = 1
    j = 1

    'loops through each file in the directory and prints their names and path
    For Each objSubFolder In objFolder.subfolders
        'print folder name
        Cells(i + 1, 1) = objSubFolder.Name
        'print folder path
        'Cells(i + 1, 2) = objSubFolder.Path

        For Each objsubsubfolder In objSubFolder.subfolders
            Cells(i + 1, j + 1) = objsubsubfolder.Name
            i = i + 1
        Next objsubsubfolder
    Next objSubFolder
End Sub

Your loop doesn't increment i if there are no sub-folders. — SJR

Pᴇʜ Pᴇʜ · Accepted Answer · 2017-07-20T14:44:59

As @SJR pointed out: Your loop doesn't increment i if there are no sub-folders.

You need an additional i = i + 1 right before that line:

For Each objsubsubfolder In objSubFolder.subfolders

Side note:

Always use Long instead of Integer especially when handling with row counts.
Excel has more rows than Integer can handle.
```
Dim i As Long, j As Long
```
You should specify a worksheet for all your Cells like
```
Worksheets("SheetName").Cells
```
to avoid any issues. Never assume the worksheet.
Instead of all .Cells(i + 1, …) you can use .Cells(i, …), if you set the start of i = 2 instead of i = 1 which makes it more human readable.

Excel macro erases cell VBA

1 Answers