Notes tests. Excel VBA Folder File Search

[DocAElstein]

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 )

[DocAElstein]

_................................................. .........

Rudi's code from Rem 5R)

This works differently in a way I have never seen before. There is no need to call a "recursion Routine"

Initially The main Folder is "put" into a "Queue" ( at the " back " of it )
https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx
https://msdn.microsoft.com/de-de/lib...(v=vs.90).aspx
( I expect in the "Queue" is just holding the Pointer to the actual Folder Object
The code the does a similar For Next as in my Code. One major difference is that the first thing it does is at each Folder is to go through every Sub Folder therein and "Put" it at the "back" of the queue. It takes the current folder being "looked" at "out" of the Queue from the "front".

It effectively then "stacks up all" the Folders in the next level down. Eventually after it goes through every Folder in the current level it will reach the point where it starts on the next level. So effectively it does not go "back and forth" like mine does. Rather it has "looking up" or "back from the front" first all the first level Folders, then all the next level Folders , then all the next, and so on.
If you look at the difference in the output that I get from mine and Rudi's code, you will soon see the corresponding difference.

Again the tricky bit for me was to get a Variable to indicate the "level" or "column to the left".

What I do in this case is count every time a Sub Folder is put in the back of the Queue.
NxtLvlCnt
This will finally give an indication of the Number of Sub folders at the next level.
I have second count variable
CurrentLvlCnt
Which is originally set to the last level count ( set initially to one for the original main folder ), which is successively decreased each time a Folder is "taken out" of the queue. When it reaches zero it is an indication that we have reached the next series of Next level Sub Folders. When that occurs it is given the value of the next level Count, and the next level Cont is then reset to Zero.

_................................................. .......

For both code I finally added a bit of Error handling. I did this as when I tested with many real files , I often had an error if , for example the "doing stuff" involved opening a file. If this happens you are told what error occurred and to which file, then you go on to the next. ( I assume that errors do not occur in the original code that just Prints out the "explorer type" Listing. If it did I expect the output could go a bit out if step !! ) )

_...............................................

So I give here some typical output from a run of both codes. To make it a bit easier I include the example set of Folders I used. ( I hope they all come up. By me only a few Folders come up, although the are "indicted as all there " ? ? )
https://app.box.com/s/onj6ntvwkxbo1088x7e0tca2gst45hnq
( Edit : Here is another Folder to try https://app.box.com/s/9e6xnb65fijjhl7bk0q6gzzriihkzibw )


words I have a main
EileensFldr

That has three sub folders in it. Therein are files and further sub Folders and files etc…. as seen in the listing the Code gives.

Initially the code is set to run from a file in the same directory as folder EileensFldr

[DocAElstein]

Output Given from my code

Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F
1	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr	EileensFldr
2
3	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_1		Fldr1_1
4			File1_1a.xlsx
5			File1_1b.xlsx
6
7	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2		Fldr1_2
8			File1_2a.xlsx
9			File1_2b.xlsx
10			File1_2c.xlsx
11
12	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1			Fldr1_2_1
13				File1_2_1a.xlsx
14				File1_2_1b.xlsx
15
16	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1\Fldr1_2_1_1				Fldr1_2_1_1
17					File1_2_1_1a.xlsx
18
19	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1\Fldr1_2_1_2				Fldr1_2_1_2
20					File1_2_1_2a.xlsx
21					File1_2_1_2b.xlsx
22
23	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3		Fldr1_3
24
25	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1			Fldr1_3_1
26
27	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1\Flsr1_3_1_1				Flsr1_3_1_1
28
29	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1\Flsr1_3_1_1\Fldr1_3_1_1 _1					Fldr1_3_1_1_1

EFFldr

[DocAElstein]

Correspondoing Output given by Rudi's code

Using Excel 2007 32 bit

Row\Col	A	B	C	D	E	F
1	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr	EileensFldr
2
3	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_1		Fldr1_1
4			File1_1a.xlsx
5			File1_1b.xlsx
6
7	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2		Fldr1_2
8			File1_2a.xlsx
9			File1_2b.xlsx
10			File1_2c.xlsx
11
12	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3		Fldr1_3
13
14	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1			Fldr1_2_1
15				File1_2_1a.xlsx
16				File1_2_1b.xlsx
17
18	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1			Fldr1_3_1
19
20	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1\Fldr1_2_1_1				Fldr1_2_1_1
21					File1_2_1_1a.xlsx
22
23	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_2\Fldr1_2_1\Fldr1_2_1_2				Fldr1_2_1_2
24					File1_2_1_2a.xlsx
25					File1_2_1_2b.xlsx
26
27	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1\Flsr1_3_1_1				Flsr1_3_1_1
28
29	H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\Eil eensFldr\Fldr1_3\Fldr1_3_1\Flsr1_3_1_1\Fldr1_3_1_1 _1					Fldr1_3_1_1_1

EFFldr

[DocAElstein]

Codes:
Rudi's Code
Sub ReplaceInAllSubFoldersRudisQing()

'' http://www.excelforum.com/excel-prog...ubfolders.html

'  Rudi     http://www.eileenslounge.com/viewtopic.php?f=27&t=22499
Sub ReplaceInAllSubFoldersQing()
Rem 1Q) Some Worksheets and General Variables Info
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets.Item(1) 'Worksheets("RudyMSRQueue") 'CHANGE TO SUIT YOUR WORKSHEET
Dim strDefpath As String: Let strDefpath = ThisWorkbook.Path ' Any Path to Folder to test this code! here we simply use the Path where the File with this code in is
Dim strDefFldr As String: Let strDefFldr = "EileensFldr" 'Just for an initial suggestion
Rem 2Q) Get Folder Info ( Using VBA Application.FileDialog(msoFileDialogFolderPicker) Property )
Dim strWB As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Folder Select "
    .AllowMultiSelect = False
        If .Show <> -1 Then
        Exit Sub
        End If
    Let strWB = .SelectedItems(1) & "\"
  End With

Rem 3Q) Microsoft Scripting Runtime Library
'Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")'Late Binding
Dim FSO As Scripting.FileSystemObject 'Early Binding alternative  activate a reference to the Microsoft Scripting Runtime Library in the Tools > References menu of VBE.
Set FSO = New Scripting.FileSystemObject

Rem 4Q)'Some variables for Positon of Things
Dim rCnt As Long, clmLvl As Long: Let clmLvl = 1: Let rCnt = -1 'rowCount is genaraly increase for a new entry, Column "level" is intended to give an indication of how far down ( to he right ) you are in the Folder chain. Ste to 1 for the first mainn Initial Folder.
Dim CurrentLvlCnt As Long: CurrentLvlCnt = 1 'Count of the Number of Folders in the Folder level currently beig run through.
Dim NxtLvlCnt As Long 'Count of the Number of Folders in the next level
Dim queue As Collection
Set queue = New Collection
queue.Add FSO.GetFolder(strWB) 'Main Folder Put at position 1 of Queue'''''
Dim celTL As Range: Set celTL = ws.Range("A1") 'Top left of where Listing should go
'Application.ScreenUpdating = False

Rem 5Q) Main loop. Do While Queue is not Empty effectivelly goes through all Folders
Dim oFile As Variant, oFolder As Variant, oSubfolder As Variant '                                                                 Can also be variant Types or Objects. - Must be for Late Binding
    Do While queue.Count > 0 'Main Loop. Does as many times as there are things ( Folders here ) stacked in the Queue========
    Set oFolder = queue(1) 'Next Folder .... effectively
    queue.Remove 1 'de-queue'......"taken" from start of Queue. ( Actually it is assigned to a variable, then removed from the Queue, which probably just has the Pointer to it.
    CurrentLvlCnt = CurrentLvlCnt - 1 'de-the count for numbers in in this current Folder level
    ''''''''Doing Stuff For the Folder
    rCnt = rCnt + 2 'Move on a line and a spare Line for every Folder Entry
    celTL.Cells(rCnt, 1).Value = oFolder.Path: celTL.Cells(rCnt, clmLvl).Offset(0, 1).Value = oFolder.Name 'Cell poroperty of Top Left Cell Range Object uset to position output.
    ''''''''End Doing Stuff for each Folder
    '5Qa) Add any Sub Folders from current Folder at end of queue
        For Each oSubfolder In oFolder.SubFolders 'For as many ( if any ) Sub Folders In the Current Folder
        queue.Add oSubfolder 'en-queue.. add the Sub Folder on at the end of the Queue
        NxtLvlCnt = NxtLvlCnt + 1 'en-the count of the Folders in the next Level..Increase our count of the Folders in the Next folder level
        Next oSubfolder
        '5b) Doing Stuff for every file in current folder
            For Each oFile In oFolder.Files
            '''''''Doing Stuff for Each File here
                If InStr(1, oFile.Name, ".xls") > 0 Then 'Option to select only if .xls ( or .xlsx or .xlsm ) type files
                rCnt = rCnt + 1
                celTL.Cells(rCnt, clmLvl).Offset(0, 1).Value = oFile.Name
                On Error GoTo ErrHdlr 'In case problem opening file for example
                'Set wbk = Workbooks.Open(oFile)
                'wbk.Close SaveChanges:=True
                Else: End If
            '''''''End Doing Stuff for Each File
NxtoFile:   Next oFile ' Spring Point after error handler so as to go on to next File after the File action that errored
        '5Qc) should we have reached the end of the current level of Folders, we reset the level Column for output, and make the new Current Folders in Folder level Count equel to the next one, as we go ion now to Folders from the next level.
        If CurrentLvlCnt = 0 Then
        clmLvl = clmLvl + 1 'Set column position 1 to the left "down" the Folder Level Chain.
        Let CurrentLvlCnt = NxtLvlCnt 'So the current Folder Level count of Folders becomes that last counted.
        NxtLvlCnt = 0 'Next level of Folders currently are not in the Queue. This will be re counted for the next Folders as Sub Folders are added to the back of the Queue
        Else
        End If
        
    Loop 'queue.Count > 0 main loop for all Folders=====================================================================
Application.ScreenUpdating = True
MsgBox "All Excel Files processed", vbInformation
ws.Columns("A:H").AutoFit
Exit Sub 'Normal End for no Erriors
Rem 6) 'Error handler section just put here for convenience
ErrHdlr: 'Hopefully we know why we are here, and after informing can continue ( to next file )
MsgBox prompt:="Error " & Err.Description & " with File " & oFile & ""
On Error GoTo -1 'This needs to be done to reset the VBA exceptional error state of being. Otherwise VBA "thinks"" Errors are being handeled and will not respond again to the Error handler.
On Error GoTo 0 ' Swiches off the current error handler. I do not really need to do this. But it is good practice so the error handler is only in place at the point where i next am expecting an error
GoTo NxtoFile
End Sub