Quickly Find All files within a folder and sub folder in Excel VBA - Code Included
HTML-код
- Опубликовано: 15 сен 2024
- Grab the Free VBA Quick Reference Guide
www.chrisjterr...
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
Thanks! I've been looking for this for a long time. Great coding there.
Appreciate it!
Perfectly it would be if cmd may run hidden. Is it possible?
But this solution is just brilliant!
Great thanks, man! 🤗
Thank you.
thanks a lot dear, really very thank full for your kind training............ WELDONE WORK THANKS A LOT FOR THIS BEAUTIFUL SESSION OF FILE SEARCHING
Amazingly fast code! Thanks!! subscribed
Thank you sir
awesome code! Thank you
Thanks!
could you please explain the key code there? I can't figure out how it works. Thank you
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
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.
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
Thanks for your reply, im still not sure what you mean but ill try to work it out.
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?
You may have to use the FSO library. This may help - www.robvanderwoude.com/vbstech_internet_download.php
Thank you
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?
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
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?
Do you have Option Explicit in your code somewhere. I usually don't declare my variable.
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.
Do you have an equivalent for the Mac Terminal?
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?
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
@@EverydayVBAExcelTraining thanks for the quick reply! could you share the code for how to save/store and show the results in that way?
Have you looked to see if it would be possible using Outlook rules
Thanks for the video
I have the Question.
How to hide cmd windows on running in case many file? (Running in the background)
O man that would be great. But I don't know how to do that during the run time if the macro
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
+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.
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.
One drive can be a pain.
Is it synced on your PC or is the file really online in OneDrive
@@EverydayVBAExcelTraining Synced
How can I add more than one file extension to the code? Thanks
You will need to do a loop or duplicate the code.
how would I go about referencing part of the file name from a list in excel
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
Thanks for you
But how i can open all xls files in multi folder to extract data.
@EverydayVBAExcelTraining
is there a way to run this code to list in a second column the date last modified information? Thank You!
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)
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 ?
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?
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”
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.
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.
@@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.
How to use VBA Macro to search folder in any disk or any Hidden files or folder??...!
I have never searched hidden file or folders. Have you checked Stack Overflow
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!
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????
Getting all is obviously more difficult than specifics.
You may need to use FileFSO.
EverydayVBA tha k you for the quick reply
ok i have them all but how do i show only the file NAME rather then its path?
How do i use the fileSCO exactly????
Go to stack overflow and search vba code to check if a file exist. There are some example there
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
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
and if iam put dim sn As Variant its also work but ineed to know the true
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
@@EverydayVBAExcelTraining really thanks so much for ur nice and great teach
Is this VBA Mac compatible?
Yes but not all the functionality is
How can it write the list to a Access table?
Thanks
Does it work also with pdf files?
It should work but there isn't a while lot you can do except know they exist
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.
When I remove letters /a and /s it imports just file names.
Hey.. How can I search in the following folder structure :
Folder1\*\folder2\
You would need to look up the power shell equivalent
You would need to look up the power shell equivalent
You would need to look up the power shell equivalent
Накатил и ролик замутил :)
can you help with a non-english file names?
Sorry but i don't think I could help. Have you checked stackoverflow.com
a
can you remove your face from the video?
I probably should