gpt4 book ai didi

excel - 使用 VBA FileSystemObject,特定文件 文件扩展名

转载 作者:行者123 更新时间:2023-12-04 20:59:56 34 4
gpt4 key购买 nike

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

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)

它会工作

关于excel - 使用 VBA FileSystemObject,特定文件 文件扩展名,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38354947/

34 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com