如何仅列出指定文件夹中的.xlsx文件？VBA代码优化咨询

你的代码统计.xlsx文件数量的逻辑是正常的，但在列出文件的环节没有做类型筛选，所以会输出所有文件。只需要在遍历文件的循环中添加扩展名判断即可，以下是修改后的完整代码：

Sub Outstanding39()

    'Count files from selected folder
    Dim folder_path As String
    Dim strtype As String
    Dim totalfiles As Variant

    strtype = "*.xlsx" '去掉末尾的*，避免匹配到.xlsx.zip这类冗余文件

    folder_path = Worksheets("Data2").Cells(83, 2).Value

    If Right(folder_path, 1) <> "\" Then folder_path = folder_path & "\" 
    totalfiles = Dir(folder_path & strtype)

    Dim i As Integer

    While (totalfiles <> "")
        i = i + 1
        totalfiles = Dir
    Wend

    Worksheets("Open").Cells(15, 7).Value = i
    Worksheets("Open").Cells(15, 7).Select

    'List Files from selected folder
    Dim objFSO As Scripting.FileSystemObject
    Dim objFile As Scripting.File
    Dim objFolder As Scripting.Folder
    Dim nextRow As Long

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFSO.GetFolder(Worksheets("Data2").Cells(83, 2).Value)

    nextRow = Cells(Rows.Count, 2).End(xlUp).Row + 1

    For Each objFile In objFolder.Files
        ' 添加扩展名筛选：仅处理.xlsx文件
        If LCase(objFSO.GetExtensionName(objFile.Name)) = "xlsx" Then
            Cells(nextRow, 2) = objFile.Name
            Cells(nextRow, 16) = objFile.ParentFolder
            nextRow = nextRow + 1
        End If
    Next

End Sub

关键修改说明： #

• 在文件遍历循环中新增了If LCase(objFSO.GetExtensionName(objFile.Name)) = "xlsx" Then判断，通过FileSystemObject的GetExtensionName方法获取文件扩展名，转小写后和"xlsx"对比，确保只处理目标类型文件。
• 同时把统计部分的strtype = "*.xlsx*"改为strtype = "*.xlsx"，避免误统计带.xlsx后缀的其他文件（比如xxx.xlsx.zip）。

内容的提问来源于stack exchange，提问作者Kov4n