Name file list

The benefit of using a U ser D efined F unction UDF is that you can save the function in a personal macro workbook and reuse it easily without repeating the steps again and again.

You can also create an add-in and share this function with others. Now let me first give you the VBA code that will create a function to get the list of all the file names from a folder in Excel.

The above code will create a function GetFileNames that can be used in the worksheets just like regular functions. Note that I entered the folder location in a cell and then used that cell in the GetFileNames formula. You can also hard code the folder address in the formula as shown below:.

In the above formula, we have used ROW -2 and we started from the third row onwards. This made sure that as I copy the formula in the cells below, it will get incremented by 1.

The above formula works great when you want to get a list of all the file names from a folder in Excel. But what if you want to get the names of only the video files, or only the Excel files, or only the file names that contain a specific keyword. Below is the code that will allow you get all the file names with a specific keyword in it or of a specific extension. This function takes two arguments — the folder location and the extension keyword.

It returns an array of file names that match the given extension. If no extension or keyword is specified, it will return all the file names in the specified folder. How about you? Any Excel tricks that you use to make life easy. I would love to learn from you. Share it in the comment section! Awesome, Thanks. Though i guess this works only for local drive folders. Is there a way to get the sever location work? There should be a way to get the files names from the severs connected.

Could you please help me out with that? Super bro! Many thanks for sharing your knowledge. Very well. I like the code of vba. And after this all works. Thanks for great job. Is there a way to select file names based on their created date or last modified date? For example, I want files created in the last 24 hrs, 36 hrs, and 5 days? Hi, this is great, but I need a list of the file names without their extension.. I find it faster to stick the folder path into a browser and then copy and paste into excel.

But yes, even better when excel is set up to extract the data with a click of the button. Hi, what i like to know is…. In this example it would be I need a macro which can automate the work of renaming the pdf with amount within the pdf, instead of depending on a software. For B, the idea is almost same as above except for one revision, let say rev. I only tried the first method and it works perfectly for me… thank you so much for saving me days of boring inputing!!

I used the code above to obtain a list of files. Is there a way to correct for this? Hey, I have a ecxel sheet which have some product names, and also have a folder which have some pdf files named same as in cell data, like if cell A2 value is apple1, Pdf file name is apple1.

I want to list the names and duration of all videos in a folder and its subfolders using Excel VBA. Also I am failing to loop through subfolders. What I want to achieve is for the macro to loop through all subfolders in the the given root folder and list only video names and duration in columns A and B.

Some help with this is truly appreciated. Option Explicit. With fldr. Namespace SourceFldr. For Each sFile In oDir. Items Cells i, 1. Sumit Bansal right? As a matter of fact you are my excel HERO. But upon discovering your skills and experience I think I still have a a long way to go. Thank you very much for this blog. You are doing wonderful work Sumit to educate Excel users. God bless you. It will be very useful for all Excel users and saves tons of time and effort.

Current version is 7. I do not have any pesronel intrest in the product except to make it known to many Excel users to benifit in their work. It was developed by Bastien Mensink from Netherlands way back in I am using it since that time. Feed back on your experience is appreciated. Read from folder, and delete all columns except file name. Save and load to table.

No macro, no formula involved. Since it has not been corrected I assume that it has not been reported yet.

Hi Sumit, Great trick. Thanks a lot. However I was wondering if there is a way to extract the file path as well along with the file name. But I have one more question. Can this be used to get a value from a cell in these docs as well? I get a list of all docs in a given folder, can I then get a value from a cell in each of those docs if its all the same cell in each doc?

Hi Sumit, great tutorial. Thank you, Tom. This thread is locked. You can follow the question or vote as helpful, but you cannot reply to this thread.

I have the same question Report abuse. Details required :. Cancel Submit. You must have the directory you wish to list on your hard drive not a network share although a mapped drive may work?

Files should be displayed in the details format within the directory. Open Excel. I wish more Windows users could use the terminal to solve simple things like this. This is great! You can also just put the line:. You could even have excel read in the external txt file, so you only need to double click the batch file to refresh the data in excel. When you have imported your text into Excel, it's still text. It's in long lines of text you then need to write formulas for, to break it up into useable data.

Then you need to manually remove the superfluous data and blank lines you don't need. This solution is a way of getting a list of files into Excel, but without a huge amount of manual manipulation, it's useless. Great Info! Easy and to the point. Love your work. Thank you for the simple solution! Excellent info!

This saved hours of work! Thank you! Web-browser worked like a charm! Saved me hours of work. Can i auto update the list of files imported in excel? Thanks bro! Tips like these makes the internet win! Keep up the great work! Thank you. It helped me to copy list of files in a folder to bring in Excel.

Thanks u so much , i am trying to find out solution from many days.. Thank you.. It is useful information. We can paste directly into excel and select remove duplicate option to see the list details. Thank you for the macro to list files in a directory. It worked awesome.

I searched everywhere for this and I cannot begin to tell you how complicated they made it; yours is so simple. Thankyou very much sir for your wonderful input. Very good website. Learn a lot from it. I'd used batch files before but that is one neat trick with Excel! Add this line in the loop to generate hyperlink for the files ActiveSheet. Hi, Thanks a lot to share this way. Hiiii any buddy pls solve my problem It will generate a list of files in the directory or subdirectory in worksheet.

See screenshot: bt my excel file is not remove password Thanks for the macro.. It was really very helpful Can you please tell me what is the significance of number 7 with Dir function. Hi there! This tool works for me, is very good. But I have a problem, I have to analyze a folder that has 30 sub folders and also like 60, images.

The system says it doesn't work with more than 10, files. What can I do? I wish you could help me. Best regards.

Did you find a solution for this problem? Hi, Is this necessary that we should have google chrome or mozilla browser? As I have only internet explorer and after executing this code, dialogue box gets opened but after selecting the folder name, it gives an error bad file name or path.

Could you please let me know what might be the issue?