How to Read All Files in Directory in Vba
How to list all files in folder and subfolders into a worksheet?
Accept yous e'er tried to listing all filenames from a folder into a worksheet including the files located within its subfolders? In fact, there is no directly way for usa to list the filenames from a folder and its subfolder in Excel, however, today, I will introduce some quick tricks to solve this job.
List all filenames in folder and subfolder with VBA code
List all filenames in folder and subfolder quickly and easily with Kutools for Excel
Commonly, Excel has no build in characteristic to bargain with this task, just, yous can apply the following VBA code to complete this trouble.
1. Activate a new worksheet which will list the filenames.
2. Hold downwardly the ALT + F11 keys in Excel, and it opens the Microsoft Visual Bones for Applications window.
3. Click Insert > Module, and paste the following lawmaking in the Module Window.
VBA code: Listing all filenames in folder and subfolder
Sub MainList() 'Updateby Extendoffice Set folder = Application.FileDialog(msoFileDialogFolderPicker) If folder.Prove <> -1 Then Exit Sub xDir = folder.SelectedItems(i) Call ListFilesInFolder(xDir, Truthful) Stop Sub Sub ListFilesInFolder(ByVal xFolderName Every bit String, ByVal xIsSubfolders As Boolean) Dim xFileSystemObject As Object Dim xFolder As Object Dim xSubFolder As Object Dim xFile Every bit Object Dim rowIndex As Long Set xFileSystemObject = CreateObject("Scripting.FileSystemObject") Set xFolder = xFileSystemObject.GetFolder(xFolderName) rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + one For Each xFile In xFolder.Files Awarding.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name rowIndex = rowIndex + 1 Side by side xFile If xIsSubfolders Then For Each xSubFolder In xFolder.SubFolders ListFilesInFolder xSubFolder.Path, True Side by side xSubFolder Finish If Set xFile = Nothing Set xFolder = Nothing Gear up xFileSystemObject = Naught Terminate Sub Function GetFileOwner(ByVal xPath As String, ByVal xName As String) Dim xFolder As Object Dim xFolderItem Equally Object Dim xShell Equally Object xName = StrConv(xName, vbUnicode) xPath = StrConv(xPath, vbUnicode) Set xShell = CreateObject("Shell.Awarding") Set up xFolder = xShell.Namespace(StrConv(xPath, vbFromUnicode)) If Not xFolder Is Nothing Then Set xFolderItem = xFolder.ParseName(StrConv(xName, vbFromUnicode)) Stop If If Not xFolderItem Is Nothing Then GetFileOwner = xFolder.GetDetailsOf(xFolderItem, viii) Else GetFileOwner = "" End If Set xShell = Nothing Gear up xFolder = Cypher Prepare xFolderItem = Naught Stop Role 4. After pasting the code into the Module, printing F5 key to run this code, and a Macros dialog box pops up, select the MainList macro name, and and so click Run push, see screenshot:
5. And in the Browse window, delight select the binder that you want to listing all filenames including the subfolders, see screenshot:
6. Later on specifying the folder, and then click OK push button, and all the filenames in the folder and its subfolders accept been listed into current worksheet from cell A2, run into screenshots:
With above code, you can just list the filenames, sometimes, you need to list other attributes, such as file size, file type, created time, containing folder and so. Kutools for Excel contains a useful office – Filename List, with this characteristic, you lot can rapidly listing all or specific types of filenames in a binder and its subfolders.
Later on installing Kutools for Excel, please do with following steps:
i. Click Enterprise > Import & Consign > Filename List, see screenshot:
2. In the Filename List dialog box, do the following operations:
A: Click 
button to choose the folder that you lot want to list the filenames;
B: Specify the file type that yous want to list from the Files blazon section;
C: Select one file size unit y'all want to brandish from the File size unit department.
Notation : To list the filenames from the subfolder, please cheque Include files in subdirectories, you tin can also check the Include hidden files and folders as yous need. If you lot check Create hyperlinks option, it volition create hyperlinks for each filenames and folders.
Download and free trial Now !
3. And so click OK push button, all of the files contained in the selected folder and its subfolders have been displayed with following attributes in a new worksheet. See screenshot:
Click to know more than details virtually this Filename List utility.
Download and free trial Kutools for Excel Now !
Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and gratis trial At present!
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that yous have used before; Encrypt Cells with password; Create Mailing List and send emails...
- Super Formula Bar (hands edit multiple lines of text and formula); Reading Layout (hands read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns without losing Data; Divide Cells Content; Combine Indistinguishable Rows/Columns... Preclude Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Automobile Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Excerpt Text, Add Text, Remove past Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (salve and apply filter schemes to other sheets); Advanced Sort past month/week/twenty-four hour period, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on central columns; Split up Information into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or arrangement. Full features 30-mean solar day free trial. 60-day money back guarantee.
Function Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Discussion, Excel, PowerPoint , Publisher, Access, Visio and Projection.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by fifty%, and reduces hundreds of mouse clicks for you every twenty-four hours!
Source: https://www.extendoffice.com/documents/excel/2994-excel-list-all-files-in-folder-and-subfolders.html
0 Response to "How to Read All Files in Directory in Vba"
Post a Comment