VBA Code to list Files in Folder
Here is a simple code for you, this function list files in a folder on an Excel sheet. Note that if there are sub-folders available in the folder then this function will not read the files inside the sub-folders.
VBA code: List all file names from a folder into a worksheet
'This function lists files in a folder 'Note: It will not read any files inside a sub-folder Public Sub ListFilesInFolder() 'Variable Declaration Dim strPath As String Dim vFile As Variant Dim iCurRow As Integer 'Clear old data Sheet1.Range("B9:B1000").ClearContents 'Set the path of the folder strPath = Sheet1.Range("B4").Value 'Add slash at the end of the path If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "\" Then strPath = strPath & "\" End If 'Set Directory to folder path ChDir strPath vFile = Dir(strPath & "*.*") 'Change or add formats to get specific file types iCurRow = 9 Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through Sheet1.Cells(iCurRow, 2).Value = vFile vFile = Dir iCurRow = iCurRow + 1 Loop End Sub
List All File Names from A Folder into Worksheet by Using VBA Code
To use this code in your Excel file, follow below steps:
1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from the menu bar
4. Paste the code in the module
5. Now add a shape to the Excel sheet
6. Give a name to the shape like ‘List Files in Folder’
7. Right-click on the shape and select ‘Assign Macro…’
8. Select ‘ListFilesInFolder’ from the list and click on ‘Ok’ button
9. Done, enter a folder path in cell B4 and click on the ‘List Files in Folder’ button
Download Practice File
Excel VBA Course : Beginners to Advanced
We are currently offering our Excel VBA Course at discounted prices. This courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from excelsirji. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.