Loop Through Files In A Folder Using VBA

**Excel Fox** · 08-21-2013, 11:17 PM

This is something that most of us would want to do more often than once in a while. Be able to loop through the files in a folder, and also use some sort of filter.

Code:

Sub EFLoopThroughFilesInAFolder()
     
    Dim strFile As String
    Dim strFileType As String
    Dim strPath As String
    Dim lngLoop As Long
     
    strPath = "C:\Journals"
    strFileType = "*.csv" 'Split with semi-colon if you want to specify the file types. Example ->> "*.xls;*.jpeg;*.doc;*.gif"
     
    For lngLoop = LBound(Split(strFileType, ";")) To UBound(Split(strFileType, ";"))
        strFile = Dir(strPath & "\" & Split(strFileType, ";")(lngLoop))
        Do While strFile <> ""
            'This is where you need to write what you need
            'The entire path of the file would be : strPath & "\" & strFile
            strFile = Dir
        Loop
    Next lngLoop
    strFile = vbNullString
    strFileType = vbNullString
    strPath = vbNullString
    lngLoop = Empty
     
End Sub

**snb** · 09-12-2013, 12:25 AM

I'd prefer

Code:

Sub M_snb()
   debug.print createobject("wscript.shell").exec("cmd /c Dir ""G:\OF\*.csv"" /b").stdout.readall
End Sub

**DocAElstein** · 03-28-2016, 03:51 AM

Originally Posted by snb

I'd prefer....

Ha, that was an interesting bit of fun..
I ran this version

Code:

Debug.Print CreateObject("wscript.shell").exec("cmd /c Dir ""H:\Excel0202015Jan2016\ExcelForum\wbSheetMaker\EFldr1_1\EFldr1_1_1\*.xlsx"" /b").StdOut.ReadAll

....................
It gave me the result I expected in the Ctrl G Immediate Window
File1_1_1a.xlsx
File1_1_2b.xlsx
But before it blinks at me something that looks like a Blank Black screen. I blinked happily back, as That and the code line looked something like I have a vague memory from about 30 years ago.

: But that is just a fun code. A nice “one liner.” The simple File listing is nice. But too restrictive to be of much use. It can only do limited stuff – here a list of the Files in the Immediate Window. But it did catch my eye as a neater, al be it restricted version, of something i did recently...
_.... So
_...........
Just to add something here: A simplified code version of some i did here:
Get Value Function Loop through all files in folder and its subfolders
Eileen's Lounge â€¢ View topic - Find and Replace Values in all excel files of Directory

It is along the lines of that from Excel Fox. It has a bit where you would ....“you need to write what you need..”.. to do stuff for each File. But it is extended to allow to do stuff for all Folders, Sub Folders, and all Files therein.
And i initially Wrote the codes to give out an explorer type output to a sheet. This shows then the Root type plan of all Files, and Folders ,within a main Folder. ( In this simplified version it gives the Output to the current ActiveSheet)
So it is an extended version of what the snb code gives in the immediate Window. But here it is printing to the Active Sheet rather than the Immediate Window ( I wish i knew how to do a snb version, but I expect it is beyond the limit of a “one liner” anyway... )
When you run the code it asks you for the Folder you want to look at.
Code: ( _ .. in more detail here:
Get Value Function Loop through all files in folder and its subfolders
_....... )
(-.. and the typical output it gives here
Excel Help Forum
_......)

Code:

'
Sub EFDoStuffInFoldersInFolderRecursion()
Dim myFolder, strWB As String
    With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Folder Select ": .AllowMultiSelect = False
        If .Show <> -1 Then
        Exit Sub
        End If
    strWB = .SelectedItems(1) & "\"
    End With
    With CreateObject("Scripting.FileSystemObject")
    Set myFolder = .GetFolder(strWB)
    End With
Dim CopyNumber As Long:  CopyNumber = 1
Range("A1").Value = myFolder.Name: Columns("A:C").AutoFit
Call EFLoopThroughEachFolderAndItsFile(myFolder, 1, CopyNumber)
Columns("A:H").AutoFit
End Sub
Sub EFLoopThroughEachFolderAndItsFile(ByVal fldFldr As Object, ByRef rCnt As Long, ByVal CopyNumberFroNxtLvl As Long)
Dim myFldrs As Object
Dim CopyNumber As Long
If CopyNumber = 0 Then CopyNumber = CopyNumberFroNxtLvl
    For Each myFldrs In fldFldr.SubFolders
    ''''''''Doing stuff for each Folder
    rCnt = rCnt + 2
    Range("A1").Cells(rCnt, 1).Value = myFldrs.path: Range("A1").Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name:
    ''''''''End doing stuff for each Folder
    Dim oFile As Object
        For Each oFile In myFldrs.Files
        ''''''''Doing Stuff for Each File
         rCnt = rCnt + 1
        Range("A1").Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name
        ''''''''End Doing Sttuff for Each File
        Next
    Call EFLoopThroughEachFolderAndItsFile(myFldrs, rCnt, CopyNumber + 1)
    Next
End Sub

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

Alan

**DocAElstein** · 12-29-2017, 01:05 AM

All Sub Folder and File List from VBA Recursion routine. Explanation and Method Comparisons. Dec 2017.

Like a lot of us, I have answered a Thread request to do the above quite a few times. I need a routine myself now to help search my own stuff, and I am learning Python, and am doing a Python code alternative in parallel, so I thought It would do no harm to share here an explanation of the most commonly code version done in VBA, ( Recursion type code ) , and welcome any comments or alternatives… The code discussed here makes use of the FileSystemObject Object, to get access to a computers File system

Recursion Process , a quick alternative simple explanation.
I like to recap and explain this in simple terms as I think its complexity is over rated due to over complicated words and explanations…
Recursion is often described as ..”.. a routine that calls itself..”..
Before I sussed out what that was about, it sounded like a very confusing and clever thing. I find it very simple now, thinking about it in a different way. The following may not be a truly technically correct description of what is going on, but it helps me to understand it, (and I haven’t met anyone yet that fully understands what the internal workings are doing anyway in a recursion process anyway)..
So here you go:
Two things I find are good to bear in mind initially
_ (i) I would forget initially the ..”.. a routine that calls itself..”.. stuff. That can be misleading on my opinion. I think it is more helpful to say “…A routine that pauses, whilst another copy of the routine is done”….
_ (ii) Be careful if you use the Debug mode ( F8 when looking at the code in the VB developer environment ) to step through a recursion routine. That can be misleading as well.
( The output that my code example produces, if looked at whilst stepping through the code in Debug mode, can help make it a bit clearer what is going on. The key to both understanding and not getting confused when in Debug mode is that typically you are actually stepping through many separate copies of a routine. What is actually going on does not show up very well in Debug mode as it appears when stepping through a code in Debug mode as though the same routine is done many times. That is not the case. By following in the DeBug mode in the VB Editor it is easy to overlook that there will typically be many separate variables for each copy of a the routine which ,although they have the same name, are independent.

Any code is just a master set of instructions and a run follows copy of that instructions
Think of a code as just a written set of instructions and when a code “runs” a copy of the instructions are made and followed in order to do something. Usually the copy is trashed when the code stops, ( Ends ) , but there may sometimes be something left over, or changed or made at the End
Broadly speaking we can think about 3 main sort of code situations, and the third one is recursion
_1) A simple single code, a Sub( ) is a Master copy of a set of instructions. You make a copy of the instructions, follow them and do it. Then stop / End. To a first approximation, usually, but not always, everything is over with at the End and gone. Usually the ( ) is empty , - the ( ) is meaningless in such a code case.
_2) Codes where an initial simple code ( calling ) a Function (oftenSomethingHere) or another Sub( oftenSomethingHere) . In a simple example of this situation, it is a code , often referred to a the main (Sub ( ) ) or main routine, that “Calls” a Function( oftenSomethingHere)or another Sub( oftenSomethingHere) . In simple terms this second routine is just another master copy of a set of instructions. The main difference is that as there is “( oftenSomethingHere)” that means that something is “taken into it” from the Calling main routine.
So just to be clear: We have 2 routines: We have a main routine and a second Called routine. The second routine cannot usually be run on its own, - it needs another routine to Call it, as it usually is given something. ( VBA syntax does not usually let you set off such a routine on its own with “(SomethingInHere)”. **You have to give the “SomethingInHere” at the “Call” code line which “calls” or “sets off” a copy of the second routine instructions.
So typically in this second code situation you initially start doing 1) , a simple Sub ( ) , as before, but before you are finished, you pause what you are doing ( “freeze” the situation, not stop / End , so you may still have stuff temporarily stored or hanging around somewhere ). This pausing happens at the “Call” line. You then make a copy of the second routine instructions. Usually , but not always, you may take something with you from the Sub, ( ** in the “(SomethingInHere)” bit ) , then Follow that next set of ( second routine ) instructions. Usually that will involve doing something with what you took with you. You do and finish / End that. Often , but not always, you may take something back which you did or made in the second routine, and then use that when you then “unfreeze” and carry on the initial (Sub) set of instructions from the point where you left them, ( at the “Call” line ). Then stop / End. To a first approximation, usually, but not always, everything is over with at the End and everything is gone.
_3) Recursion situation: This is not really much different to 2). In fact it is almost the same. The situation is that you are in the second set of instructions ( second routine ). But before you are finished , you once again pause. You make a second copy of the second routine instruction. Once again, you freeze the situation of the fist copy of the second routine instruction, ( and remember, you have already previously frozen / paused the initial main Sub instructions. So a couple of code instruction copies are hanging around, frozen, on hold, and waiting to restart from where they paused).
So you now do again what you just started but you do it this second time completely first before you go back and finished doing it the first time… Sounds a bit pointless initially. But the point is that usually what you take with you and consequently what you bring back is different. So you do different things using the same set of instructions, and/ or what you end up will be different each time as it depends on what you take with you when you go through each copy of the ( second routine ) instructions . You can keep pausing the current copy of the second routine instructions and starting again with a new copy set of the second routine instructions, at least until something gets overfilled.
Some process/place in the computer has to keep a record / track of all the copies of the second routine instructions currently not finished along with any “frozen” values of variables locally held in those copies , which would have been also frozen at the values had at the pause, and which will be needed for when each copy restarts. That process/place is usually referred to as a stack. So when that gets overfilled you get the famous “Stack overflow” error. In the practice you would usually have some condition checked which needs to be satisfied before you start following a new copy of the second routine instructions. In the following example, that condition to be satisfied will be if there are Sub Folders in a current Folder that is being searched for Excel Files.

Recursion example

Purpose of the code:
The aim is to produce a Tree root sort of a sketch in a worksheet to show all the Folders, Sub Folders and files starting within an initial Folder. That is what I need, for example , to get finally a list of Titles to help me search for a particular subject area, to see if I have a code already to solve a particular problem . ( I don’t really need a worksheet full of the titles, but it looks nice and does demo very well the recursion process .. in particular the “up and down” nature of the process, which is very difficult to explain in words. It is also handy to have a worksheet saved showing pictorially all the files and where they are. It ends up looking like a classic Explorer Window)

General Code Form and strategy.
The code is a shortened version of this one Recursion version:
https://www.excelforum.com/excel-pro...ml#post4348630
various code variations in VBA and full code descriptions https://www.excelforum.com/developme...directory.html .
To simplify for demo purposes, assume a main Folder is known and so is hard coded in this example.
( I supply the demo Folder with my code example. You will need to download it and place it, unzipped , in the same Folder in which the Excel File is in which you place and run my code )

Following on from the idea explained in the first post.. it follows that the main Sub will pass over to a second routine ( or put another way the second routine will take (here) ) a single Folder which may have **Excel Files and / or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. Those Sub Folders could themselves have Excel Files and/ or Sub Folders. …etc.
So the second routine would usually “take in” just a Folder. It then does For all the Sub Folders in it the following:
_ Increments the row count by 1+1, then writes the folder name. ( 1 is for a new line for the next Folder name entry, and the extra 1 is so that an extra empty line is made for clarity before each Folder name. )
and
_ writes the name of any contained excel File File names before checking for Sub Folders, and if so it would, For Each of these Sub Folders´, “take” each folder in a run of a new copy of the second routine
Generally , in most seen examples of this code type, we do not need to take anything “out” at the End, as more usually the code is intended just to “do something” to Folders and/ or Files, or look in them for something.
However, in the case of this particular code example, some variables are passed as they keep track of where we are vertically and horizontally in the root structure which will be. The vertical is simply incremented by one every time the second routine is used. The Horizontal is adjusted appropriately to keep track as we “go up and down” ( shown pictorially as right and left )

In the many published examples of such codes, the main code will always be a simple Sub( )
The second routine would normally have a form something of this form: Function(FolderToLookIn) or Sub(FolderToLookIn)
The difference in these versions of the second routine is usually said to be that you are able to return something with the Function and not the Sub, as it would often be seen in this form,
Variable = Function(OneOrMoreThingsTakenInhere) , whereas the Sub would just be Called, like
Call _ TheSubName _ (FolderToLookIn)
That is Bollox actually!!!, but would make no difference anyway in the usual form as you do not want anything back, so just Calling either ( VBA allows you also to do that to the Function also ) in these forms sets them off, (which is all that would normally be done):
Call _ TheSubName _ (FolderToLookIn)
or
Call _ TheFunctionName _ (FolderToLookIn)

In my example I need to use the Sub(SomeThingsInHere) because in addition to “taking in” the FolderToLookIn , I want to also:
Give a cell as Top Left of where I want my output to start
and
pass a variable to be used By Referring to it ( giving it ) a count of the number of times a copy of the second routine instructions is run through. This will be used to increment the row number that each Folder name or Excel File name is written in. ( This By Ref method effectively “returns” a value !!!)
and
pass a Value to it ( giving it ) a number for the column number that is used for output. This effectively gives an indication of the Copy Number of the second routine copy being used. This is also an indication of how far “down” or “to the right” we are in the Folder system

The nice thing about the final output is that as you work down you can see what is going on, as different copies of the second routine start and pause or stop.
The next post works through the code in detail.
The over next post gives an example, using a supplied Main Folder which contains Folders and Files. I give a screenshot of what you should be able to reproduce , and on that screenshot have added some notes in orangeto show what is actually going on ( https://imgur.com/FjjUMYz )

( ** To simplify initially the comparison of this code with a Python program alternative, all Files are examined and printed out into the worksheet “explorer” output example, but there is a simple few lines ( ' ##### commented out ) which can be used to select Excel Files only. )

**DocAElstein** · 12-29-2017, 01:06 AM

Recursion Codes process Description example

( ** To simplify initially the comparison of this code with a Python program alternative, all Files are examined and printed out into the worksheet “explorer” output example, but there is a simple few lines ( ' ##### commented out ) which can be used to select Excel Files only. )

Here are the two full codes with full 'Comments and the descriptions below follow that code.
_1) Calling Sub( _ ) routine.
http://www.excelfox.com/forum/showth...0419#post10419
_2) Second routine ( routine used in recursion process, and called initially, ( once ) , from _1) ). This will usually “Call” itself many times, after it is set off by _1 )
Sub VBALoopThroughEachFolderAndItsFile(Folder, , , __ :
http://www.excelfox.com/forum/showth...0419#post10419

( ( This is a shorter simplified both codes version : ) To be added )

( Codes are also in the first Worksheet Code module of this Workbook: ( ‘== ' Dec 2017 For Python Comparison. https://app.box.com/s/gfuintgifu1hgw5nap3jriz2x8mp911x ) )

Full codes description.
This description is of the two codes. The codes should be copied to the same code module. The codes are split into a few sections for compatibility with some earlier alternative code versions, ( some of which do not use the recursion technique ) ( https://www.excelforum.com/developme...ml#post4440515 https://www.excelforum.com/developme...ml#post4440512 ),

Calling Sub VBADoStuffInFoldersInFolderRecursion ( _ )
Rem 1A) Worksheet info.
Rem 2A) Initial ( Start ) Folder
Rem 3A) System Library Object
Rem 4A) 'Some variables for Position of Things

Sub VBALoopThroughEachFolderAndItsFile(Folder, , , __
Rem 5A) Doing while Sub ( Under ) Folders are still found
__'5Ab) Doing stuff for current Folder
__'5Ac) Doing stuff for current file
( Rem 6) Handling Errors )

Rem 1A) Worksheet info.
Often such codes make no particular reference to a worksheet, and the output to a worksheet is either not required or is simply by default to the active worksheet starting from Top left of first cell A1. In this code version the worksheet can be hard coded here, as can the Top Left cell from where the output should start.

Rem 2A) Initial ( Start ) Folder
For this example the example main Folder to be searched for is named " EileensFldr". It can be seen and downloaded from here : https://app.box.com/s/k3m0xfm89msqpoarpx93onyyqupydzk8
EileensFldr zip Download.jpg https://imgur.com/f7V2PTL
By me in Windows that downloads as a .zip File. To use the File, I store it anywhere, then I make a Folder with the name EileensFldr in the same Folder which contains the File in which I place the macros being described here.
EileensFldr Make Empty Folder.JPG https://imgur.com/6kolxi4
Then I copy all the files in Folder EileensFldr.zip
EileensFldr Contents Copy.JPG https://imgur.com/hgqg64w
And paste into the new Folder which is in the same Folder which contains the File in which I place the macros being described here.
EileensFldr Contents Paste.JPG https://imgur.com/4qK00eI
You may have a different method for “unzipping” a “Zipped file”, but the important thing is that you end up with both the File containing the macros and the example File in the same Main Folder.
Example Folder and Macro File in same Folder.JPG https://imgur.com/LWSUssc
The above requirement is only because of this code line _..
Let strWB = ThisWorkbook.Path & "" & "EileensFldr"
_.. That code line tells the code that the Initial Folder is in the same Folder as the file in which the code is.
Often such codes are written such that they ask you to select a Folder to be searched through. Here it is simply hardcoded to simplify the demo code.

Rem 3A) System Library Object, FileSystemObject Object
An external Library is made available which allows access to a computer File system.

Rem 4A) 'Some variables for Position of Things
The variables that are used to keep track of “where” we are in the Tree root structure are initialised.
We use a couple of variables:
_ We pass a variable, rCnt, to the second routine copies which are run, to be used By Referring to it ( giving it ) a count of the number of times a copy of the second routine instructions is run through. This will be used to increment the row number that each Folder name or Excel File name is written in. It is increased by 1 just before a File Name entry is made. Just before a Folder name entry is made, it is increased by 2. The extra 1 is just done for neatness, to give an extra empty Line before each Folder. The name for any contained Files will come directly under the Folder name.
and
_ We also pass a Value to the second routine copies which are run, which effectively gives an indication of the Copy Number of the second routine copy being used. The logic for this needs some very careful explanation. In the initial main routine Sub( ) this is passed a variable, CopyNumber1 , which we set in this code section to = 1 . That logic is fairly easy to understand: The copy set off by the Sub( ) is the first copy
Which variable value is passed when the second routine calls itself, and how that value is further used is very subtle, and once again, I think it is important to bear in mind that we typically will have independent copies of the second routine either running …. see –- Rem 5A)
( Finally in code section 4A) the main Folder Path and Name is written to the Worksheet, and then the second routine is set off for the fist time, that is to say, the first copy of that second routine is made and set off. )

Sub VBALoopThroughEachFolderAndItsFile(Folder, , , __
-- Rem 5A) Doing while Sub ( Under ) Folders are still found
At the outset of the second routine, the variable keeping track of the second routine copy number is dealt with. That logic needs careful explanation. 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” Folder “level”. I cannot simply add a progressively increasing count, ( as I can and do for the row for next output, rCnt ) , as in the recursion Code I will be going “back and forth” depending on if and where Sub folders are. I need a way to know at which “level” of Sub Folders I am in when in at any time. That variable is used to determine the column in which to write a Folder or File name. ( Each column in the final output represents a different Folder level )
I achieve the necessary as follows. It demonstrates well how recursion works. Here we go:
Inside the Routine towards the start is a variable,
CopyNumber.
This will be a unique variable for each “Copy” Routine. Ideally I would like to rename this at each level down/ to the right something like CopyNumber1, CopyNumber2, CopyNumber3 … etc. But, I cannot do that as VBA only allows me to type in the code window a single Master copy of the code. Effectively as the recursion process is going on, and we have a copy or the second routine, say the forth copy running, then VBA has stored, amongst other things, the following variables (in the “stack”, as it is called )….
Routine copy 4 : CopyNumber
Routine copy 3 : CopyNumber
Routine copy 2 : CopyNumber
Routine copy 1 : CopyNumber
That is to say we have a unique CopyNumber variable which is paused or in use. (Confusingly to us, they all have the same name. But VBA holds them in a different “stack place”, so somehow can keep track and use the appropriate one ). Only one ( or none ) will be in use at any time. The one in use at any time needs to hold an integer number to indicate the copy number or “how far down” or “to the right” we are.
Here is a working logic which is used:
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 , ( by the main first routine ), as mentioned, it is set to 1 in a variable whose value is taken in at its value, as a value, in the Signature line.
Towards the start of the second routine, the local variable, is given this passed value of 1
Subsequently, however, when the second routine “calls” itself, the value passed to CopyNumberFroNxtLvl will be given the
= ( value of the local variable, CopyNumber , from the routine copy doing the call ) +1
Hence as further copies are started, the integer held within the local variable, CopyNumber, will be one higher then the previous copy. This value is then either in use, or in the appropriate place in the stack, to be used when any paused copy resumes.
This somewhat complicated process is necessary so that paused routine copies have available to use the correct integer when they resume. This is possibly demonstrated better pictorially by looking at the output produced in the worksheet, along with the extra comments in orange https://imgur.com/FjjUMYz

The rest of section Rem5 follows a fairly standard format for this type of recursion code.
'5Ab) The initial condition here is also the means by which “stack overflow” is avoided: The main and major part of the second ( recursion ) routine is done only For Each Sub Folder found within the current Folder taken into the routine at the signature line. ( Note here, that should there be any Files in the main initial Folder, then this routine will not catch and list them, as the routine goes straight into looking in any Sub Forums found ).
Assuming one or more Sub Folders are found, then the code is at the classic section for … „Doing stuff for each Folder”,….. In this case the row for an output, rCnt, is increases by 2, ( 1 for a new line, and 1 to make an empty line to help make the final output a bit clearer ) , and then Folder name is pasted to a cell, whose co ordinates are given by rCnt for row, and CopyNumber for column. ( For Folders an initial column is also used for the complete full Path and Name )
'5Ac) Doing stuff for current file. A second For Each Loop, this time for any Files in the current Folder, is nested in the previous '5Ab) For Each loop. For every File, the row for an output, rCnt, is increases by 1 and the File Name is written to the worksheet. - The cell co ordinate given by rCnt and the same value of CopyNumber used for the cell co ordinate used for the File Name of the file in which the file or files are. That way the File names are neatly written directly under their Folder name.

Once all File names are outputted, the point is reached where the routine “calls itself”. So at that point a new copy of the second routine is made, and the Folder given is the current one , ( the one which had just had its Name and the Name of any Files within it outputted.) The variable passed to indicate the level of the folder is then given the value of
= ( the current CopyNumber )+ 1
Hence any further outputs made by the new copy just started will be written outputted 1 column to the right of the previous.

_..._____________________________
The next post shows the output that should be achieved with the given codes and sample Folder

Ref
http://www.excelfox.com/forum/showth...0144#post10144
https://www.excelforum.com/excel-pro...ml#post4348630
http://excelpoweruser.blogspot.de/20...-files-in.html
http://www.excelforum.com/excel-prog...ubfolders.html
http://www.excelforum.com/tips-and-t...ml#post4221356
http://www.excelforum.com/excel-prog...ubfolders.html
http://excelmatters.com/2013/09/23/v...-late-binding/
http://www.mrexcel.com/forum/general...plication.html
http://www.eileenslounge.com/viewtopic.php?f=27&t=22499
http://www.excelfox.com/forum/showth...0419#post10419

**DocAElstein** · 12-29-2017, 01:09 AM

Example Output using " EileensFldr". It can be downloaded from here: https://app.box.com/s/k3m0xfm89msqpoarpx93onyyqupydzk8 Edit Some folder may be missing - alternative see uploaded File
EileensFldr zip Download.jpg https://imgur.com/f7V2PTL
By me in Windows that downloads as a .zip File. To use the File, I store it anywhere, then I make a Folder with the name EileensFldr in the same Folder which contains the File in which I place the macros being described here.
EileensFldr Make Empty Folder.JPG https://imgur.com/6kolxi4
Then I copy all the files in Folder EileensFldr.zip
EileensFldr Contents Copy.JPG https://imgur.com/hgqg64w
And paste into the new Folder which is in the same Folder which contains the File in which I place the macros being described here.
EileensFldr Contents Paste.JPG https://imgur.com/4qK00eI
You may have a different method for “unzipping” a “Zipped file”, but the important thing is that you end up with both the File containing the macros and the example File in the same Main Folder.
Example Folder and Macro File in same Folder.JPG https://imgur.com/LWSUssc
This requirement is only because of the code line
Let strWB = ThisWorkbook.Path & "" & "EileensFldr"
Often such codes are written such that they ask you to select a Folder to be searched through. Here it is simply hardcoded to simplify the demo code.

Screenshot: EileensFolderExplainedOutput.JPG https://imgur.com/FjjUMYz
EileensFolderExplainedOutput.jpg

Using Excel 2007 32 bit

	Main Code	Second routine copy 1	Second routine copy 2	Second routine copy 3
		CopyNumber1=1	CopyNumber2=2	CopyNumber3=3	Main first routine, ( Sub( ) ) , starts
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr	EileensFldr				( Folder Details of Main Folder are written by Main routine ( Sub( ) ) )
					Main first routine, ( Sub( ) ) , pauses
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_1		Fldr1_1			First copy of second routine starts
		File1_1a.xlsx
		File1_1b.xlsx
					First copy of second routine pauses
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_1\Fldr1_1_1			Fldr1_1_1		Second copy of second routine starts
					Second copy of second routine Ends
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_2		Fldr1_2			First copy of second routine resumes
		File1_2a.xlsx
		File1_2b.xlsx
		File1_2c.xlsx
					First copy of second routine pauses
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_2\Fldr1_2_1			Fldr1_2_1		Another (Second) copy of second routine starts
			File1_2_1a.xlsx
			File1_2_1b.xlsx
					Second copy of second routine pauses
G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_2\Fldr1_2_1\Fldr1_ 2_1_1				Fldr1_2_1_1	Third copy of second routine starts
				File1_2_1_1a.xlsx

G:\Excel0202015Jan2016\ExcelForum\wbSheetMakerClsd WbADOMsQueery\EileensFldr\Fldr1_2\Fldr1_2_1\Fldr1_ 2_1_2				Fldr1_2_1_2
				File1_2_1_2a.xlsx
				File1_2_1_2b.xlsx
					Third copy of second routine resumes and Ends
					Second copy of second routine resumes and Ends
					First copy of second routine resumes and Ends
					Main first routine, ( Sub( ) ) , resumes and Ends.

Yellow indicates code copy currently running: The main code is copied and run once. For the example Folder shown,
the second routine is copied and run once for the first Folder level, twice for the second level, and once for the third level.

Worksheet: EFFldr

See also First Worksheet here:
https://app.box.com/s/gfuintgifu1hgw5nap3jriz2x8mp911x

**DocAElstein** · 12-29-2017, 01:09 AM

Spare post for later use

Posts number: 15631 ( I copied this post from the original post #6 which is post number 10422 )
Links to here :
https://excelfox.com/forum/showthrea...ll=1#post15631
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA?p=15629&viewfull=1#post15631
https://excelfox.com/forum/showthrea...-VBA#post15631
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA#post15631

Some extra notes.
These notes are for the following reasons …
_ In support of possible further answers to this Thread: https://excelfox.com/forum/showthrea...ll-Sub-Folders ( https://excelfox.com/forum/showthrea...5616#post15616 )
_ a working example to revise / refresh and simplified summarise some of my notes above:

Working example to demonstrate and summarise what I was talking about in the last few posts above
This is what you do..
I download the zip file, EileensFldr.zip , which has some folders and sub folders and files to demo the coding on. I also downloaded the file, wbCodes.xlsm , with a lot of macros in it including the various codings being discussed, ( see _1b in next pic) , . - I chose some arbitrary place and made a note of it: F:\Excel0202015Jan2016\ExcelFox\Word\prkhan56 ( see _1 in next pic )
I need to “unzip” the zip file, ( we “zip things” just to make it easier to store and post . “zipping” does some sort of efficient condensing which makes it easier to store and transport big things. They need to then be “unzipped” to get them back in their normal original form )
To unzip I _1) double click in the explorer window on EileensFldr.zip to get inside it _2)
Inside it I have what I want, the folder EileensFldr. In my Computers with window systems from Operating system Vista, ( Vista, Windows 7, Windows 10 ) , the “unzipping” seems to happen automatically when I copy and paste what’s in the zip folder. So I copy it, and paste it, _3)
( for convenience I paste it back in the same folder containing the zip folder. Download and unzip _1 _2 _3 .jpg

Attachment 3619

In the downloaded file, wbCodes.xlsm , I add a new worksheet, and kept that as the active sheet ( active sheet = the one showing, the one you are looking at ).
I go into the VB Editor ( via Alt+F11 , for example ) and look for one of the recursion codings.
I chose Sub VBADoStuffInFoldersInFolderRecursion() 'Main routine to "Call" the first copy of the second routine, VBALoopThroughEachFolderAndItsFile(
Sub VBADoStuffInFoldersInFolderRecursion().JPG

Attachment 3620

I made a few changes , so as to make sure the correct sample files were to be used and that the results appeared on my active sheet

Code:

' Dec 2017 For Python Comparison. Tutorial Post: excelforum: https://www.excelforum.com/tips-and-tutorials/1213798-all-sub-folder-and-file-list-from-vba-recursion-routine-explanation-and-method-comparison.html Tutorial Post: ExcelFox: http://www.excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA?p=10420#post10420
'http://excelpoweruser.blogspot.de/2012/04/looping-through-folders-and-files-in.html http://www.excelforum.com/excel-programming-vba-macros/1126751-get-value-function-loop-through-all-files-in-folder-and-its-subfolders.html#post4316662 http://www.excelfox.com/forum/f5/loop-through-files-in-a-folder-using-vba-1324/
Sub VBADoStuffInFoldersInFolderRecursion() 'Main routine to "Call" the first copy of the second routine, VBALoopThroughEachFolderAndItsFile(
Rem 1A) Some Worksheets and General Variables Info
Dim Ws As Worksheet '_-Dim: Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post4411
' Set Ws = ThisWorkbook.Worksheets.Item(1) 'Worksheets("EFFldr") 'CHANGE TO SUIT YOUR WORKSHEET '_- Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
Set Ws = ActiveSheet
Ws.Range("B3:F30").ClearContents ' This line only needed for demo code
Dim celTL As Range: Set celTL = Ws.Range("B3") 'Top left of where Listing should go
Rem 2A) Get Folder Info
Dim strWB As String ' "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular “Value”, or (“Values” for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116
' Let strWB = ThisWorkbook.Path & "\" & "EileensFldr" ' 'CHANGE TO SUIT if you store the main Folder to be looked through somewhere other than in the same Folder as this workbook in which the codes are in
Let strWB = "F:\Excel0202015Jan2016\ExcelFox\Word\prkhan56\EileensFldr"
Rem 3A ) ' FileSystemObject Object

That’s it!
Run the macro, Sub VBADoStuffInFoldersInFolderRecursion() 'Main routine to "Call" the first copy of the second routine, VBALoopThroughEachFolderAndItsFile( ( make sure you have the new made sheet active )
The results match those given in the previous posts: Example results.jpg

Another example
Just a practical example. .. I am curious to see if some files containing information about my drivers on a computer change when I install a simple HP Printer, ( HP 1050 ). ……._ Continued in next page #2.