List all files in folder " D:\MyWorkBooks", line 10: PathSpec = "D:\MyWorkBooks" FileType: line 24, specify the file types to list, default all types, case insensitive.MySheetName: line 20, specifies the name of the worksheet to list file information, default sheet name: Files.PathSpec: line 10, specify a folder, if omitted, we will open Browse for Folder to select a folder, default: browse for folder.We define several important variables you need to know: Show -1 Then Exit Function 'Check if user clicked cancel buttonįor Each Mysheet In ThisWorkbook.Worksheets Set FolderPicker = Application.FileDialog(msoFileDialogFolderPicker) Cells(LastBlankCell, 9) = "FALSE" 'Is Hidden Cells(LastBlankCell, 9) = "TRUE" 'Is Hidden Cells(LastBlankCell, 8) = oFile.Size 'File Size Cells(LastBlankCell, 7) = oFile.DateLastModified 'Last Modified Cells(LastBlankCell, 6) = oFile.DateLastAccessed 'Last Accessed Cells(LastBlankCell, 5) = oFile.DateCreated 'Data Created Cells(LastBlankCell, 4) = FileExtension 'File Extension Cells(LastBlankCell, 3) = oFile.Name 'File Name Cells(LastBlankCell, 2) = oFolder 'Folder If (FileType = "*" Or FileExtension = FileType) Then LastBlankCell = ThisWorkbook.Sheets(MySheetName).Cells(Rows.Count, 1).End(xlUp).Row + 1 'get the last blank cell of column Aįor Each oFile In oFolder.Files 'loop all filesįileExtension = UCase(Split(oFile.Name, ".")(UBound(Split(oFile.Name, ".")))) 'get file extension, eg: TXT '.insert any folder processing code here. Queue.Add fso.GetFolder(PathSpec) 'enqueueįor Each oSubfolder In oFolder.SubFolders 'loop all sub-folders MySheetName = "Files" 'Add a Sheet with name "Files"įileType = "*" '*:all, or pdf, PDF, XLSX.ĭim queue As Collection, oFolder As Object, oSubfolder As Object, oFile As Objectĭim LastBlankCell As Long, FileExtension As String If (fso.FolderExists(PathSpec) = False) Then Exit Sub 'folder exist or not?Īpplication.ScreenUpdating = False 'Disable Screen Updating to speed up macro Set fso = CreateObject("Scripting.FileSystemObject") 'Late Binding If (PathSpec = "") Then PathSpec = SelectSingleFolder 'Browse for Folder to select a folder ''Description: List all files in folder and sub-folders (include hidden ,read only.) If you want to get more file informations, such as video length, see this macro: Excel Macro: List Files in Subfolders, Version 2 List All Files in Folders and Subfolders Option Explicit Updated November 8, 2022: This code has been rewritten to meet most needs. This Excel macro can list files (include hidden files) in a specified folder and subfolders, or files with a specific extension.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |