Tôi đã phải làm theo ba bước sau:
Chọn tập tin 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 tin tiếp theo và lặp lại bước 1 & 2.
Nghe có vẻ đơn giản phải không? Nó thật sự đơn giản và lãng phí thời gian rất lớn. Thay vì làm tôi mất ba ngày. Thì tôi có thể được thực hiện trong vài phút nếu tôi biết các kỹ thuật phù hợp.
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 này trở nên siêu nhanh và siêu dễ dàng (có và không có VBA). Hướng dẫn này bao gồm:
Dùng chức năng FILES để nhận danh sách tên tệp từ thư mục
Sử dụng VBA Nhận danh sách tất cả tên tệp từ thư mục
Sử dụng VBA Nhận danh sách tất cả các tên tệp với phần mở rộng cụ thể
Hạn chế của các phương thức được hiển thị trong hướng dẫn này:
Với các kỹ thuật được hiển thị sau đây, bạn sẽ chỉ có thể nhận được các tên tệp trong thư mục chính. Bạn sẽ 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à cách để lấy tên tệp từ thư mục và thư mục con bằng Power Query
Dùng chức năng FILES để nhận danh sách tên tệp từ thư mục
Mặc dù công thức này không hoạt động trong các ô của bảng tính. Nhưng nó vẫn hoạt động trong các phạm vi được đặt tên. Chúng tôi sẽ sử dụng thực tế này để lấy danh sách tên tệp từ một thư mục được chỉ định.
1. Trong ô A1, nhập địa chỉ đầy đủ của thư mục theo sau là dấu hoa thị (*)
Ví dụ: Nếu thư mục của bạn trong ổ C, thì địa chỉ sẽ như sau: C: Users Sumit Desktop Thư mục thử nghiệm *
Nếu bạn không chắc chắn làm thế nào để lấy địa chỉ thư mục, hãy sử dụng phương pháp sau:
Trong thư mục mà bạn muốn lấy tên tệp. Hãy tạo Sổ làm việc Excel mới hoặc mở sổ làm việc hiện có. Trong thư mục và sử dụng công thức:=REPLACE(CELL(“filename”),FIND([“,CELL(“filename”)),LEN(CELL(“filename”)),”*”).
Ttrong bất kỳ ô nào. Công thức này sẽ cung cấp cho bạn địa chỉ thư mục và thêm dấu hoa thị (*) ở cuối. Bây giờ bạn có thể sao chép dán(paste as value). Địa chỉ này vào bất kỳ ô nào (A1 trong ví dụ này) trong sổ làm việc mà bạn muốn tên tệp.
2. Đi tới tab ‘Formulas’ và chọn ‘Define Name’.
3. 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)
4. Bây giờ để có được danh sách các tệp
chúng tôi sẽ sử dụng phạm vi được đặt tên trong hàm INDEX. Chuyển đến ô A3 (hoặc bất kỳ ô nào bạn muốn danh sách tên bắt đầu) và nhập công thức sau: =IFERROR(INDEX(FileNameList,ROW()-2),””)
5. Kéo xuống và nó sẽ cung cấp cho bạn một danh sách tất cả các tên tệp trong thư mục
Bạn muốn giải nén tập tin 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ị với phần mở rộng tệp đó. Ví dụ: nếu bạn chỉ muốn các tệp excel, bạn có thể sử dụng * xls * thay vì *. Khi đó, địa chỉ thư mục bạn cần sử dụng sẽ là: C:UsersSumit DesktopTest Directory*xls*.
Tương tự, đối với các tệp tài liệu từ, sử dụng * doc *
Cách thực hiện hoạt động này
Công thức FILES lấy tên của tất cả các tệp của tiện ích mở rộng đã chỉ định trong thư mục đã chỉ định.
Trong công thức INDEX, chúng tôi đã đặt tên tệp là mảng và chúng tôi trả về tên tệp thứ 1, 2, 3, v.v. bằng cách sử dụng hàm ROW.
Lưu ý rằng tôi đã sử dụng ROW () – 2, khi chúng tôi bắt đầu từ hàng thứ ba trở đi. Vì vậy, ROW()-2 sẽ là 1 cho trường hợp đầu tiên, 2 cho trường hợp thứ hai khi số hàng là 4, v.v.
Sử dụng VBA nhận danh sách tất cả tên tệp từ thư mục
Bây giờ, tôi phải nói rằng phương pháp trên hơi phức tạp (với một số bước). Tuy nhiên, tốt hơn nhiều so với làm điều này bằng tay.
Nhưng nếu bạn có thể sử dụng VBA (hoặc nếu bạn giỏi 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 chức năng tùy chỉnh (UDF) có thể dễ dàng lấy cho bạn tên của tất cả các tệp.
Lợi ích của việc sử dụng Hàm do người dùng xác định (UDF). Bạn có thể lưu hàm trong sổ làm việc macro cá nhân và sử dụng lại dễ dàng mà không cần lặp đi lặp lại các bước. Bạn cũng có thể tạo một bổ trợ và chia sẻ chức năng này với những người khác.
Bây giờ 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.
Function GetFileNames(ByVal FolderPath As String) As VariantDim Result As VariantDim i As IntegerDim MyFile As ObjectDim MyFSO As ObjectDim MyFolder As ObjectDim MyFiles As ObjectSet 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.Namei = i + 1Next MyFile GetFileNames = Result End Function
Đoạn mã trên sẽ tạo ra một hàm GetFileNames có thể được sử dụng trong các bảng tính (giống như các hàm thông thường).
Nơi đặt các mã này
Trong trình chỉnh sửa VB. Bấm chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà bạn đang làm việc. Đi tới Insert và bấm vào Module . Nếu bạn không có thể 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 phím ‘R,).
Cách sử dụng chức năng này
Trong bất kỳ ô nào, 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 ô nơi bạn muốn có danh sách, hãy nhập công thức sau: =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),””) (Tôi đang nhập nó vào ô A3):
Copy và Paste công thức vào các ô bên dưới để có danh sách tất cả các tệp.
Lưu ý rằng tôi đã nhập vị trí thư mục trong một ô và sau đó sử dụng ô đó trong công thức GetFileNames. Bạn cũng có thể mã cứng địa chỉ thư mục trong công thức như sau: =IFERROR(INDEX(GetFileNames(“C:UsersSumitDesktopTest 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 từ hàng thứ ba trở đi. Điều này đảm bảo rằng khi tôi sao chép công thức trong các ô bên dưới, nó sẽ được tăng thêm 1. Trong trường hợp bạn có thể nhập công thức vào hàng đầu tiên của cột, bạn chỉ cần sử dụng ROW ().
Công thức này sẽ hoạt động như sau:
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 cho mỗi ô, bắt đầu từ tên đầu tiên.
Hàm IFERROR được sử dụng để trả về trống thay vì #REF! lỗi được hiển thị khi một công thức được sao chép trong một ô nhưng không có thêm tên tệp để liệt kê.
Sử dụng VBA nhận danh sách tất cả các tên tệp với phần mở rộng cụ thể
Công thức trên hoạt động tuyệt vời, khi bạn muốn nhận danh sách tất cả các tên tệp từ một thư mục trong Excel. Nhưng nếu bạn chỉ muốn lấy tên của các tệp video hoặc chỉ các tệp Excel hoặc chỉ các tên tệp có chứa một từ khóa cụ thể.
Đoạn mã trên sẽ tạo ra một hàm ‘GetFileNamesbyExt có thể được sử dụng trong các bảng tính (giống như các hàm thông thường).
Hàm này có hai đối số – vị trí thư mục và từ khóa mở rộng. Nó trả về một mảng các tên tệp khớp với phần mở rộng đã cho. Nếu không có phần mở rộng hoặc từ khóa được chỉ định, nó sẽ trả về tất cả các tên tệp trong thư mục được chỉ định.
Công thức: = GetFileNamesbyExt (Vị trí thư mục tại chỗ, phần mở rộng
Nơi đặt mã
Chuyển đến tab Developer
Nhấp vào nút Visual Basic. Điều này sẽ mở VB Editor.
Trong Trình chỉnh sửa VB, bấm chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà bạn đang làm việc, đi tới Chèn và bấm vào Module. Nếu bạn không có thể 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 phím ‘R,).
Nhấp đúp chuột vào đối tượng Module và sao chép và dán mã ở trên vào cửa sổ mã module.
Các sử dụng chức năng này
Trong bất kỳ ô nào, 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 cái này vào ô A1.
Trong một ô, 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. Tôi đã nhập cái này vào ô B1.
Trong ô nơi bạn muốn có danh sách, hãy nhập công thức sau =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),””) (Tôi đang nhập nó vào ô A3):
Copy and paste the formula in the cells below to get a list of all the files
Bất kỳ thủ thuật Excel nào bạn sử dụng cũng đều giúp cho công việc trong cuộc sống dễ dàng.
Trích nguồn: Trumpexcel.