Hàm lấy tên file trong excel
Vào ngày thứ nhất khi làm việc trong một công ty hỗ trợ tư vấn nhỏ, tôi vẫn được bố trí một dự án công trình ngắn trong ba ngày. Vào file công việc được giao có tương đối nhiều thư mục bên trên ổ đĩa mạng. Từng thư mục có hàng nghìn tệp vào đó.
Bạn đang xem: Hàm lấy tên file trong excel
Tôi sẽ phải làm theo ba cách sau:
Chọn tập tin và xào luộc tên của nó. Dán thương hiệu đó vào một ô trong Excel với nhấn Enter. Dịch chuyển đến tập tin tiếp theo sau và tái diễn bước 1 & 2.Nghe gồm vẻ đơn giản phải không? Nó thiệt sự dễ dàng và lãng phí thời gian rất lớn. Thay vày làm tôi mất cha ngày. Thì tôi có thể được thực hiện trong vài ba phút nếu như tôi biết những kỹ thuật phù hợp.
Trong hướng dẫn này, tôi đang chỉ cho mình các phương pháp khác nhau. Để làm cho cho cục bộ quá trình này trở đề xuất siêu nhanh và siêu dễ dãi (có và không tồn tại VBA). Lí giải này bao gồm:
Dùng tác dụng FILES nhằm nhận danh sách tên tệp từ bỏ thư mục thực hiện VBA dìm danh sách tất cả tên tệp từ bỏ thư mục thực hiện VBA nhấn danh sách tất cả các tên tệp cùng với phần mở rộng cụ thểHạn chế của những phương thức được hiển thị trong lí giải này:
Với các kỹ thuật được hiển thị sau đây, các 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 cảm nhận tên của những tệp trong các thư mục nhỏ trong folder chính. Đây là phương pháp để lấy tên tệp từ thư mục với thư mục con bằng Power Query
Dùng tác dụng FILES nhằm nhận list tên tệp trường đoản cú thư mục
Mặc dù phương pháp này không hoạt động trong những ô của bảng tính. Nhưng nó vẫn vận động trong những phạm vi được để tên. Công ty chúng tôi sẽ sử dụng thực tế này để đưa danh sách thương hiệu tệp từ 1 thư mục được chỉ định.
Bây giờ, giả sử các bạn có một thư mục bao gồm tên-Folder. Thư mục soát sổ trên screen nền và bạn muốn nhận list tên tệp cho toàn bộ các tệp trong thư mục này. Tiếp sau đây là quá trình sẽ cung cấp cho bạn tên tệp từ folder này:
1. Trong ô A1, nhập showroom đầy đầy đủ của thư mục theo sau là vệt hoa thị (*)
Ví dụ: giả dụ thư mục của chúng ta trong ổ C, thì add sẽ như sau: C: Users Sumit Desktop Thư mục nghiên cứu *
Nếu các bạn không chắc hẳn rằng làm gắng nào nhằm lấy showroom thư mục, hãy sử dụng phương thức sau:
Trong thư mục mà bạn có nhu cầu lấy tên tệp. Hãy sinh sản Sổ thao tác làm việc Excel new hoặc mở sổ làm việc hiện có. Trong folder và áp dụng công thức:=REPLACE(CELL(“filename”),FIND(<“,CELL(“filename”)),LEN(CELL(“filename”)),”*”).
Ttrong ngẫu nhiên ô nào. Cách làm này sẽ cung cấp cho bạn showroom thư mục cùng thêm vết hoa thị (*) ngơi nghỉ cuối. Bây giờ bạn có thể sao chép dán(paste as value). Địa chỉ này vào ngẫu nhiên ô làm sao (A1 trong lấy ví dụ này) trong sổ thao tác mà bạn có nhu cầu tên tệp.
2. Đi cho tới tab ‘Formulas’ và lựa chọn ‘Define Name’.
3. Trong vỏ hộp thoại thương hiệu mới, áp dụng các chi tiết sau
Tên: FileNameList (thoải mái chọn ngẫu nhiên 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ẽ áp dụng phạm vi được đặt tên trong hàm INDEX. Chuyển mang đến ô A3 (hoặc ngẫu nhiên ô nào bạn muốn danh sách tên bắt đầu) cùng nhập cách làm sau: =IFERROR(INDEX(FileNameList,ROW()-2),””)
5. Kéo xuống với nó sẽ cung cấp cho chính mình một danh sách toàn bộ các tên tệp vào thư mục
Nếu bạn muốn nhận toàn bộ các tệp bao gồm phần không ngừng 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 khách hàng chỉ muốn các tệp excel, bạn cũng có thể sử dụng * xls * thay vị *. Lúc đó, địa chỉ thư mục bạn cần sử dụng sẽ là: C:UsersSumit DesktopTest Directory*xls*.
Tương tự, so 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àyCông thức FILES mang tên của tất cả các tệp của tiện ích mở rộng đã chỉ định và hướng dẫn trong thư mục đang chỉ định.
Trong bí quyết INDEX, chúng tôi đã đặt tên tệp là mảng và shop chúng tôi trả về tên tệp vật dụng 1, 2, 3, v.v. Bằng cách sử dụng hàm ROW.
Lưu ý rằng tôi đã thực hiện ROW () – 2, khi chúng tôi ban đầu từ sản phẩm thứ cha trở đi. Do vậy, ROW()-2 sẽ là một cho trường hòa hợp đầu tiên, 2 mang lại trường hợp máy hai lúc số hàng là 4, v.v.
Sử dụng VBA dấn danh sách toàn bộ tên tệp từ thư mục
Bây giờ, tôi cần nói rằng phương pháp trên hơi tinh vi (với một số trong những bước). Tuy nhiên, tốt hơn các so cùng với làm vấn đề này bằng tay.
Nhưng nếu chúng ta cũng có thể sử dụng VBA (hoặc nếu bạn xuất sắc theo công việc chính xác nhưng tôi vẫn liệt kê mặt dưới). Bạn có thể tạo một chức năng thiết lập (UDF) rất có thể dễ dàng lấy cho mình tên của toàn bộ các tệp.
Lợi ích của việc thực hiện Hàm do bạn dùng xác minh (UDF). Bạn cũng có thể lưu hàm vào sổ thao tác làm việc macro cá thể và sử dụng lại dễ dãi mà không bắt buộc lặp đi lặp lại các bước. Chúng ta cũng có thể tạo một bổ trợ và share chức năng này với những người dân khác.
Bây giờ hãy nhằm tôi cung cấp cho chính mình mã VBA. Sẽ khởi tạo một hàm để mang danh sách tất cả các thương hiệu tệp xuất phát từ 1 thư mục vào 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 over FunctionĐoạn mã trên sẽ khởi tạo ra một hàm GetFileNames có thể được sử dụng trong những bảng tính (giống như những hàm thông thường).
Xem thêm: Màn Hình Iphone 6 Bị Lỗi Màn Hình Iphone 6 Thường Gặp Và Cách Khắc Phục
Nơi đặt các mã này
Thực hiện nay theo công việc dưới phía trên để xào luộc mã này trong VB Editor. Chuyển mang lại tab Developer.
Trong trình chỉnh sửa VB. Nhấp chuột phải vào bất kỳ đối tượng nào của sổ làm việc mà nhiều người đang làm việc. Đi cho tới Insert và click chuột Module . Nếu bạn không rất có thể thấy Project Explorer. Hãy áp dụng phím tắt Control + R (giữ phím tinh chỉnh và điều khiển và thừa nhận phím ‘R,).
Cách sử dụng tính năng này
Dưới đây là các bước để sử dụng chức năng này trong một bảng tính:
Trong ngẫu nhiên ô nào, nhập add 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 có nhu cầu có danh sách, hãy nhập công thức sau: =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),””) (Tôi sẽ nhập nó vào ô A3):Copy với Paste công thức vào các ô bên dưới để gồm danh sách toàn bộ các tệp.
Lưu ý rằng tôi sẽ nhập địa chỉ thư mục trong một ô và sau đó sử dụng ô đó trong công thức GetFileNames. Chúng ta có thể mã cứng địa chỉ thư mục trong bí quyết như sau: =IFERROR(INDEX(GetFileNames(“C:UsersSumitDesktopTest Folder”),ROW()-2),””)
Trong bí quyết trên, cửa hàng chúng tôi đã sử dụng ROW()-2 và chúng tôi bắt đầu từ mặt hàng thứ tía trở đi. Điều này bảo đảm rằng lúc tôi xào luộc công thức trong các ô mặt dưới, nó đang được tăng lên 1. Vào trường hợp bạn cũng có thể nhập phương pháp vào số 1 tiên của cột, bạn chỉ việc sử dụng ROW ().
Công thức này sẽ vận động như sau:
Công thức GetFileNames trả về một mảng đựng tên của toàn bộ các tệp trong thư mục.
Hàm INDEX được sử dụng để liệt kê một thương hiệu tệp cho mỗi ô, bước đầ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 1 công thức được xào nấu trong một ô nhưng không có thêm thương hiệu tệp nhằm liệt kê.
Sử dụng VBA dấn danh sách toàn bộ các thương hiệu tệp cùng với phần không ngừng mở rộng cụ thể
Công thức trên vận động tuyệt vời, khi bạn có nhu cầu nhận danh sách toàn bộ các thương hiệu tệp xuất phát từ một thư mục vào Excel. Nhưng nếu khách hàng chỉ ước ao lấy tên của những tệp đoạn phim hoặc chỉ những tệp Excel hoặc chỉ những tên tệp tất cả chứa một từ bỏ khóa thế thể.
Trong trường hợp đó, bạn cũng có thể sử dụng một công dụng hơi không giống nhau. Dưới đây là mã sẽ được cho phép bạn bao gồm được toàn bộ các tên tệp với một từ bỏ khóa ví dụ trong đó (hoặc của một trong những phần mở rộng cầm cố thể).
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”) phối MyFolder = MyFSO.GetFolder(FolderPath) mix MyFiles = MyFolder.Files ReDim Result(1 khổng lồ MyFiles.Count) i = 1 For Each MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) 0 Then Result(i) = MyFile.Name i = i + 1 kết thúc If Next MyFile ReDim Preserve Result(1 lớn i – 1) GetFileNamesbyExt = Result end Function
Đoạn mã trên sẽ khởi tạo ra một hàm ‘GetFileNamesbyExt có thể được sử dụng trong những bảng tính (giống như các hàm thông thường).
Hàm này còn có hai đối số – địa điểm 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 cùng với phần mở rộng đã cho. Nếu không tồn tại phần mở rộng hoặc từ khóa được chỉ định, nó vẫn trả về tất cả các thương hiệu tệp trong folder được chỉ định.
Công thức: = GetFileNamesbyExt (Vị trí thư mục trên chỗ, phần mở rộng
Nơi để mã
Thực hiện tại theo các bước dưới trên đây để xào luộc mã này trong VB Editor.
Chuyển mang đến tab Developer Nhấp vào nút Visual Basic. Điều này đã mở VB Editor. Trong Trình sửa đổi VB, click chuột phải vào ngẫu nhiên đối tượng nào của sổ thao tác mà bạn đang làm việc, đi tới Chèn và nhấp chuột Module. Nếu như bạn không có thể thấy Project Explorer, hãy sử dụng phím tắt Control + R (giữ phím tinh chỉnh và dìm phím ‘R,). Nhấp đúp chuột vào đối tượng Module và xào nấu và dán mã sinh hoạt trên vào cửa sổ mã module.Xem thêm: Cách Đặt Video Đại Diện Facebook, Cách Đặt Video Làm Avatar
Các sử dụng tính năng này
Dưới trên đây là quá trình để sử dụng tính năng này vào một bảng tính:
Trong ngẫu nhiên ô nào, nhập địa chỉ cửa hàng thư mục của folder mà bạn muốn liệt kê tên tệp. Tôi đang nhập điều này vào ô A1. Trong một ô, nhập phần không ngừng mở rộng (hoặc tự khóa) mà bạn có nhu cầu tất cả những tên tệp. Tôi đang nhập điều này vào ô B1. Vào ô nơi bạn muốn có danh sách, hãy nhập phương pháp 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 khổng lồ get a list of all the files
Bất kỳ thủ thuật Excel như thế nào bạn áp dụng cũng phần đông giúp cho quá trình trong cuộc sống dễ dàng.
