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

[nathansav]

Set Destination = Destination.Offset(1, 0) is where you are going wrong (I think) you are using the folder name as a range, so you'll need to get the folder name, find it on the page, and then offset from where its found.

[charlests]

Set Destination = Destination.Offset(1, 0) is where you are going wrong (I think) you are using the folder name as a range, so you'll need to get the folder name, find it on the page, and then offset from where its found.

Thanks for your reply Nathan, since Im new to vba, it will be helpful for me if you post the codes.

[nathansav]

Use the match function to find it.