Subfolder Search

Sounds like the same issues you posted in this thread at MrExcel.

Hi Kenneth, how r u.
yes its almost same problem , is there a simple solution for this.

i Just want that it should return the subfolders result.

Regards

Kreshnr

Then similar to what you did:

Please Login or Register  to view this content.

Hi Kenneth,
Thanx for the solution, but i afraid its not working for me.
i have made folder structure the way i m looking for information to be retrieved.
pls find attachment for more details.

Regards

Kreshnr

I used DOS methods to do this rather than traditional fso methods.

You will need to get my Speedup routines from the site commented in the code or comment SpeedOn and SpeedOff. You will also need to get the sub ExecCmd as commented. Or, copy the Modules from SubFolderParts.xls
as the site had a 503 Service Unavailable error when I tried to attach it.

Note: I did not add a part to check the root folder but that is easy enough. I also added a 3rd column to show what the full path to a found subfolder would be as I guessed that would be your next question.

Here is the code less the two modules:

Please Login or Register  to view this content.

Hi Kenneth,
ur solution worked really well, though the code is very complex for me to understand.

Thanx for ur solution...

Kenneth ur Rock!!

Regards

kreshnr

This probably looks more complicated, but it might help explain my thinking.

The function Subfolder basically just boils down to getting the subfolder names using DOS. You can do it manually at a DOS prompt (Start > Run > cmd > OK). The command is like this at a DOS prompt:
Dir *.xls /ad/s/b > c:\temp\XLS.txt

* is a wilcard. /ad means list folders only, /s means look in subfolder and /b means display just bare information, paths. > means send redirect so it sends the output that would normally go to the console to that file. If type this at a DOS prompt and press Enter key, it will show the command line switches: Dir /?
or: help Dir

To get to a DOS prompt, I passed: Environ$("comspec") & " /c
Which resolves to "cmd /c". This opens a new DOS command window but the /c closes the window when the next part completes. You can type cmd in Win+R to get a DOS prompt.

One thing about using DOS methods is that you need a loop to wait or ExecCmd so that vba will wait until the DOS process is complete.

The last part of the SubFolder function is that it opens the text file in read mode and reads the data into an array. The function then returns the array. After getting the array, we do have to check that Ubound(returnedArrray) <> -1. If it is, then we know that no array was returned from the Subfolder function. In other words, there were not subfolders on the original drive:\path did not exist.

The other parts are error checks. The next part iterates through the array of subfolder names and returns the location or index of subfolder in the array if it matches the subfolder. So, we know the first part and the last part from the Excel data. If we check the left and right ends of each element in the array of subfolder names, the first one found is the index that we want. If we know the index, we now know the subfolder name's location in the array.

So, not all that bad once you know the logic that I used.