Search for file names in main folder and its sub folders

Hello everyone
I created main folder named "Main Folder" and inside that main folder I created sub folders
Next to the main folder there is the workbook which I desire to be the Search Workbook for file names in the main folder and the sub folders
Using input box for search..

For example : I would type "Excel" in the input box and the expected output would be the sub folder name in column A and the name of the file in column B ...
No case sensitive (Excel - excel - EXCEL ...) all instances should be included in the search process ..
Another point the word may be implemented (I mean may be for example the word Excel existed withing another word (ExcelForum) ) this is also to be included in the search process
I attached expected results for the word "Excel" to illustrate the issue
Thanks advanced

Any help with this topic please?

Hello YasserKhalil,

In the attached workbook, I have added a button on "Sheet1" to call the macro "ListFilesTest". This will match the file names either partially or fully without regard to case.

Please Login or Register  to view this content.

Macro Called by the r Command Button

Please Login or Register  to view this content.

Mr. Leith Ross
Really really amazing & fascinating code
I'm very admired of this wonderful code
Thanks a lot .. Thank you very very much
I appreciate your great help

Sorry for disturbing you again
If possible I need to create hyperlinks to the results .. as in fact I have thousands of files and it would be time saving if I had hyperlinks to the search results
Thanks advanced

Hello YasserKhalil,

Do you want the output to appear same same but have it be a hyperlink to the folder and a hyperlink to the file?

Thanks for reply
I need to create hyperlink to the file (In column B) .. as it would be very useful to click the file name in column B and the code navigate directly to the file with one click

Hello YasserKhalil,

This version of the code and the workbook add a hyperlink to the files.

Macro to List Files and Add Hyperlinks

Please Login or Register  to view this content.

Macro called by the Button

Please Login or Register  to view this content.

Great and wonderful
That's fascinating code
Just last point ... I need to disable the alert related to opening the file ..
I tried to use application.displayalerts=false but didn't work for me

Hello YasserKhalil,

If the alert is about the workbook containing macros, it is part of the security settings and can not be overridden.

It's a Microsoft Office message in title
'Opening .......(full file path) ...
Some files can contain viruses or otherwise be harmful to your computer
It's important to be certain that this file is from a trustworthy source
Would you like to open this file?'

and there is two buttons "Ok" and "Cancel"..
How can I get rid of this alert message?It would be disturbing for me

Hello YasserKhalil,

You will need to add the folder path to "Trusted Locations" in your workbook. You can find this under File > Options > Trust Center > Trust Center Settings... > Trusted Locations

I followed the steps and I still got the alert message ..even if I checked the option of "Trust subfolders"

Hello YasserKhalil,

It looks like Office's security does not allow this message to be suppressed for hyperlinks.

Thanks a lot Mr. Leith
I tried to search to solve this problem and I found some solutions which rely on the registry edit ..I tried a lot but all didnot succeed to bypass this alert
Hope to find solution to this problem
Thank you very much for great help in this topic

May I post a new thread for my problem with hyperlinks?
This is time-consuming because of this alert message