Using VBA FileSystemObject, specific file File extension
17,489
Add the below code after For Each SubFolder In SourceFolder.SubFolders
Call List_XL_Files(SheetName, SubFolder.Path, True)
It will work
Author by
Shan
Updated on June 04, 2022Comments
-
Shan almost 2 years
I'm using the following code to list all files with xls,xlsx or xlsm extension from folder and its subfolder. The following code works but the problem is, it lists all files with all extensions from subfolders but lists only excel files from main folder. I can not figure out whats wrong with this code. Could you please help me?
Sub List_XL_Files(ByVal SheetName As String, ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean) Dim FSO As Object Dim SourceFolder As Object Dim SubFolder As Object Dim FileItem As Object Dim lRoMa As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set SourceFolder = FSO.GetFolder(SourceFolderName) lRoMa = ThisWorkbook.Sheets(SheetName).Cells(Rows.Count, 2).End(xlUp).Row + 1 ReDim arrFolders(ctr) With ThisWorkbook.Sheets(SheetName) For Each FileItem In SourceFolder.Files strFileExt = FSO.GetExtensionName(FileItem) If strFileExt = "xlsm" Or strFileExt = "xlsx" Or strFileExt = "xls" Then MsgBox strFileExt .Cells(lRoMa + r, 1).Value = lRoMa + r - 7 .Cells(lRoMa + r, 2).Formula = strFileExt .Cells(lRoMa + r, 3).Formula = FileItem.Name .Cells(lRoMa + r, 4).Formula = FileItem.Path .Cells(lRoMa + r, 5).Value = "-" .Cells(lRoMa + r, 6).Value = "" .Cells(lRoMa + r, 7).Value = "" r = r + 1 ' next row number X = SourceFolder.Path End If Next FileItem End With If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SheetName, SubFolder.Path, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End sub
Thanks
-
Shan almost 8 yearsThanks ..i forget to change there. I have two versions of this code.
-
Karthick Gunasekaran almost 8 yearsIt happens sometimes.