A1=name of folder >> B1= Subfolders,seperated by comma(Tricky Problem?)

[Tobi Harnegg]

I have the following folder structure:

/Folder01/Subfolder01
/Folder01/Subfolder02
/Folder02/Subfolder01
/Folder02/Subfolder02

list_folders.xls should be in the ROOT

I want the makro to read the foldernames in Column A, and as a result
i would like to see the Subfolder Names in Column B (Seperated by
comma)
Btw. Subfolders deeper than that can be ignored - so its basically
about the subfolders on "level 2".

So with the above folder structure, having filled in A1&A2 before, it
should look like this:

A1="Folder01" >> B1="Subfolder01,Subfolder02"
A2="Folder02" >> B2="Subfolder02,Subfolder02"

Can anyone solve this?
It seems quite tricky...

 

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = LastRow To 1 Step -1

.Cells(i, "B").Value = Split(.Cells(i, "A").Value, "/")(2)
.Cells(i, "A").Value = Split(.Cells(i, "A").Value, "/")(1)
If .Cells(i, "A").Value = .Cells(i + 1, "A").Value Then

.Cells(i, "B").Value = .Cells(i, "B").Value & "," & .Cells(i
+ 1, "B").Value
.Rows(i + 1).Delete
End If
Next i
End With

End Sub

 

[Tobi Harnegg]

Thanx Bob,

I get "Error 9" though.... "Index out of valid Area..."

Since i am quite rookie, of course i dont exactly understand what your
macro is doing,
and also not if i was clear enough in my description...
thats why i uploaded this xls

http://sites.google.com/site/excelauswahl/excel-vba/list-folder.zip?attredirects=0
It already includes your makro and also shows what results i'd like to
get.
(ah, and its zipped, since it also contains the folder structure to
test it with)

Thanks a lot,
Tobias

PS: I saved it in xls, but i am actually using Excel 2007 - maybe the
error message is related to that?
PS2: This link shows a screenshot of the results id like to have
http://sites.google.com/site/excelauswahl/excel-vba

 

[Bob Phillips]

My code is based upon the folder names being in column A as you described.
Your example shows neither folder names, nor in column A.

 

[Tobi Harnegg]

My code is based upon the folder names being in column A as you described..
Your example shows neither folder names, nor in column A.

--
__________________________________
HTH

Bob

Yess it does!

The Folder Names in Root - where the workbook lies - are "01", "02",
"03", and "xyz"

Maybe you got mislead because i put the "Desired result" in Collum F &
G
but that had no meaning, i just wanted to put it next to each other
for the screenshot.

I entered The names into Collumn A
A1="01"
A2="02"
A3="03"

Copied your makro into modul 1, and when i execute i get No result in
Collumn B
except for Error 9

 

[Tobi Harnegg]

Yess it does!

The Folder Names in Root - where the workbook lies - are "01", "02",
"03", and "xyz"

Maybe you got mislead because i put the "Desired result" in Collum F &
G
but that had no meaning, i just wanted to put it next to each other
for the screenshot.

I entered The names into Collumn A
A1="01"
A2="02"
A3="03"

Copied your makro into modul 1, and when i execute i get No result in
Collumn B
except for Error 9

that screenshot is probably more clear:
http://5333903419338715748-a-180274...swahl/excel-vba/screenshot.jpg?attredirects=0