In support of these Forum Threads
https://www.excelforum.com/developme...ml#post4440512
https://excel.tips.net/T008233_Findi...e_Desktop.html
http://www.excelfox.com/forum/showth...ll=1#post10420
http://www.excelfox.com/forum/showth...ll=1#post10421
Original Post was the eigth Post after this one_.....
http://www.eileenslounge.com/viewtop...185272#p175068
_...... but it is no longer there currently
_.. Original Post: ( Post Split over several Posts in this Thread ( It was one Post originally )
-::::::................
)
Hi,
I found the code here to loop through all folders and sub Folders very interesting and revealing.
I am only a novice but have answered more Threads then I can remember doing this and almost always the method I and others use is the same one. The code here is different..
So I am mainly just adding another solution as well as discussing and comparing with the solution given here by Rudi.
I am mainly looking at applying the codes to get to the point of being able to "do stuff" with each File. For the OP "doing stuff" was to Find and Replace Values in all excel files of a main Folder, including all Files in Sub Folders. But the actual Doing stuff is rarely the difficult bit. The main work is to "get at " all Files.
What really caught my eye with this Thread is that it does this without using the, as many people find a big mystery, the process of recursion.
I thought it would be very useful to write a code where the "doing stuff " was to print out to a Spreadsheet a Full Listing of all The Folders, Sub Folders., and all files within.
So for any required " doing stuff " code you could first run the code, which asks you to select your main Folder. The Print out lets you see and check that you are "getting" at each File you want. The code can then be modified by simply replacing at those lines which print out the information, the code lines necessary to do stuff to each file and / or Folder.
_..........................................
I will present two codes, a version of Rudi's and mine which is based on the classical Recursion Way.
Here then some quick notes on the codes.
Rem 1) Just some Worksheet info. Currently the Code accesses the first tab from the left ( Usually your "Sheet1" ). Identical for both codes.
Rem 2) Identical for Both Codes. Calls up a dialogue box in which you may enter the Start Folder in which all Folders and Sub Folders of interest are in. ( There are at least 3 ways in VBA I know to get that, I just chose one of them:
http://www.mrexcel.com/forum/general...plication.html
( The one using an Object that has a lot to do with Window things ).
http://www.mrexcel.com/forum/general...plication.html
I did the above just to practice something new to me.
The Application.FileDialog(msoFileDialogFolderPicker)
Originally dine by Rudis Way is probably better. That is just a VBA Property that pulls up a dialogue box, in this case the one that lets you pick a Folder.
_................................................. ............
Rem 3) Similar for both codes.
Sets up and allows us to use the Microsoft Scripting Runtime Library, which allows us to do lots with Files and related things.
Rem 4) Positional Info variable declaration.
Variables for Positons of where I print the Folder and File Info in the Worksheet.
For that the Range Object of the Top Left of where a "Explorer" Listing should go has the Cells Property applied using the Co ordinates I determine to give the Position in the Worksheet in which to paste out the Folder or File name
For Rudis Code the Queue thing is also declared
For Rudis Code I have variables for the count of Folders in the next Folder level, and for the count of the current Folder level being looped through This is the actual stand at the time within the Queue.
Rem 5 ) Here the codes differ.
Explanations are given extensively in the code 'Comments, best viewed in the VB Code Window whilst stepping through the code in debug ( F8 ) ( Whilst if possible also looking at the Spreadsheet at the same time )
Briefely in Words.
My code. The Classic recursion Type.
The Routine at Rem 5A) is called initially from the
Sub DoStuffInFoldersInFolderRecursion()
Code. It passes to it this first time the Main Folder.
This called subroutine starts going into the next level "down" or "to the right" of Sub Folders in the given Original Main Folder.
It does stuff for Each Sub Folder and files, if any , therein. ( Using a For Next Loop typically )
After that it calls itself !!!! It takes into it the current Sub Folder.
At this point most people get confused. I think, as I am thick, and can understand the following explanation , then it may be easy for most people to understand
The thing that is often missing at this point is knowing what VBA actually does when this happens. Quite simply it makes a NEW Copy of the Routine, completely independent of the Original Routine calling it. And it starts running that. The original Routine is "frozen" by VBA. And VBA stores somewhere( typically referred to as in a "Stack Row" or "Stack" ) all the variable values used in the Calling Routine and "freezes" them at their current values as well.
In the New copy all variable are new and independent of those in the Original. Unfortunately you never think you see this new Copy, but you do. If you step through such a recursion code, when it "looks" like it springs back to the original when the Code calls itself, you are actually seeing at that point the Copy.
So you see, if you have a couple of Folders, and the first has a Sub Folder in it then the following happens:
You do stuff in the First Folder. Then that Routine "freezes" as it calls itself. The current Sub Folder is taken into the "Copy" Routine. The "Copy" Routine then Does the same for the all Sub Folders now in the current Sub Folder. Important is that the Code line which calls itself is within the main For Next. So if there are no more Sub Folders, the Copy Routine will end. This occurs in our example here after the one Sub Folder. Effectively the "Copy" Routine then "dies" The original Routine then Unfreezes. So the next of our two Folders is gone through.
The only difficulty I had in writing the particular Recursion code is that I wanted to show each Folder "level" down the Folder Chain at each column going "down" or "to the right" as typically seen in a classic Explorer Window. The problem is how do I know which "Copy" Routine I am in. Every successive Copy will relate to a run in a the next "down" or "to the right. I cannot simply add a progressively increasing count, as in the recursion Code I will be going "back and forth" depending on if and how many Sub folders there are. I need a way to know at which "level" of Sub Folders I am in when in any progression back and forth.
I do that as follows. I mention it here as it does demonstrate clearly again how recursion works.
Inside the Routine towards the start is a variable,
CopyNumber.
This will be a unique variable for each "Copy" Routine. Every time the Routine is called a number is taken in at the value inside a variable in the signature line
CopyNumberFroNxtLvl
For the very first call it is set to 1 in a variable whose value is taken in at its value, as a value, in the Signature line
Within the Routine this value is given to
CopyNumber.
Only
If CopyNumber = 0
That is to say if
CopyNumber
Had never been given a number
When the function calls itself to takes in by value at the value iif using the value of
CopyNumber + 1
( Call LoopThroughEachFolderAndItsFile(myFldrs, celTL, rCnt, CopyNumber + 1) )
This has the effect that when you go to the "next down" or "next level to the right" only the first time will
CopyNumber
Be assigned, and its value will take an incremented number giving an indication of you "level"
This value is frozen when you go "further down" in the next "Copy Routine" . But When you come back up, it "thaws out" and is used within so that my line which prints out information will be in the correct "column" which is an indication in my final Output of the "level" of my folder ( and possibly Files within )
(celTL.Cells(rCnt, 1).Value = myFldrs.Path: celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name )
( celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name )
Reply With Quote
Bookmarks