Traversing folders and subfolders

M

Michael

Assuming I have some xls files in C:\Folder the code below will list them
one by one in Sheet1 starting from cell A1.

Set ListPointer = Sheets("Sheet1").Range("A1")
FileName = Dir(Range("C:\Folder") & "\*.*")
Do While FileName <> ""
If Right(FileName, 4) = "." & Range("xls") Then
ListPointer = FileName
Set ListPointer = ListPointer.Offset(1, 0)
End If
FileName = Dir()
Loop

What if I have sub folders e.g. C:\Folder\SubFolder1 and
C:\Folder\SubFolder2 with lots of .xls files below them. How can I write a
simple loop to traverse all of the files in all folders and subfolders below
c:\Folder and list them in one range?

Thanks in advance
Michael
 
M

Michael

I don't thik that I've explained this very good. Allow me to clarify:

I want to write a recursive loop that traverses c:\Music and writes the
data back to Excel in the following manner:

Specifically under C:\Music I have the following tree structure

c:\music\artistname1
c:\music\artistname1\CDName1\a.mp3
c:\music\artistname1\CDName1\b.mp3
c:\music\artistname1\CDName1\c.mp3
c:\music\artistname1\CDName1\d.mp3
c:\music\artistname1\CDName2\a.mp3
c:\music\artistname1\CDName2\b.mp3
c:\music\artistname1\CDName2\c.mp3
c:\music\artistname1\CDName2\d.mp3
c:\music\artistname1\CDName3\CD1\a.mp3
c:\music\artistname1\CDName3\CD1\b.mp3
c:\music\artistname1\CDName3\CD1\c.mp3
c:\music\artistname1\CDName3\CD1\d.mp3
c:\music\artistname1\CDName3\CD2\a.mp3
c:\music\artistname1\CDName3\CD2\b.mp3
c:\music\artistname1\CDName3\CD2\c.mp3
c:\music\artistname1\CDName3\CD2\d.mp3

Note that I have multiple artists under c:\Music. I have lot's of CDs for
each artist and some of them have CD1 and CD2. What I'd like is to write the
following in Excel

A1 (Artist Name) A2 (CD Name) A3 (CD 1 or 2 -- if any) A4
(Song Name)
artstname1 CDName1 CD1
a.mp3
artstname1 CDName1 CD1
b.mp3
artstname1 CDName1 CD1
c.mp3
artstname1 CDName1 CD1
d.mp3
artstname1 CDName2 CD1
a.mp3
artstname1 CDName2 CD1
b.mp3
artstname1 CDName2 CD1
c.mp3
artstname1 CDName2 CD1
d.mp3
artstname1 CDName3 CD1
a.mp3
artstname1 CDName3 CD1
b.mp3
artstname1 CDName3 CD1
c.mp3
artstname1 CDName3 CD1
d.mp3
artstname1 CDName3 CD2
a.mp3
artstname1 CDName3 CD2
b.mp3
artstname1 CDName3 CD2
c.mp3
artstname1 CDName3 CD2
d.mp3

Any help would be greatly appreciated :)

Thanks
Michael
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro with actions on variable/dynamic pathnames 1
Macro: make list of all files in all folder with sub folders 5
Command Line to Open/Close an Excel Files , macro print it in a designated Folder 1
Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
Import File Information 0
Open files from folder by matching name 1
Open File Macro 3
Macroproblem: Proceed another macro after end of loop 5

Top