PDA

View Full Version : Get folder names in different columns excel



cccmmm123
06-06-2011, 11:16 PM
Hy, I'm new in VBA,
I need some example of code VBA for get list of subfolders with specified name to put in excel table. Name of my folder is like:
AUD_C1234_02_PRODUCTS

I want to get names like this (each folder names in new row) in to table that I have 4 columns, so separator for new columns is underscore _
Final result must be:
ColumnA ColumnB ColumnC ColumnD
AUD C1234 02 PRODUCTS
Does anybody know , or can help me please

Thanks A lot:beerchug:

p45cal
06-07-2011, 12:34 AM
Assuming you have the full folder names in column A, use the macro recorder then use Text To Columns and use "_" as the delimiter.
I got this: Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="_", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2)), _
TrailingMinusNumbers:=True
Now just tweak it by adjusting Selection, and the destination.

mancubus
06-07-2011, 12:43 AM
first part:
http://www.erlandsendata.no/english/index.php?d=envbafolderslistfoldersscripting

for subfolders.


second part:
texttocolumns or VBA split below.


Sub split_str()
Dim rng As Range, cll As Range
Dim vSplit As Variant
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row 'col A, last nonblank row
Set rng = Range("A1:A" & LR) 'range that houses subfolders list. change to suit
On Error Resume Next
For Each cll In rng
vSplit = Split(cll.Value, "_") 'delimiter is "_"
cll.Resize(1, UBound(vSplit) - LBound(vSplit) + 1).Value = Application.Transpose(Application.Transpose(vSplit))
Next
End Sub

p45cal
06-07-2011, 12:51 AM
mancubus, shouldn't
For Each cll In r
be
For Each cll In rng

mancubus
06-07-2011, 01:10 AM
mancubus, shouldn't
For Each cll In r
be
For Each cll In rng

thanks for the correction p45cal.

sorry for the typo.

cccmmm123
06-07-2011, 02:27 AM
Many thanks guys, i think I will solved my problem.
But I forgot to say that I'm using excel 2010.

Regards

p45cal
06-07-2011, 03:05 AM
But I forgot to say that I'm using excel 2010.Do any of the solution offered not work in xl2010?

cccmmm123
06-07-2011, 03:42 AM
Yes it is , I followed instruction with referenced On Microsoft scripting runtime and staf worked
Thanks