Vào ngày đầu tiên làm việc tại một công ty tư vấn nhỏ, tôi đã được giao cho một dự án ngắn hạn ba ngày.Công việc rất đơn giản.
Có nhiều thư mục trên ổ đĩa mạng, mỗi thư mục có hàng trăm tệp.
Tôi phải làm theo ba bước sau:
- Chọn tệp và sao chép tên của nó.
- Dán tên vào một ô trong Excel và nhấn Enter.
- Di chuyển đến tệp tiếp theo và lặp lại bước 1 và 2.
Nghe có vẻ đơn giản đúng không?
Nó đơn giản và rất tốn thời gian.
Những gì tôi đã làm trong ba ngày có thể hoàn thành trong vài phút nếu tôi biết đúng kỹ thuật.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn các cách khác nhau để làm cho toàn bộ quá trình siêu nhanh và siêu dễ dàng (có và không có VBA).
Hạn chế của các phương pháp được hiển thị trong hướng dẫn này:Sử dụng kỹ thuật hiển thị bên dưới, bạn sẽ chỉ có thể lấy tên của các tệp trong thư mục chính của bạn.Bạn không nhận được tên của các tệp trong các thư mục con trong thư mục chính.Đây là một cách để lấy tên tệp từ các thư mục và thư mục con bằng cách sử dụng Power Query
Nội dung
Sử dụng chức năng FILES để lấy danh sách tên tệp từ một thư mục
nghe về nóHàm FILES?
Nếu bạn không, đừng lo lắng.
Đó là từ một bảng tính Excel từ thời thơ ấu (công thức phiên bản 4).
Mặc dù công thức này không hoạt động với các ô trang tính, nhưng nó vẫn hoạt động với các phạm vi được đặt tên.Chúng tôi sẽ sử dụng dữ kiện này để lấy danh sách tên tệp từ thư mục được chỉ định.
Bây giờ, giả sử bạn có một tệp trên màn hình của mình có tên " Thư mục Kiểm tra ”Và bạn muốn nhận danh sách tên tệp cho tất cả các tệp trong thư mục đó.
Dưới đây là các bước sẽ cung cấp cho bạn tên tệp trong thư mục này:
- Trong ô A1, nhập địa chỉ thư mục đầy đủ, theo sau là dấu hoa thị (*)
- Ví dụ: nếu thư mục của bạn nằm trong ổ C, địa chỉ sẽ giống như
C: \ Người dùng \ TÊN CỦA BẠN \ Máy tính để bàn \ Thư mục kiểm tra \ * - Nếu bạn không chắc chắn về cách lấy địa chỉ thư mục, hãy sử dụng phương pháp sau:
-
- Trong thư mục bạn muốn lấy tên tệp, hãy tạo một sổ làm việc Excel mới hoặc mở một sổ làm việc hiện có trong thư mục và sử dụng công thức sau trong bất kỳ ô nào.Công thức này sẽ cung cấp cho bạn địa chỉ thư mục có dấu hoa thị (*) ở cuối.Bây giờ bạn có thể sao chép-dán (dán dưới dạng giá trị) địa chỉ này vào bất kỳ ô nào trong sổ làm việc (A1 trong ví dụ này) nơi bạn muốn tên tệp.
= REPLACE (CELL ("tên tệp"), TÌM ("[", CELL ("tên tệp")), LEN (CELL ("tên tệp")), "*")[Nếu bạn đã tạo một sổ làm việc mới trong một thư mục để sử dụng công thức trên và lấy địa chỉ thư mục, bạn có thể cần phải xóa nó để nó không có trong danh sách các tệp trong thư mục đó]
- Trong thư mục bạn muốn lấy tên tệp, hãy tạo một sổ làm việc Excel mới hoặc mở một sổ làm việc hiện có trong thư mục và sử dụng công thức sau trong bất kỳ ô nào.Công thức này sẽ cung cấp cho bạn địa chỉ thư mục có dấu hoa thị (*) ở cuối.Bây giờ bạn có thể sao chép-dán (dán dưới dạng giá trị) địa chỉ này vào bất kỳ ô nào trong sổ làm việc (A1 trong ví dụ này) nơi bạn muốn tên tệp.
-
- Ví dụ: nếu thư mục của bạn nằm trong ổ C, địa chỉ sẽ giống như
- Chuyển đến tab "Công thức" và nhấp vào tùy chọn "Xác định tên".
- Trong hộp thoại Tên mới, sử dụng các chi tiết sau
- Tên: FileNameList (thoải mái chọn bất kỳ tên nào bạn thích)
- Phạm vi: Workbook
- Tham chiếu đến: = FILES (Sheet1! $ A $ 1)
- Bây giờ để có được danh sách các tệp, chúng ta sẽ sử dụng một dải ô đã đặt tên trong hàm INDEX.Chuyển đến ô A3 (hoặc bất kỳ ô nào bạn muốn bắt đầu danh sách tên) và nhập công thức sau:
= IFERROR (INDEX (FileNameList, ROW () - 2), "") - Kéo nó xuống và nó sẽ cung cấp cho bạn danh sách tất cả các tên tệp trong thư mục
Bạn muốn giải nén các tệp với một phần mở rộng cụ thể? ?
Nếu bạn muốn nhận tất cả các tệp có phần mở rộng cụ thể, chỉ cần thay đổi dấu hoa thị bằng phần mở rộng tệp đó.Ví dụ: nếu bạn chỉ muốn tệp excel, bạn có thể sử dụng * xls * thay vì *
Vì vậy, địa chỉ thư mục bạn cần sử dụng làC: UsersSumitDesktopTest Folder * xls *
Tương tự như vậy, đối với các tệp tài liệu word, hãy sử dụng * doc *
Cái này hoạt động ra sao?
Công thức FILES truy xuất tên của tất cả các tệp có phần mở rộng được chỉ định trong thư mục được chỉ định.
Trong công thức INDEX, chúng tôi đặt tên tệp dưới dạng một mảng và sử dụng hàm ROW để trả về tên tệp đầu tiên, thứ hai, thứ ba, v.v.
Lưu ý rằng tôi đã sử dụngROW () - 2, vì chúng ta bắt đầu ở dòng thứ ba.Vì vậy, khi số hàng là 4, ROW () - 2 sẽ là 1 cho trường hợp đầu tiên, 2 cho lần thứ hai, v.v.
Nhận danh sách tất cả các tên tệp từ một thư mục bằng VBA
Bây giờ, tôi phải nói rằng phương pháp trên là một chút phức tạp (có nhiều bước).
Tuy nhiên, nó tốt hơn nhiều so với làm điều này theo cách thủ công.
Tuy nhiên, nếu bạn cảm thấy thoải mái khi sử dụng VBA (hoặc nếu bạn giỏi làm theo các bước chính xác mà tôi sẽ liệt kê bên dưới), bạn có thể tạo một hàm tùy chỉnh (UDF) có thể dễ dàng lấy tên của tất cả các tệp.
Lợi ích của việc sử dụng các chức năng do người dùng xác định (UDF)是BạnCó thểsẽhàm sốLưu trong sổ làm việc macro cá nhân và sử dụng lại dễ dàng mà không cần phải lặp đi lặp lại các bước này.Bạn cũng có thể tạo tiện ích bổ sung và chia sẻ chức năng này với những người khác.
Bây giờ, trước tiên hãy để tôi cung cấp cho bạn mã VBA sẽ tạo một hàm để lấy danh sách tất cả các tên tệp từ một thư mục trong Excel.
Hàm 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") Đặt MyFolder = MyFSO. GetFolder (FolderPath) Đặt MyFiles = MyFolder.Files ReDim Kết quả (1 Đến MyFiles.Count) i = 1 Cho mỗi MyFile Trong MyFiles Kết quả (i) = MyFile.Name i = i + 1 MyFile Tiếp theo GetFileNames = Kết quả Kết thúc Hàm
Đoạn mã trên sẽ tạo một hàm GetFileNames có thể được sử dụng trong trang tính (giống như một hàm thông thường).
Đặt mã này ở đâu?
Làm theo các bước dưới đây để sao chép mã này trong trình soạn thảo VB.
Làm thế nào để sử dụng chức năng này?
Dưới đây là các bước để sử dụng tính năng này trong trang tính:
- Trong bất kỳ ô nào, hãy nhập địa chỉ thư mục của thư mục mà bạn muốn liệt kê tên tệp.
- Trong ô mà bạn muốn có danh sách, hãy nhập công thức sau (tôi đã nhập công thức vào ô A3):
= IFERROR (INDEX (GetFileNames ($ A $ 1), ROW () - 2), "") - Sao chép và dán công thức vào ô bên dưới để nhận danh sách tất cả các tệp.
Lưu ý rằng tôi đã nhập vị trí thư mục vào một ô và sau đó nhậpLấy tên tập tinÔ này được sử dụng trong một công thức.Bạn cũng có thể mã hóa địa chỉ thư mục trong công thức như sau:
= IFERROR (INDEX (GetFileNames ("C: \ Users \ TÊN CỦA BẠN \ Desktop \ Test Folder"), ROW () - 2), "")
Trong công thức trên, chúng tôi đã sử dụng ROW () - 2 và chúng tôi bắt đầu với hàng thứ ba.Điều này đảm bảo rằng khi tôi sao chép công thức trong ô bên dưới, nó sẽ tăng lên 1.Nếu bạn nhập công thức vào hàng đầu tiên của cột, bạn có thể chỉ cần sử dụng ROW ().
Công thức này hoạt động như thế nào?
Công thức GetFileNames trả về một mảng chứa tên của tất cả các tệp trong thư mục.
Hàm INDEX được sử dụng để liệt kê một tên tệp trên mỗi ô, bắt đầu bằng tên đầu tiên.
Hàm IFERROR được sử dụng để trả về giá trị trống thay vì #REF!Lỗi hiển thị khi công thức được sao chép trong một ô nhưng không có thêm tên tệp nào để liệt kê.
Nhận danh sách tất cả các tên tệp có phần mở rộng cụ thể bằng VBA
Công thức trên rất hữu ích khi bạn muốn lấy danh sách tất cả các tên tệp từ một thư mục trong Excel.
Tuy nhiên, điều gì sẽ xảy ra nếu bạn chỉ muốn lấy tên của một tệp video, hoặc chỉ một tệp Excel, hoặc chỉ một tệp chứa một từ khóa nhất định.
Trong trường hợp này, bạn có thể sử dụng một chức năng hơi khác.
Đoạn mã dưới đây sẽ cho phép bạn lấy tất cả các tên tệp có một từ khóa cụ thể (hoặc một phần mở rộng cụ thể) trong đó.
Hàm GetFileNamesbyExt (ByVal FolderPath As String, FileExt As String) As Variant Dim Kết quả 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 Giữ nguyên Kết quả (1 Đến i - 1) GetFileNamesbyExt = Chức năng Kết thúc Kết quả
Đoạn mã trên sẽ tạo một hàm có thể được sử dụng trong trang tính " GetFileNamesbyExt ”(giống như các hàm thông thường).
Hàm này nhận hai tham số - vị trí thư mục và từ khóa mở rộng.Nó trả về một mảng tên tệp phù hợp với phần mở rộng đã cho.Nếu không có phần mở rộng hoặc từ khóa nào được chỉ định, nó sẽ trả về tất cả các tên tệp trong thư mục được chỉ định.
Cú pháp: = GetFileNamesbyExt ("Vị trí thư mục", "Tiện ích mở rộng")
Đặt mã này ở đâu?
Làm theo các bước dưới đây để sao chép mã này trong trình soạn thảo VB.
- Chuyển đến tab nhà phát triển.
- Bấm vào nút Visual Basic.Thao tác này sẽ mở trình soạn thảo VB.
- Trong trình soạn thảo VB, nhấp chuột phải vào bất kỳ đối tượng nào trong sổ làm việc bạn đang làm việc, đi tới Chèn và nhấp vào Mô-đun.Nếu bạn không thấy Project Explorer, hãy sử dụng phím tắt Control + R (giữ phím điều khiển và nhấn "R").
- Nhấp đúp vào đối tượng mô-đun và sao chép và dán đoạn mã trên vào cửa sổ mã mô-đun.
Làm thế nào để sử dụng chức năng này?
Dưới đây là các bước để sử dụng tính năng này trong trang tính:
- Trong bất kỳ ô nào, hãy nhập địa chỉ thư mục của thư mục mà bạn muốn liệt kê tên tệp.Tôi đã nhập điều này vào ô A1.
- Trong ô, hãy nhập phần mở rộng (hoặc từ khóa) mà bạn muốn tất cả các tên tệp là.Tôi đã nhập điều này vào ô B1.
- Trong ô mà bạn muốn có danh sách, hãy nhập công thức sau (tôi đã nhập công thức vào ô A3):
= IFERROR (INDEX (GetFileNamesbyExt ($ A $ 1, $ B $ 1), ROW () - 2), "") - Sao chép và dán công thức vào ô bên dưới để nhận danh sách tất cả các tệp.
Còn bạn thì saoBất kỳ thủ thuật Excel nào bạn sử dụng để đơn giản hóa cuộc sống của mình.Tôi rất thích học hỏi từ bạn.Chia sẻ nó trong phần bình luận!
