使用VBA FileSystemObject，特定文件文件扩展名 [英] Using VBA FileSystemObject, specific file File extension

问题描述

我正在使用以下代码从文件夹及其子文件夹中列出所有带有xls，xlsx或xlsm扩展名的文件.以下代码有效，但问题是，它列出了所有具有子文件夹扩展名的文件，但仅列出了主文件夹中的excel文件.我无法弄清楚这段代码有什么问题.你能帮我吗?

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

谢谢

推荐答案

在For Each SubFolder In SourceFolder.SubFolders

Call List_XL_Files(SheetName, SubFolder.Path, True)

它将起作用

这篇关于使用VBA FileSystemObject，特定文件文件扩展名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！