利用 Excel 從文件夾中獲取文件名列表（使用和不使用 VBA）

我在一家小型諮詢公司工作的第一天，我被安排了三天的短期項目。工作很簡單。

網絡驅動器上有許多文件夾，每個文件夾中都有數百個文件。

我必須遵循以下三個步驟：

1. 選擇文件並複制其名稱。
2. 將該名稱粘貼到 Excel 中的單元格中，然後按 Enter。
3. 移至下一個文件並重複步驟 1 和 2。

聽起來很簡單吧？

這很簡單，而且非常浪費時間。

如果我知道正確的技術，我花了三天時間完成的工作可以在幾分鐘內完成。

在本教程中，我將向您展示使整個過程超級快速和超級簡單（使用和不使用 VBA）的不同方法。

本教程中顯示的方法的限制：使用下面顯示的技術，您將只能獲取主文件夾中文件的名稱。您不會獲得主文件夾中子文件夾中文件的名稱。這是一種使用 Power Query 從文件夾和子文件夾中獲取文件名的方法

使用 FILES 函數從文件夾中獲取文件名列表

聽說過FILES 函數嗎？

如果您沒有，請不要擔心。

它來自童年時代的 Excel 電子表格（第 4 版公式）。

雖然此公式不適用於工作表單元格，但它仍然適用於命名範圍。我們將使用這個事實從指定文件夾中獲取文件名列表。

現在，假設您在桌面上有一個名為“ Test Folder ”的文件夾，並且您想要獲取該文件夾中所有文件的文件名列表。

以下是將為您提供此文件夾中文件名的步驟：

1. 在單元格 A1 中，輸入文件夾完整地址，後跟星號 (*)
   • 例如，如果您的文件夾位於 C 驅動器中，則地址將類似於
     C:\Users\YOUR NAME\Desktop\Test Folder\*
   • 如果您不確定如何獲取文件夾地址，請使用以下方法：
     • • 在要從中獲取文件名的文件夾中，創建一個新的 Excel 工作簿或打開文件夾中的現有工作簿，然後在任何單元格中使用以下公式。此公式將為您提供文件夾地址並在末尾添加星號 (*)。現在，您可以將此地址複製粘貼（粘貼為值）到您想要文件名的工作簿中的任何單元格（本示例中為 A1）中。

         =REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

         [如果您在文件夾中創建了一個新工作簿以使用上述公式並獲取文件夾地址，您可能需要將其刪除，使其不在該文件夾中的文件列表中]

2. 轉到“公式”選項卡，然後單擊“定義名稱”選項。
3. 在 New Name 對話框中，使用以下詳細信息
   • 名稱：FileNameList（隨意選擇您喜歡的任何名稱）
   • 範圍：工作簿
   • 指：=FILES(Sheet1!$A$1)
4. 現在要獲取文件列表，我們將在 INDEX 函數中使用命名範圍。轉到單元格 A3（或您希望名稱列表開始的任何單元格）並輸入以下公式：

   =IFERROR(INDEX(FileNameList,ROW()-2),"")

5. 將其向下拖動，它將為您提供文件夾中所有文件名的列表

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

如果要獲取具有特定擴展名的所有文件，只需更改具有該文件擴展名的星號。例如，如果你只想要 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),"")

• 將公式複制並粘貼到下面的單元格中以獲取所有文件的列表。

請注意，我在一個單元格中輸入了文件夾位置，然後在GetFileNames公式中使用了該單元格。您還可以在公式中硬編碼文件夾地址，如下所示：

=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 技巧。我很想向你學習。在評論區分享吧！