Re: Recursive Function + File searching to return path


Thanks for the help folks, I appreciate it.

I do have more questions though, if you all would be so kind...

I looked at the Add in link, and I don't think I will be able to use
this. Then end goal for this system is to place it on a network drive
for many users to access. I don't want to force all users to download
and install an add on (especially since some/most of these users will
be computer illiterate).

Otherwise both the VBA codes provided in responses seem to be the way I
want to go. The problem is both those codes surpass my VBA knowledge.
I don't entirely know what does what and how it all works (and as such,
how to use it). Could anyone please explain the code and what it does.
Ideally I wanted a macro to go through directories and make an output
list of all the directories and files it finds. I think the best way
to explain this would be with an example. Imagine this is the
Hierarchy:

Tier 1 Filename: A.xls
--Tier 2 Filename: B.xls
----Tier 3 Filename: C.xls
----Tier 3 Filename: D.xls
------Tier 4 Filename: E.xls
------Tier 4 Filename: F.xls
------Tier 4 Filename: G.xls
----Tier 3 Filename: H.xls
----Tier 3 Filename: I.xls
--Tier 2 Filename: J.xls
----Tier 3 Filename: K.xls
----Tier 3 Filename: L.xls
----Tier 3 Filename: M.xls
--Tier 2 Filename: N.xls
----Tier 3 Filename: O.xls
------Tier 4 Filename: P.xls
------Tier 4 Filename: Q.xls
----Tier 3 Filename: R.xls

Ok. Bear with me, I'm sorry if this doesn't make sense. This 'file
finding' spread*** is going to be in a folder which contains another
folder. That other folder contains the entire hierarchy. The way the
Hierarchy itself is organized is the folder you click on will contain a
excel *** with the same name as the folder its contained in. In that
folder will also be the folder for the next level sheets for the
hierarchy. IE: the first 2 tiers should look like this.

\\NetworkDrive\FileFinder.xls
\\NetworkDrive\A -[dir]-
\\NetworkDrive\A\A.xls
\\NetworkDrive\A\B -[dir]-
\\NetworkDrive\A\B\B.xls
\\NetworkDrive\A\B\C -[dir]-
\\NetworkDrive\A\B\D -[dir]-
\\NetworkDrive\A\B\H -[dir]-
\\NetworkDrive\A\B\I -[dir]-
\\NetworkDrive\A\J -[dir]-
\\NetworkDrive\A\N -[dir]-

I hope that makes sense.

In any case, each spread*** has a distinct filename and they all have
a 'tier' value. The recursive function I was planning on writing (since
VBA can handle recursion -- thanks for the answer by the way, folks!)
was a spread*** that starts at the FileFinder.xls level, then goes to
the first Excel Find it finds, which will be A.xls. It then needs to
output the excel filename (with or without .xls extension, preferably
without) to the first available row in a column I will have reserved
for this output in the FileFinder function. After that it needs to
look in the A.xls directory and it will see the next directories. It
should go into that directory (and bump the tier counter to 2) and
output the XLS file it finds, which will be B.xls. The output column
will actually be two columns. Both the tier and the filename will be
output. From B it will look for another directory, and find C, which
will be output with tier 3. From the "C" directory, it won't find any
other directories before it, which will be the termination/end for the
recursive function. It will drop the Tier back to 2, and go back to
the B directory, where it will find the next directory which will be
"D". ETC... all the way down the list.

For the Hierarchy above, the output should resemble exactly this:
FileName / Tier
A 1
B 2
C 3
D 3
E 4
F 4
G 4
H 3
I 3
J 2
K 3
L 3
M 3
N 2
O 3
P 4
Q 4
R 3

Does that make any sense? I hope so.

Either way, I haven't exactly started any coding work for this, so I'd
be open to other suggestions if the more experienced VBA users know a
simpler way to accomplish this. I am considering axing the recursive
part completely and just update the 'output' manually as I add/remove
spreadsheets to the hierarchy. I just figured this way would be
simpler.

After typing this all out its dawned on me. I think I would rather
code this myself, but I definitely need help. I don't know how to
frankly. I guess all I am trying to say is, in this case I would
rather be taught to fish then be given a fish. . . if you catch my
drift.

Again though, thanks everyone for your time and help. These forums
have never let me down :).


--
ph8
------------------------------------------------------------------------
ph8's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19871
View this thread: http://www.excelforum.com/showthread.php?threadid=387116

.