The work was simple. There were many folders on the network drive and each folder had hundreds of files in it. I had to follow these three steps: Sounds simple right? It was – Simple and a huge waste of time. What took me three days could have been done in a few minutes if I knew the right techniques. In this tutorial, I will show you different ways to make this entire process super fast and super easy (with and without VBA).
Using FILES Function to Get a List of File Names from a Folder
Heard of FILES function before? Don’t worry if you haven’t. It is from the childhood days of Excel spreadsheets (a version 4 formula). While this formula does not work in the worksheet cells, it still works in named ranges. We will use this fact to get the list of file names from a specified folder. Now, suppose you have a folder with the name – ‘Test Folder‘ on the desktop, and you want to get a list of file names for all the files in this folder. Here are the steps that will give you the file names from this folder:
Want to Extract Files with a Specific Extension?? If you want to get all the files with a particular extension, just change the asterisk with that file extension. For example, if you want only excel files, you can use xls instead of * So the folder address that you need to use would be C:\Users\Sumit\Desktop\Test Folder*xls* Similarly, for word document files, use doc How does this work? FILES formula retrieves the names of all the files of the specified extension in the specified folder. In the INDEX formula, we have given the file names as the array and we return the 1st, 2nd, 3rd file names and so on using the ROW function. Note that I have used ROW()-2, as we started from the third row onwards. So ROW()-2 would be 1 for the first instance, 2 for the second instance when the row number is 4, and so on and so forth. Watch Video – Get List of File Names from a Folder in Excel
Using VBA Get a List of All the File Names from a Folder
Now, I must say that the above method is a bit complex (with a number of steps). It’s, however, a lot better than doing this manually. But if you’re comfortable with using VBA (or if you’re good at following exact steps that I am going to list below), you can create a custom function (UDF) that can easily get you the names of all the files. The benefit of using a User Defined Function (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). Where to put this code? Follow the steps below to copy this code in the VB Editor.
Go to the Developer tab. Click on the Visual Basic button. This will open the VB Editor. In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key). Double click on the Module object and copy and paste the above code into the module code window.
How to Use this Function? Below are the steps to use this function in a worksheet:
In any cell, enter the folder address of the folder from which you want to list the file names. In the cell where you want the list, enter the following formula (I am entering it in cell A3): =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),"") Copy and paste the formula in the cells below to get a list of all the files.
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. In case you’re entering the formula in the first row of a column, you can simply use ROW(). How does this formula work? The GetFileNames formula returns an array that holds the names of all the files in the folder. The INDEX function is used to list one file name per cell, starting from the first one. IFERROR function is used to return blank instead of the #REF! error which is shown when a formula is copied in a cell but there are no more file names to list.
Using VBA Get a List of All the File Names with a Specific Extension
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. In that case, you can use a slightly different function. Below is the code that will allow you get all the file names with a specific keyword in it (or of a specific extension). The above code will create a function ‘GetFileNamesbyExt‘ that can be used in the worksheets (just like regular functions). 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. Syntax: =GetFileNamesbyExt(“Folder Location”,”Extension”) Where to put this code? Follow the steps below to copy this code in the VB Editor.
Go to the Developer tab. Click on the Visual Basic button. This will open the VB Editor. In the VB Editor, right-click on any of the objects of the workbook you’re working in, go to Insert and click on Module. If you don’t see the Project Explorer, use the keyboard shortcut Control + R (hold the control key and press the ‘R’ key). Double click on the Module object and copy and paste the above code into the module code window.
How to Use this Function? Below are the steps to use this function in a worksheet:
In any cell, enter the folder address of the folder from which you want to list the file names. I have entered this in cell A1. In a cell, enter the extension (or the keyword), for which you want all the file names. I have entered this in cell B1. In the cell where you want the list, enter the following formula (I am entering it in cell A3): =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"") Copy and paste the formula in the cells below to get a list of all the files.
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!
Filter cells with bold font format. How to Combine Multiple Excel Files into One Excel Workbook. Creating a Drop Down Filter to Extract Data Based on Selection. Using VBA FileSystemObject (FSO) in Excel.
=IFERROR(LEFT(INDEX(FileNameList,ROW()-2),FIND(“.”,INDEX(FileNameList,ROW()-2))-1),””) Thank you very much for you effort. thankyou for the post I need a macro which can automate the work of renaming the pdf with amount within the pdf, instead of depending on a software it made our work very easy with our macro For A, the idea is I have a PDF file, let say rev. 1,2,3,4 etc, and I will put it in one folder, what I need is I need to capture the latest revision with hyperlink using formula. For B, the idea is almost same as above except for one revision, let say rev. 01 and I will put it in one folder (same folder as formula A), what I need is I need to capture the exact revision with hyperlink using formula. I get this this formula but i don’t know how it will work- thanks in advance. A) Formula for latest “rev number” column =IF(Bfile(“Z:3 M+ MWC3.1 M+_RSSM+ (CC_2015_3A_022)3_DrawingsUSBM+ WS4_Drawings”&MIDB($A50,11,3)&”PDF”&MIDB($A50,1,35)&”-“&LOOKUP(1,0/($K50:$DF50” “),$K50:$DF50)&”.PDF”),HYPERLINK(“Z:3 M+ MWC3.1 M+_RSSM+ (CC_2015_3A_022)3_DrawingsUSBM+ WS4_Drawings”&MIDB($A50,11,3)&”PDF”&MIDB($A50,1,35)&”-“&LOOKUP(1,0/($K50:$DF50” “),$K50:$DF50)&”.PDF”,LOOKUP(1,0/($K50:$DF50″ “),$K50:$DF50)),””&LOOKUP(1,0/($K50:$DF50” “),$K50:$DF50)) B) Formula for latest “rev number individual” column =IF(Bfile(“Z:3 M+ MWC3.1 M+_RSSM+ (CC_2015_3A_022)3_DrawingsUSBM+ WS4_Drawings”&MIDB($A52,11,3)&”PDF”&MIDB($A52,1,35)&”-00.PDF”),HYPERLINK(“Z:3 M+ MWC3.1 M+_RSSM+ (CC_2015_3A_022)3_DrawingsUSBM+ WS4_Drawings”&MIDB($A52,11,3)&”PDF”&MIDB($A52,1,35)&”-00.PDF”,”00″),”00″) So the INDEX formula (in column B) gives #N/A 🙁 I want to list the names and duration of all videos in a folder and its subfolders using Excel VBA. From the code below I can get the duration of video files, but I can’t exclude all other files. This gives me a list of all the file names, which I don’t need. 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 Dim i As Long, SourceFldr Dim c As Range, rng As Range Dim sFile As Variant Dim oWSHShell As Object Dim WS As Worksheet Dim lRow As Long Sub GetDuration() Dim fldr As FileDialog Set fldr = Application.FileDialog(msoFileDialogFolderPicker) Set oWSHShell = CreateObject(“WScript.Shell”) With fldr .Title = “Select a Source Folder” .AllowMultiSelect = False .InitialFileName = oWSHShell.SpecialFolders(“Desktop”) If .Show -1 Then GoTo NextCode SourceFldr = .SelectedItems(1) NextCode: End With Dim oShell: Set oShell = CreateObject(“Shell.Application”) Dim oDir: Set oDir = oShell.Namespace(SourceFldr) i = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row + 1 For Each sFile In oDir.Items Cells(i, 1).Value = oDir.GetDetailsOf(sFile, 0) ‘File Name Cells(i, 2).Value = oDir.GetDetailsOf(sFile, 27) ‘File Lenght i = i + 1 Next sFile Set oDir = Nothing Set oShell = Nothing End Sub Some time back i was working on its VBA and m non VBA guy………. Much appreciated Question: When the New File Names come in, they Start Over from the Top-Shifting File Names Down, how can I get them to come in at the bottom of the list (based on date/time modified)? Make File Name Hyperlink? If I Select the Column with your Formula, Insert Hyperlink and Add Folder Location, this links them to the folder, but how can I make it open the file directly? Also If you can Help: I have a Folder Filled with Email Messages (.msg Files) that I am keeping a Running List of in Excel and have to manually enter data from each Email such as Name (Email Address before @) + Company (Email Address after @), Date Received etc. – Is there a way to Auto Populate this information into Excel from the .msg File following the Automated File Name you have created here? c:This PCDocumentsHusen Data Files – 2014Word =INDEX(FileNameList,1) #NAME? http://www.extendoffice.com/documents/excel/627-excel-list-files.html =LEFT($B$3;LEN($B$3)-1)&IFERROR(INDEX(FileList0916;ROWS($B$4:B33));””) https://www.dropbox.com/s/ex6rtxpgr2twyne/Excel%20Index.xlsx?dl=0 https://www.dropbox.com/s/ebd0bbh1exwkw8y/Excel%20Index.xlsx?dl=0 It shouldn’t look something as shown in your spreadsheet. Also, make sure the excel file (in which you are extracting the file names) is saved in the same folder. i think this formula should also work for retrieving the file path, looks shorter 🙂 =LEFT(CELL(“filename”),FIND(“[“,CELL(“filename”))-1)&”” It can get filenames, folders, file extensions and other information regarding files. This tool can write up to excel limit number of rows in just a few minutes. In a stress test I did, I got more than 1,000,000 file names in just about 3 minutes. Here is a link to try: http://excel.gegprifti.com Before dragging down, we may use =COUNTA(FileNameList) to get a sense of how far we need to go down. btw, another approach in getting the directory for consideration. =REPLACE(CELL(“filename”),FIND(“[“,CELL(“filename”)),LEN(CELL(“filename”)),”*”) thnx a lot for such a great trick. Is “Files” function is valid only Excel 2013. I am trying to make it in Excel 2010 but could not find the Turkish of “Files” in Excel? Do you have an idea? I am dying here to try above trick but still waiting your reply about the function FILES ? 🙁 Thnx for your reply. I made it but unfortunately when I drag down, it gives only the first file name ? Do you have any idea? I changed all names, but stll same… Here it is: https://www.dropbox.com/sh/inwriaq0ttocwzu/AACSB1-ox5z6Z5rm2MaxsYpva?dl=0 In VBA I write and run simple macro like: Sub Makro1() ActiveWorkbook.Names.Add Name:=”Test”, RefersToR1C1:=”=FILES(Sheet1!R2C1)” End Sub Then in Name Menager I have 4.0 Macro Function name in my Excel language (for my it’s Polish)
