Get Value Function Loop through all files in folder and its subfolders

Hi Cheeseburger,

I find some code at:
http://excelpoweruser.blogspot.com/2...-files-in.html

See if that does what you need.

Check the below link to know how to read all the file in the folder and subfolder. Also, Modify the code to suit your requirement.

http://stackoverflow.com/questions/2...ders-using-vba

Hi Cheeseburger,
The Link given by MarvinP is an adoption of a very common way to Loop through a Folder and all its Subfolders. It basically uses the “SubFolders collection get at all Sub Folders in a recursion process. using Microsoft Scripting Runtime File System Object ” Way.
I have put some explaining ‘Comments in a version for you.
It is a nice demo.

In the original adaption the codes simply lists for you all Sub Folders within an initial Folder. The Initial Folder you can Pick when the code runs as it initially brings up a dialogue box (msoFileDialogFolderPicker). This is done in the First Main Procedure. As written in the Link the Sub then calls the Function which lists out all your Sub Folders within that Initial Folder, and all Sub Folders within those Sub folders, and all Sub Folders within Those Folders, and all Sub Folders within Those Folders, and all Sub Folders within Those Folders... I think you get the point!

There is also in that Link a second Function shown . This does the same but just has an extra bit in the Function to simply loop through and display out the name of each File. So it is Looping through all files in a Folder and its Subfolders. This is what you basically want to do!!

I have Just Called that Function (Function LoopThroughEachFolderAndItsFile ) in my Main Procedure (Sub GetFolderStructure AlanWonk () ), and I just use that and have modified it all a bit to maybe make it all a bit clearer

The code will usually run on the ActiveSheet, listing out all Folders in column A and B and all Files alongside the relavent Folder in Column C. So make sure you have a spare sheet Active when you run the code.

I would recommend you run the code in F8 to see what is going on. The only tricky thing to understand is line 40 in Function LoopThroughEachFolderAndItsFile.
This is an example of recursion. It is actually very simple once you understand it. But it is just incredibly difficult to put in words.
It is basically a Procedure that keeps calling itself as much as necessary as it goes "along", "down", or "to the right" of the Path "roots". Every time it goes off calling itself VBA runs a copy of that Procedure. It “Stacks” all info carefully for each “Copy” Run, in order and continues to do this "drilling" down as far as it must, in this case finding the Next Folder, and then the next Folder in that, then the next Folder in that, then the next Folder in that...I think you get the point! After “drilling down” it comes back up, in reverse order from where it currently is, carefully selecting copies of any variables taken from the appropriate stack. It may then go back down again a bit if it hits a folder “along” that has Sub Folders in it.

The code is a very nice demo actually. If you pick your sample Folders and sub Folders carefully, then the Pasted out results show nicely what is going on in the recursion process, and the Finally output has nice resemblance to a typical sketch of a Files Exploring Diagram.

You really must take the time to go through the demo codes in F8 Debug Mode, and you will soon get the point and wonder why there is any mystery to recursion as it is simple once you follow carefully through a specific example,

Your requirement comes up a lot and has been done for specific examples a lot at this Forum. You do not always catch them in a search as the title may not reflect too well the requirement. This is just one I know because i did it..
http://www.excelforum.com/excel-prog...ubfolders.html
Some where there in all the codes is an identical demo, just using Message boxes to tell you the Folder and file names
Main Procedure ___ Sub AAARRRGH()
Called Function ___ Sub DoOneFolderDemo
Further in that thread a specific example is done both involving getting stuff from the files in two File versions one which opens them files, and one which Gets the same without opening the Files – the Latter example is basically what you are doing with your GetValue Function.

At the end of the day, the adaption to any requirement is quite simple. In my codes here for example, in brief, you would replace my line 34 with your original code that does stuff to each found file. ( I give a bit more detail to that below *** )

The link given to you from “ Sixthsense ” is more of the same..Just in that Link no “demo” is done in the code, that is to say File names are not listed out, no message boxes come up etc...
It simply has a space with a comment (' Operate on each file ) saying where you would put your “doing stuff code” ( Equivalent of replacing my line 34 with your stuff )
Also in that code you are not asked for your main folder so you have to change appropriately the line
HostFolder = "C:\"

( there is also a a non-recursive method there. I have not tried that yet )




My Codes with some explanations I put here for you:
http://www.excelforum.com/developmen...ml#post4316795


*** Coming back to your requirement. You need to do approximately the following.

( p in your code is your initial main Folder, which my code asks you for. ) So you simply select that in my code and do not need to do a p anywhere - Or rather, your p is my ObjFolder.path
And your f is something like my Right(objFldLoop.path, Len(objFldLoop.path) - InStrRev(objFldLoop.path, "\")) - this latter returns the File Name including the bit after the . Dot but without all the Full Path stuff before it

You need a quick check before you do stuff to check you have a good Extension like you want.
( I made a quick start for you just after Line 34. )

Then you add the code lines to do your Get value stuff

That's it!.....

I think that should make an adaption to your code easy. It is difficult for us to do it all for you as we would have to make up a whole lot of Files to check.

I think if initially you run my code before you modify you will get a nice neat listing of all your files.
If you then get a successful adaption of your code, it would be nice if you could paste that code to us and the initial File listing. That would be a nice working example of that way of getting stuff from a file without opening it. I did that a few times in Threads and i had a feeling “not a lot of people knew about that sort of thing”

Alan

P.s.
Here are Your Codes from your uploaded text File: ( you are basically using the Dir way, which is, I think, not so easy to use as the Microsoft Scripting Runtime File System Object way when needing the extra bit of looking at Sub Folders )

Please Login or Register  to view this content.

Hi Alan and thanks for the rep....

There may be a much easier way to do this problem. Newer versions of Excel have Power Query built in. Using the 2016 Excel Data Tab -> New Query -> From File -> From Folder: you can click on a root folder. In the Query Editor dialog then shows all folder and subfolder names. See:
http://excelunplugged.com/2015/02/10...n-power-query/ or
http://dutchdatadude.com/combining-e...ery-for-excel/ for examples.

I'm still learning how to use Power Query and many of its features, but I think Microsoft is ahead of us with a tool to solve these types of problems.

I'll keep studying my book called "M is for (DATA) Monkey" by Puls & Escobar to see if I really can use Power Query to solve this one..

Hi MarvinP

Originally Posted by MarvinP

......
There may be a much easier way to do this problem. Newer versions of Excel have Power Query built in. ....... see if I really can use Power Query to solve this one..

Interesting, it never ends the possibilities with Excel and in particular VBA. Different solutions to the same problem make for the best learning i find. I suppose a Dir solution along the same recurring Function idea should work also. I used the Shells Browse Folder for selecting Folders in conjunction with a Dir for the selection of the files within just yesterday for the first time in answering a Thread to call up a “box” to select files..
http://www.mrexcel.com/forum/excel-q...xcel-file.html

I like to have a lot of independent Solutions, just so I hopefully have at least one that works when I want to do something “for real”
I have not got the slightest idea about Power Query unfortunately, maybe a bit much for me just now.
I suppose if you get that one working, then a macro recording of doing it may reveal another VBA alternative..... and give a bit of insight there, and maybe lend itself to a mod to work in earlier versions.... ( I have only up to XL 2010 though, - no idea if that does Power query, sounds from what you said that it does not )

All good learning stuff..
I wish I had used that first code before, when learning recursion – Gives a nice output and nice to step through with F8 and bounding up that “Explorer” type Output. I tried that manually with colours.. did not look as clear though
http://www.excelforum.com/tips-and-t...ml#post4221356
http://www.excelforum.com/developmen...ml#post4221316


Alan

Hi,
As this recent Thread I was in is concerned with and has a title of the form Looping through all files in folder and its subfolders, I thought it would not do any harm to add another contribution here,.

The main reason being I almost always see Threads of this type done with the “recursion way”, just as done here already. Sometimes that can be a bit tricky to understand

In another Thread I was in, for the first time I tried out a “Queue way” that I had never seen before.
http://www.eileenslounge.com/viewtop...176283#p175024

So I am mainly just adding another solution as well as discussing and comparing the two solutions.

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 Get Values from 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.

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. This was done already by me in Post #5, where I did an explained adaption of the Code in the Link given by Marvin in Post #2.
But I have taken it one step further for both codes, so that you see the various folder levels to the right, as you see sometimes in an explorer type window.

So for anyone requiring a “ doing stuff “ code, you could first run either of the codes I give, which asks you to select your main Folder. The listing it then gives in a Spreadsheet 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 paste out the information, the code lines necessary to do stuff to each file and / or Folder.
As the codes work a bit differently ( which I will try to explain a bit ) the outputs look a bit different
_..........................................
So I will present two codes, a ( new to me ) “Queue way” and again one 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/excel-q...ml#post4417971
( The one using VBA Application.FileDialog(msoFileDialogFolderPicker) 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 the Queue Code the Queue thing is also declared and 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.

The Classic recursion Type Code.

The Routine at Rem 5A)
Sub LoopThroughEachFolderAndItsFile(
is called initially from the
Sub DoStuffInFoldersInFolderRecursion()
Code. It passes to it this first time the Main Folder., EFldr1_1
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 originall 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 the calling code in a variable whose value is taken in at its value, as a value, in the Signature line
Within the Routine this value in CopyNumberFroNxtLvl is given to
CopyNumber.
But Only
If CopyNumber = 0
That is to say If
CopyNumber
Had never been given a number ( 0 is the Empty state of a Long Variable ) so it will have this after Declaration initially )

When the function calls itself to takes in by value at the valueof 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 )

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

The Queue Type from Rem 5Q)

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 a second count variable
CurrentLvlCnt
Which is originally set to the last level count ( set initially to ! 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 Count 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.
https://app.box.com/s/9e6xnb65fijjhl7bk0q6gzzriihkzibw

In words I have a main ( Effectively the ( Only ) Level 1 Folder )
EFldr1_1

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.

Output Given recursion code: You will see it goes “back and Forth” between levels
http://www.excelforum.com/developmen...ml#post4332758
_......

Output Given from Queue Code: You will see it does a Folder level at a time, as this is how they are effectively stacked in the Queue
http://www.excelforum.com/developmen...ml#post4332764
_...........................

Codes:

Recursion Code
Calling code
Sub DoStuffInFoldersInFolderRecursion()
And called Routine
Sub LoopThroughEachFolderAndItsFile(

http://www.excelforum.com/developmen...ml#post4332773
_...........................

Queue Code
Sub ReplaceInAllSubFoldersQing()

http://www.excelforum.com/developmen...ml#post4332790


Thanks for watching
Alan

_.........................................................
Ref
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

Hi,
Just a quick edit as i noticed in passing when using my code in another Thread that there was a typo in the original ...Recursion Code......
_...
Recursion Code
Calling code
Sub DoStuffInFoldersInFolderRecursion()
And called Routine
Sub LoopThroughEachFolderAndItsFile(

This would only effect the Late Binding version working properly as some things need to be dimensioned as Objects rather than being able to be also declared as Folders and Files as in the Early Binding case..
It is a bit late and complicated to ask a Mod to Edit it, so here is that code again, corrected.....

Please Login or Register  to view this content.

_...
Just tidying up my "bad doings"

Alan

P.s.
happy Easter