Display folder name in Excel - VBA Macro

[charlests]

Hi, I need a macro which displays the folder and subfolder name from a specific path in the column A of the excel sheet in the same heirarchy. Also whenever a sub folder name is displayed in column A, I need its main folder name to be displayed in the same row Column B i.e offset(0,1). I have a macro, but it displays the folder name in the 1st cell of column B, I need it to increment to the subsequent cells based on subfolders.

Sub Step1()
Dim startRange As Range

Sheet1.Cells.Clear
Set startRange = Sheet1.Range("A5")
'Parent Directory - Change this to whichever directory you want to use
ListFoldersAndInfo "C:\desktop\", startRange
End Sub


Sub ListFoldersAndInfo(foldername As String, Destination As Range)
Dim FSO As Object
Dim Folder As Object
Dim R As Long
Dim subfolder As Object
Dim Wks As Worksheet
Dim x As Long
Dim y As Long


Set FSO = CreateObject("Scripting.FileSystemObject")

Set Folder = FSO.GetFolder(foldername)
Destination = Folder.Name
'R = Activecell.Row
'c = Activecell.Column
Set Destination = Destination.Offset(1, 0)

For Each subfolder In Folder.SubFolders

x = Activecell.Row
y = Activecell.Column

Cells(x, y + 1) = Folder.Name
'Set Destination = Destination.Offset(0, 1)


ListFoldersAndInfo Folder.Path & "\" & subfolder.Name, Destination

Next subfolder


Set FSO = Nothing

End Sub

I am a beginner in vba...so an explanation along with codes will be really helpful for me. Thanks in advance

 

[BrianMH]

Can you post an example path and how you want it to be output. Use the tools mentioned in the link on my signature "Why use HTML Tables instead of file links?".

 

[charlests]

Can you post an example path and how you want it to be output. Use the tools mentioned in the link on my signature "Why use HTML Tables instead of file links?".

Thanks for your reply Brian. My path is C:\Desktop\Macro.
Here 'Macro' is the main folder. Inside the folder 'Macro' there are several subfolders like 'one', 'two', 'three', 'four'. Inside the folder 'one', there is a subfolder 'test' which has subfolders 'test1', 'test2', 'test3'. Inside the folder 'three', there is a subfolder 'new' which has subfolders 'new1', 'new2', 'new3'.

I need macro to print the folder name as follows,

Macro
one
test
test1 one
test2 one
test3 one
two
three
new
new1 three
new2 three
new3 three
four

Hope this is clear view of what I need.
</SPAN>

 

[BrianMH]

If you go to the link below you can find code for directory listing. I think the way it formats it is easier to understand than what you are asking for but you should be able to easily modify it to output in the way you describe.

Recursion And The FileSystemObject

BTW aren't test1, test2 and test3 a subfolder of test so instead of being "test1 one" it would be "test1 test"?