List files with some specific text from the folders that also contains specific text

Hi all,

Inside the path C:\Users\user\Documents\Test Searcher I have a lot of folders that have the material and the customer name as a part of the folder string name:

3154- Shoes Puma
3155- Shirt Puma
3156- Shoes Adidas
3157- Belt Nike

etc...

Inside these folders then there are still more subfolders with a lot of files. Also note that there are More than 3000 folders....

My goal was to achieve a Macro to list all the files that the name of the file contains the specific text I would put in A2 through all the subfolders from the main folders that contains part of the text I would put in A1.

Ex: From all the folders inside the path C:\Users\user\Documents\Test Searcher that for example contains the words "Shoes" to then search inside all the subfolders of that specific folders all the files containing the file name text "template"

So, in A2 would appear part of the file name "Template" and in A1 part of the folder name "Shoes".

Because I'm quite a begginer, the closest code I could achieve is the one created by Akatrouble based on this thread https://www.excelforum.com/excel-pro...ple-paths.html I edited the code to achieve the following:

Based on the folder (and all the subfolders) from the path I put in A1 to look for all the files that contains in the file name the text I put in A2 and make a list to be pasted in the sheet named "Test Searcher"

Please Login or Register  to view this content.

Although it is not exactly what I want, I still could work with it. The code works good, but I'm the dealing with a small issue that I certainly don't know how to solve. For some reason the part from this code is case sensitive:

Please Login or Register  to view this content.

meaning if I put in range A2 the text "plate" it will list all the files that have the name "Template" for example. But if in A2 I put "PLATE" or "TEMPLATE" it will not list any file if the file name is written as "Template". I've tried to use Lcase function, but because I have some different text possibilites it fails to work in some cases. So my will would be to list all the files without taking into account any capitalization, therefore as a total case insensitive.

Is there any way to achieve that? Or I should create a completly new code?

Thanks in advance for your kind attention!

Hi TMS, thanks for your fast response.

As I mentioned, I already tried the function Lcase but the outcome is not the desired one. It really needs to be completely case insensitive!

This is my old code.
Wild card serach.

Enter
A1:
*Shoes*

A2:
*Template*.xlsx
or just without file extension like "*Template* for all file types.

When file dialog appears, select main folder.

Please Login or Register  to view this content.

@jindon

Thank you for your fast reply, I don't know if you realized that I just edited my message few minutes ago, so I'm not sure if you took into consideration the previous message...

Anyway, I have just tried your modifcation it and I keep having the same issue, the macro works properly when I select the path deep in the subfolder that I know that I have the files, but it doesn't work If I choose the main folder that should apply the condition in A1.

Attachment 871491

and inside the 3154, etc... folders there are subfolders such as Commercial, Performance, etc... and then inside you can find the files that contains the condition in A2

Have you tried the attached workbook named GetFiles.xlsm?

OOps, so sorry, uploaded a wrong file.

#9 has been edited, so try again....

Hi @jindon,

Sorry for my late reply, real life is very demanding sometimes :D

I tried your Excel but the issue is that doesn't go deep enough to more subfolders. In the zip you sent me, try to add more subfolders inside the subfolders and then you will see that the macro fails to work unless you will select the path of the previous folder which contains the file you are looking for...

@jindon,

With the code I already shared at my first post, is it possible to edited and put there the condition to search into the first folder that contains some specific text? Just saying if it would be faster to find a solution... In the meantime I'm trying by myself if I'm able to achieve it...

Are you sure?

It is picking up all the files.

If the attached doesn't work for you, I will leave this to the others.