excel中使用VBA提取指定文件夹中指定扩展名的所有文件名的技巧

在不使用VBA，列出指定文件夹的所有文件名的方法中，我们使用Excel中隐藏的FILES函数定义名称，并将之应用到Excel公式中提取指定文件夹中所有文件名。今天更进一步：按扩展名筛选，只提取我们想要的那一类文件（比如只提取 .xlsx 或 .jpg）。配合自定义函数（UDF），像使用内置函数一样简单！

可以使用VBA创建一个自定义函数（UDF），轻松获取指定文件夹指定扩展名的所有文件的名称。

代码来了

'================================================
' 函数名称：GetFileNamesbyExt
' 功能描述：获取指定文件夹中特定扩展名的所有文件名
' 参　　数：FolderPath - 文件夹路径
'           FileExt    - 文件扩展名（如".xlsx"）
' 返 回 值：包含文件名的数组
'================================================
Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant    
Dim Result As Variant    
Dim i As Integer    
Dim MyFile As Object    
Dim MyFSO As Object    
Dim MyFolder As Object    
Dim MyFiles As Object     
Set MyFSO = CreateObject("Scripting.FileSystemObject")    
Set MyFolder = MyFSO.GetFolder(FolderPath)    
Set MyFiles = MyFolder.Files     
ReDim Result(1 To MyFiles.Count)    
i = 1     
For Each MyFile In MyFiles        
If InStr(1, MyFile.Name, FileExt) <> 0 Then            
Result(i) = MyFile.Name            
i = i + 1        
End If    
Next MyFile     
ReDim Preserve Result(1 To i - 1)    
GetFileNamesbyExt = ResultEnd Function

上述代码将创建了一个名为GetFileNamesbyExt的函数，该函数可像Excel内置函数一样在工作表中使用。

如何使用这个函数？

• 插入模块打开VBE编辑器（Alt + F11），插入一个标准模块，粘贴上述代码。
• 使用公式提取

在工作表任意单元格中输入希望列出文件名的文件夹地址，示例中为单元格A1；在工作表任意单元格中输入指定的扩展名，示例中为单元格B1。然后，在想要列出文件名单元格中（示例为单元格A3），输入公式：

=IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")

向下拖动公式复制到下方单元格直至列出所有文件名，如下图1所示。

注意，在上面的公式中，使用了ROW()-2，因为公式单元格是从第三行开始的，这确保了当在下面的单元格中复制公式时，它将递增1。如果在某列的第一行输入公式，则可以简单地使用row()。

特别提示

• 如果省略第二个参数（扩展名），函数将返回文件夹中 所有文件名；
• 扩展名区分大小写？不用怕，InStr 默认不区分，放心用；
• 路径最后不要忘了加反斜杠？不用加，GetFolder 自动识别。

写在最后

这个小工具非常适合：

• 整理照片时提取所有 .jpg 文件；
• 汇总报表时提取所有 .xlsx 文件；
• 清理电脑时列出所有 .tmp 临时文件。