利用Excel 从文件夹中获取文件名列表（使用和不使用VBA）

0 分享

我在一家小型咨询公司工作的第一天，我被安排了三天的短期项目。工作很简单。

网络驱动器上有许多文件夹，每个文件夹中都有数百个文件。

我必须遵循以下三个步骤：

选择文件并复制其名称。
将该名称粘贴到Excel 中的单元格中，然后按Enter。
移至下一个文件并重复步骤1 和2。

听起来很简单吧？

这很简单，而且非常浪费时间。

如果我知道正确的技术，我花了三天时间完成的工作可以在几分钟内完成。

在本教程中，我将向您展示使整个过程超级快速和超级简单（使用和不使用VBA）的不同方法。

本教程中显示的方法的限制：使用下面显示的技术，您将只能获取主文件夹中文件的名称。您不会获得主文件夹中子文件夹中文件的名称。这是一种使用Power Query 从文件夹和子文件夹中获取文件名的方法

内容

1 使用FILES 函数从文件夹中获取文件名列表
2 使用VBA 从文件夹中获取所有文件名的列表
3 使用VBA 获取具有特定扩展名的所有文件名的列表
4 哦，您好很高兴认识你。

使用FILES 函数从文件夹中获取文件名列表

听说过FILES 函数吗？

如果您没有，请不要担心。

它来自童年时代的Excel 电子表格（第4 版公式）。

虽然此公式不适用于工作表单元格，但它仍然适用于命名范围。我们将使用这个事实从指定文件夹中获取文件名列表。

现在，假设您在桌面上有一个名为“ 测试文件夹 ”的文件夹，并且您想要获取该文件夹中所有文件的文件名列表。

以下是将为您提供此文件夹中文件名的步骤：

在单元格A1 中，输入文件夹完整地址，后跟星号(*)
- 例如，如果您的文件夹位于C 驱动器中，则地址将类似于
  C:\Users\YOUR NAME\Desktop\Test Folder\*
- 如果您不确定如何获取文件夹地址，请使用以下方法：
  - - 在要从中获取文件名的文件夹中，创建一个新的Excel 工作簿或打开文件夹中的现有工作簿，然后在任何单元格中使用以下公式。此公式将为您提供文件夹地址并在末尾添加星号(*)。现在，您可以将此地址复制粘贴（粘贴为值）到您想要文件名的工作簿中的任何单元格（本示例中为A1）中。
```
=REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")
```
      [如果您在文件夹中创建了一个新工作簿以使用上述公式并获取文件夹地址，您可能需要将其删除，使其不在该文件夹中的文件列表中]
转到“公式”选项卡，然后单击“定义名称”选项。
在New Name 对话框中，使用以下详细信息
- 名称：FileNameList（随意选择您喜欢的任何名称）
- 范围：工作簿
- 指：=FILES(Sheet1!$A$1)
现在要获取文件列表，我们将在INDEX 函数中使用命名范围。转到单元格A3（或您希望名称列表开始的任何单元格）并输入以下公式：
```
=IFERROR(INDEX(FileNameList,ROW()-2),"")
```
将其向下拖动，它将为您提供文件夹中所有文件名的列表

想要提取具有特定扩展名的文件？？

如果要获取具有特定扩展名的所有文件，只需更改具有该文件扩展名的星号。例如，如果你只想要excel 文件，你可以使用*xls* 而不是 *

所以你需要使用的文件夹地址是C:UsersSumitDesktopTest Folder*xls*

同样，对于word 文档文件，使用*doc*

这是如何运作的？

FILES 公式检索指定文件夹中指定扩展名的所有文件的名称。

在INDEX 公式中，我们将文件名作为数组给出，并使用ROW 函数返回第一个、第二个、第三个文件名等。

请注意，我使用了ROW()-2，因为我们从第三行开始。因此，当行号为4 时，第一个实例的ROW()-2 将为1，第二个实例为2，依此类推。

使用VBA 从文件夹中获取所有文件名的列表

现在，我必须说上面的方法有点复杂（有很多步骤）。

但是，它比手动执行此操作要好得多。

但是，如果您对使用VBA 感到满意（或者如果您擅长遵循我将在下面列出的确切步骤），您可以创建一个自定义函数(UDF)，它可以轻松获取所有文件的名称。

使用用户定义函数(UDF) 的好处是您能够将函数保存在个人宏工作簿中并轻松重复使用，而无需一次又一次地重复这些步骤。您还可以创建加载项并与他人共享此功能。

现在让我首先给你VBA 代码，它将创建一个函数来从Excel 中的文件夹中获取所有文件名的列表。

Function GetFileNames(ByVal FolderPath 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 Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

上面的代码将创建一个可在工作表中使用的函数GetFileNames（就像常规函数一样）。

把这段代码放在哪里？

按照以下步骤在VB 编辑器中复制此代码。

如何使用此功能？

以下是在工作表中使用此功能的步骤：

在任何单元格中，输入要从中列出文件名的文件夹的文件夹地址。
在您想要列表的单元格中，输入以下公式（我在单元格A3 中输入它）：
```
=IFERROR(INDEX(GetFileNames($A$1),ROW()-2),"")
```
将公式复制并粘贴到下面的单元格中以获取所有文件的列表。

请注意，我在一个单元格中输入了文件夹位置，然后在获取文件名公式中使用了该单元格。您还可以在公式中硬编码文件夹地址，如下所示：

=IFERROR(INDEX(GetFileNames("C:\Users\YOUR NAME\Desktop\Test Folder"),ROW()-2),"")

在上面的公式中，我们使用了ROW()-2，我们从第三行开始。这确保了当我在下面的单元格中复制公式时，它将增加1。如果您在列的第一行输入公式，您可以简单地使用ROW()。

这个公式是如何工作的？

GetFileNames 公式返回一个数组，其中包含文件夹中所有文件的名称。

INDEX 函数用于列出每个单元格的一个文件名，从第一个开始。

IFERROR 函数用于返回空白而不是#REF！在单元格中复制公式但没有更多文件名要列出时显示的错误。

使用VBA 获取具有特定扩展名的所有文件名的列表

当您想从Excel 中的文件夹中获取所有文件名的列表时，上述公式非常有用。

但是，如果您只想获取视频文件的名称，或者只获取Excel 文件的名称，或者只获取包含特定关键字的文件名，该怎么办。

在这种情况下，您可以使用稍微不同的功能。

下面的代码将允许您获取其中包含特定关键字（或特定扩展名）的所有文件名。

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 = Result End Function

上面的代码将创建一个可在工作表中使用的函数“ GetFileNamesbyExt ”（就像常规函数一样）。

这个函数有两个参数——文件夹位置和扩展关键字。它返回与给定扩展名匹配的文件名数组。如果没有指定扩展名或关键字，它将返回指定文件夹中的所有文件名。

语法：=GetFileNamesbyExt(“文件夹位置”,”扩展名”)

把这段代码放在哪里？

按照以下步骤在VB 编辑器中复制此代码。

转到开发人员选项卡。
单击Visual Basic 按钮。这将打开VB 编辑器。
在VB 编辑器中，右键单击您正在使用的工作簿的任何对象，转到插入并单击模块。如果您没有看到Project Explorer，请使用键盘快捷键Control + R（按住control 键并按“R”键）。
双击模块对象并将上面的代码复制并粘贴到模块代码窗口中。

如何使用此功能？

以下是在工作表中使用此功能的步骤：

在任何单元格中，输入要从中列出文件名的文件夹的文件夹地址。我在单元格A1 中输入了这个。
在单元格中，输入您想要所有文件名的扩展名（或关键字）。我在单元格B1 中输入了这个。
在您想要列表的单元格中，输入以下公式（我在单元格A3 中输入它）：
```
=IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")
```
将公式复制并粘贴到下面的单元格中以获取所有文件的列表。

你呢？您用来简化生活的任何Excel 技巧。我很想向你学习。在评论区分享吧！