Quickly Find All files within a folder and sub folder in Excel VBA

Quickly Find All files within a folder and sub folder in Excel VBA - Code Included

Рет қаралды 34,871

8 жыл бұрын

Grab the Free VBA Quick Reference Guide
www.chrisjterrell.com/excel-v...
In this video we use a nice little piece of code that will find all of the files within a folder and all of its subfolders.
A Subscriber requested this video as he was reminiscing of ages gone by when Excel 2003 had a file search function
NOTE: Since this is pulling all the folder and subfolders it could take a lot of time and return a bunch of files
CODE:
==============
Sub Find_Files()

Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
fldr.Show
f = fldr.SelectedItems(1)
f = f & "\"

ibox = InputBox("File Must Contain (Note * wildcards can be used)", , "*.xls*")
On Error GoTo ext

sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & f & ibox & """ /s /a /b").stdout.readall, vbCrLf)

Sheets(1).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
ext:
End Sub

Пікірлер: 87

@markarvin7725 6 жыл бұрын

Thanks! I've been looking for this for a long time. Great coding there.

@EverydayVBAExcelTraining 6 жыл бұрын

Appreciate it!

@POMI_Singing_Guru 7 ай бұрын

thanks a lot dear, really very thank full for your kind training............ WELDONE WORK THANKS A LOT FOR THIS BEAUTIFUL SESSION OF FILE SEARCHING

@ismaelmunoz3047 2 жыл бұрын

Amazingly fast code! Thanks!! subscribed

@EverydayVBAExcelTraining 2 жыл бұрын

Thank you sir

@Rice0987 Жыл бұрын

Perfectly it would be if cmd may run hidden. Is it possible? But this solution is just brilliant! Great thanks, man! 🤗

@rodrigomelo5762 3 жыл бұрын

Thank you.

@ba177ba18 5 жыл бұрын

awesome code! Thank you

@EverydayVBAExcelTraining 5 жыл бұрын

Thanks!

@wuguerrero 4 жыл бұрын

Hi, do you know how can i extract paths that contain tildes? or how can i treat them after i extract the paths? Im using a code after finding all that paths that will open all the files one by one and extract data from each one of them. Please let me know.

@amitbodhwani9119 7 жыл бұрын

Hi, I have a button called UpLoad on my webpage which ask to upload the .csv file. I am able to click that button using VBA code and after clicking that button on webpage a popup appears similar to open dialog box which ask to upload a file for example say "Star.csv" but i want to insert the file name called "Star.csv" which is stored in my local directory automatically using VBA without user interaction. Any help will be appreciated . Thanks!

@tommydieguez 5 жыл бұрын

How can it write the list to a Access table? Thanks

@popobolin 5 жыл бұрын

could you please explain the key code there? I can't figure out how it works. Thank you

@LeeTownsend-rx6sm 2 ай бұрын

Do you have an equivalent for the Mac Terminal?

@SuperHanme 5 жыл бұрын

Thank you

@johnguo8283 2 жыл бұрын

Great Coding reading comments , but I can't get it to work.. I have inserted code in module and hit run. I get he pop up boxes but nothing displays in Sheet 1 Column 1.. Can u please help

@YesltsMeAgain 4 жыл бұрын

Hi there, thanks very much for the informative video, this code is great! I just wondered, is there any way for the macro to take the two variables (the folder path and the search query) from predefined cells on the spreadsheet rather than manually inputting each time? This may be a very simple query but i am just getting started with this kind of thing. Thank you.

@EverydayVBAExcelTraining 4 жыл бұрын

Yes this is possible you would have to loop through the sheet and then concatenate a couple strings and pass that through. Does that make senss

@YesltsMeAgain 4 жыл бұрын

Thanks for your reply, im still not sure what you mean but ill try to work it out.

@WKatekaew 6 жыл бұрын

Thanks for the video I have the Question. How to hide cmd windows on running in case many file? (Running in the background)

@EverydayVBAExcelTraining 6 жыл бұрын

O man that would be great. But I don't know how to do that during the run time if the macro

@flcommentator 7 жыл бұрын

Thanks for the video. This is pretty close to what I am trying to accomplish. My dilema is that the files are contained on a web server. I want to list al .dat files within the directory. How can this be changed to search a web server location, instead of a network drive?

@EverydayVBAExcelTraining 7 жыл бұрын

You may have to use the FSO library. This may help - www.robvanderwoude.com/vbstech_internet_download.php

@manufacturersalesrepresent424 7 жыл бұрын

great code. How to I modify it so it searches for a name in a column and puts the path to the file in the next column

@EverydayVBAExcelTraining 7 жыл бұрын

+dlandstrom you may be able to use a vlookup... If that doesn't work. Try a loop with an if statement. I have some videos on that.

@user-ht2mw4ko6f 7 ай бұрын

is there a way to run this code to list in a second column the date last modified information? Thank You!

@EverydayVBAExcelTraining 7 ай бұрын

The easiest thing to do is to insert a column after the list is created. You could also try cells(1,2) instead of cells(1)

@leehonghui 5 жыл бұрын

hi. this is a really awesome code. how do i modify this code to open every text file and search for a particular word or number that the user has entered. the output will be on a sheet before copying to a notepad and deleting the sheet or clearing it. i have been asking for help everywhere but no definite answer.

@EverydayVBAExcelTraining 5 жыл бұрын

Thanks, I would start by opening the file put the text in a variable and use split to add each word in an array then loop through the array to find the word or number Hope that makes sense.

@leehonghui 5 жыл бұрын

@@EverydayVBAExcelTraining on second thought i think using the file name would be good enough. so according to the video, i see that it will pull all of the files in that folder with the file type as xls. i need it to be in txt and it will only show the files which the names that corresponds to the inputbox that the user has entered.

@thomassutton4670 7 жыл бұрын

This code is beautiful and very handy. I've been trying to alter it to make it so that rather than looking for just file extensions it can use approximate values to search. For example, say I want to find every file that contains the word "Bananas" it its file name. How would I go about that?

@EverydayVBAExcelTraining 7 жыл бұрын

Good Question... If you wanted to find Bananas you would enter *Bananas* the asterisk is a wild card so it will accept any text before "Bananas" and any text after Bananas

@tymussh 4 жыл бұрын

If I run this code AS IS, it is asking to "dimension" f, ibox & sn. Why these three variables are not defined here but still it works?

@EverydayVBAExcelTraining 4 жыл бұрын

Do you have Option Explicit in your code somewhere. I usually don't declare my variable.

@richie144248 3 жыл бұрын

how would I go about referencing part of the file name from a list in excel

@EverydayVBAExcelTraining 3 жыл бұрын

Hi Joshua, You would need to parse the string, most likely using the mid function. You could also also use split but that might be complicated

@DJPejsen 3 жыл бұрын

It’s it possible to write the file paths to an Excel table (listobject) and can I also get the file names and date modified ?

@EverydayVBAExcelTraining 3 жыл бұрын

Are you wanting all the tables in a workbook. Or are you wanting to open files and get all the tables within each workbook. Do you k ow how to open a workbook from a file path?

@DJPejsen 3 жыл бұрын

I have a folder with multi sub folders and a bunch of files.The Excel files I want to find all starts with file name “ACTIVE*” (100+ files) I would like to store the files in an Excel table (ListObject) in listColumn(2) If file paths can be stored in Table(1).ListColumn(2) then I would create a FSOobject macro that can get the file properties such as file name and last modified date in Table(1).ListColumn(3) and Table(1).ListColumn(4). Similar to what you do in video “VBA Loop Through All Files in a Folder”

@AliNBarakat 6 ай бұрын

Thanks for you But how i can open all xls files in multi folder to extract data.

@AliNBarakat 6 ай бұрын

@EverydayVBAExcelTraining

@niccolebortolussi4859 2 жыл бұрын

can someone tell me how this is able to capture the local path of the folder selected? I am wondering if i could modify it to not use the picker...just whatever directory the file is in. All the other solutions for returning the local path of a onedrive location seem long and complicated. this seems to return local path with not much to it.

@EverydayVBAExcelTraining 2 жыл бұрын

One drive can be a pain. Is it synced on your PC or is the file really online in OneDrive

@niccolebortolussi4859 2 жыл бұрын

@@EverydayVBAExcelTraining Synced

@boeykensa 5 жыл бұрын

Thanks for the code. I want to use it from office outlook. How in the vba code is the list of search results stored? I mean , what is the next step if I want to show all of the result files, but not in excel (directly in the filepath?) How can i show the result of sn in vba?

@EverydayVBAExcelTraining 5 жыл бұрын

Outlook VBA is different, and you would need to save the list in an array or a text file. It depends on what you are doing

@boeykensa 5 жыл бұрын

@@EverydayVBAExcelTraining thanks for the quick reply! could you share the code for how to save/store and show the results in that way?

@EverydayVBAExcelTraining 5 жыл бұрын

Have you looked to see if it would be possible using Outlook rules

@jehanrana3508 3 жыл бұрын

How can I add more than one file extension to the code? Thanks

@EverydayVBAExcelTraining 3 жыл бұрын

You will need to do a loop or duplicate the code.

@deepakvimal7798 4 жыл бұрын

How to use VBA Macro to search folder in any disk or any Hidden files or folder??...!

@EverydayVBAExcelTraining 4 жыл бұрын

I have never searched hidden file or folders. Have you checked Stack Overflow

@poweroff9593 4 жыл бұрын

thanks, sir if i want put these variable in ur code (sn ,f,ibox) in : dim sn as ?? dim f as ?? dim ibox as ?? please help me to declare them in code

@poweroff9593 4 жыл бұрын

Sub Find_Files() Dim fldr As FileDialog Dim f, ibox As String, sn, Resize As Long Set fldr = Application.FileDialog(msoFileDialogFolderPicker) fldr.Show f = fldr.SelectedItems(1) f = f & "\" ibox = InputBox("File Must Contain (Note * wildcards can be used)", , "*.xls*") On Error GoTo ext sn = Split(CreateObject("wscript.shell").exec("cmd /c Dir """ & f & ibox & """ /s /a /b").stdout.readall, vbCrLf) Sheets(1).Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn) ext: End Sub its true iam try and its work

@poweroff9593 4 жыл бұрын

and if iam put dim sn As Variant its also work but ineed to know the true

@EverydayVBAExcelTraining 4 жыл бұрын

Variant should work for numbers and strings. And is the most dynamic and works well If you know you need a string Dim as string If it is a number and you are going to use math you will want to dim as integer or double. For the most part in this code you will be using strings

@poweroff9593 4 жыл бұрын

@@EverydayVBAExcelTraining really thanks so much for ur nice and great teach

@chobblegobbler6671 5 жыл бұрын

Hey.. How can I search in the following folder structure : Folder1\*\folder2\

@EverydayVBAExcelTraining 5 жыл бұрын

You would need to look up the power shell equivalent

@EverydayVBAExcelTraining 5 жыл бұрын

You would need to look up the power shell equivalent

@EverydayVBAExcelTraining 5 жыл бұрын

You would need to look up the power shell equivalent

@RTJ3DCosplay 6 жыл бұрын

WHAT A FANTASTIC CODE!!! I need something like this to log all my PDO AND STL FILES IS THERE ANYWAY I CAN DO THIS but only have the file names NOT the path too them????

@EverydayVBAExcelTraining 6 жыл бұрын

Getting all is obviously more difficult than specifics. You may need to use FileFSO.

@RTJ3DCosplay 6 жыл бұрын

EverydayVBA tha k you for the quick reply

@RTJ3DCosplay 6 жыл бұрын

ok i have them all but how do i show only the file NAME rather then its path?

@RTJ3DCosplay 6 жыл бұрын

How do i use the fileSCO exactly????

@EverydayVBAExcelTraining 6 жыл бұрын

Go to stack overflow and search vba code to check if a file exist. There are some example there

@keshavgokhaledivine 5 жыл бұрын

Does it work also with pdf files?

@EverydayVBAExcelTraining 5 жыл бұрын

It should work but there isn't a while lot you can do except know they exist

@bondniko 3 жыл бұрын

Накатил и ролик замутил :)

@matthewanacleto7885 2 жыл бұрын

Is this VBA Mac compatible?

@EverydayVBAExcelTraining 2 жыл бұрын

Yes but not all the functionality is

@leknyzma 5 жыл бұрын

can you help with a non-english file names?

@EverydayVBAExcelTraining 5 жыл бұрын

Sorry but i don't think I could help. Have you checked stackoverflow.com

@MrSuchos 4 жыл бұрын

Thanks, this is great. Im only looking at the way how can import these data in order from newest to oldest rather than alphabetically. I'm having almost 30000 files... thanks for the help.

@MrSuchos 4 жыл бұрын

When I remove letters /a and /s it imports just file names.

@johnguo8283 2 жыл бұрын