07-18-2005, 04:35 AM
|
|
Registered User
|
|
Join Date: 13 Feb 2005
Posts: 66
|
|
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' spreadsheet 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 sheet 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 spreadsheet 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 spreadsheet 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  .
|