Hàm lấy tên file trong excel

Vào ngày đầu tiên khi làm việc vào một đơn vị hỗ trợ tư vấn nhỏ tuổi, tôi đã có được sắp xếp một dự án nđính thêm vào bố ngày. Trong tệp tin công việc được giao có rất nhiều tlỗi mục bên trên ổ đĩa mạng. Mỗi thỏng mục có hàng trăm tệp trong các số ấy.

Bạn đang xem: Hàm lấy tên file trong excel

Tôi vẫn yêu cầu tuân theo ba bước sau:

Chọn tập tin và xào nấu thương hiệu của nó. Dán tên đó vào một ô vào Excel với dấn Enter. Di gửi đến tập tin tiếp theo với tái diễn bước 1 & 2.

Nghe dường như đơn giản phải không? Nó thật sự dễ dàng và đơn giản với lãng phí thời gian rất cao. Ttuyệt vị làm cho tôi mất ba ngày. Thì tôi có thể được triển khai trong vài ba phút ít nếu tôi biết các chuyên môn phù hợp.

Trong gợi ý này, tôi vẫn chỉ cho bạn các bí quyết không giống nhau. Để tạo cho toàn thể quá trình này trlàm việc bắt buộc siêu nkhô cứng và siêu dễ dãi (tất cả cùng không có VBA). Hướng dẫn này bao gồm:

Dùng tính năng FILES nhằm dìm list thương hiệu tệp từ thỏng mục Sử dụng VBA Nhận list tất cả thương hiệu tệp từ thư mục Sử dụng 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ố kỉnh thể

Hạn chế của các thủ tục được hiển thị vào lý giải này:

Với các nghệ thuật được hiển thị dưới đây, bạn sẽ chỉ có thể nhận ra các tên tệp vào thư mục bao gồm. Quý Khách sẽ không còn cảm nhận tên của các tệp trong số thỏng mục con trong tlỗi mục bao gồm. Đây là phương pháp để lấy thương hiệu tệp từ thư mục và tlỗi mục nhỏ bằng Power Query

Dùng tính năng FILES nhằm nhấn list tên tệp từ tlỗi mục

Mặc mặc dù phương pháp này sẽ không hoạt động trong các ô của bảng tính. Nhưng nó vẫn chuyển động trong số phạm vi được lấy tên. Chúng tôi đã thực hiện thực tế này để lấy list tên tệp từ 1 thỏng mục được hướng đẫn.

Bây giờ, trả sử chúng ta tất cả một thỏng mục bao gồm tên-Folder. Thư mục soát sổ bên trên màn hình nền cùng bạn có nhu cầu nhấn list tên tệp đến tất cả những tệp vào tlỗi mục này. Dưới đó là các bước vẫn cung cấp cho mình thương hiệu tệp từ thư mục này:

1. Trong ô A1, nhập tác động khá đầy đủ của thỏng mục theo sau là lốt hoa thị (*)

Ví dụ: Nếu tlỗi mục của khách hàng vào ổ C, thì cửa hàng vẫn nlỗi sau: C: Users Sumit Desktop Thư mục xem sét *

Nếu chúng ta không chắc chắn làm cho chũm như thế nào để mang tác động thư mục, hãy sử dụng cách thức sau:

Trong thư mục mà lại bạn có nhu cầu mang tên tệp. Hãy sinh sản Sổ làm việc Excel bắt đầu hoặc mnghỉ ngơi sổ thao tác hiện bao gồm. Trong thỏng mục và thực hiện công thức:=REPLACE(CELL(“filename”),FIND(<“,CELL(“filename”)),LEN(CELL(“filename”)),”*”).

Tvào bất kỳ ô làm sao. Công thức này vẫn hỗ trợ cho bạn thúc đẩy thỏng mục và thêm vết hoa thị (*) làm việc cuối. Bây giờ đồng hồ bạn cũng có thể coppy dán(paste as value). Địa chỉ này vào ngẫu nhiên ô nào (Amột trong các ví dụ này) trong sổ làm việc mà bạn muốn tên tệp.

2. Đi cho tới tab ‘Formulas’ và chọn ‘Define Name’.

*

3. Trong vỏ hộp thoại Tên mới, thực hiện những chi tiết sau

Tên: FileNameList (dễ chịu lựa chọn ngẫu nhiên thương hiệu làm sao bạn thích) Phạm vi: Workbook Tyêu thích chiếu đến:=FILES(Sheet1!$A$1)

*

4. Bây giờ để sở hữu được danh sách các tệp

công ty chúng tôi vẫn sử dụng phạm vi chọn cái tên trong hàm INDEX. Chuyển mang lại ô A3 (hoặc ngẫu nhiên ô như thế nào bạn có nhu cầu list tên bắt đầu) cùng nhập phương pháp sau: =IFERROR(INDEX(FileNameList,ROW()-2),””)

5. Kéo xuống và nó đã cung ứng cho bạn một list toàn bộ các thương hiệu tệp vào tlỗi mục

*
quý khách hàng ý muốn bung file tập tin cùng với 1 phần mở rộng cầm cố thể?

Nếu bạn muốn thừa nhận tất cả những tệp gồm phần mở rộng rõ ràng. Chỉ buộc phải chuyển đổi vết hoa thị cùng với phần mở rộng tệp đó. Ví dụ: nếu khách hàng chỉ hy vọng các tệp excel, bạn có thể thực hiện * xls * rứa vị *. Khi đó, địa chỉ thỏng mục bạn cần áp dụng đang là: C:UsersSumit DesktopTest Directory*xls*.

Tương từ, đối với các tệp tư liệu từ bỏ, áp dụng * doc *

Cách thực hiện chuyển động này

Công thức FILES đem thương hiệu của tất cả những tệp của phầm mềm mở rộng đang chỉ định trong thư mục vẫn chỉ định và hướng dẫn.

Trong bí quyết INDEX, Cửa Hàng chúng tôi đã đánh tên tệp là mảng và Cửa Hàng chúng tôi trả về thương hiệu tệp lần đầu tiên, 2, 3, v.v. bằng cách áp 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ừ bỏ sản phẩm sản phẩm công nghệ ba trsinh hoạt đi. Vì vậy, ROW()-2 vẫn là 1 trong cho ngôi trường thích hợp thứ nhất, 2 mang đến ngôi trường vừa lòng máy nhị lúc số sản phẩm là 4, v.v.

Sử dụng VBA nhận list tất cả thương hiệu tệp từ bỏ thư mục

Bây giờ đồng hồ, tôi yêu cầu bảo rằng cách thức bên trên tương đối tinh vi (cùng với một vài bước). Tuy nhiên, giỏi rộng các đối với làm cho vấn đề đó bằng tay.

Nhưng ví như bạn có thể thực hiện VBA (hoặc nếu như khách hàng giỏi theo các bước đúng đắn cơ mà tôi vẫn liệt kê mặt dưới). quý khách hoàn toàn có thể tạo một chức năng thiết lập cấu hình (UDF) rất có thể dễ dàng mang cho chính mình tên của toàn bộ các tệp.

Lợi ích của bài toán sử dụng Hàm do người tiêu dùng khẳng định (UDF). Quý Khách hoàn toàn có thể lưu giữ hàm trong sổ thao tác làm việc macro cá nhân với áp dụng lại thuận lợi mà lại không bắt buộc lặp đi tái diễn công việc. Quý Khách cũng rất có thể sản xuất một bổ trợ và share tác dụng này cùng với những người dân khác.

Bây giờ hãy để tôi cung ứng cho chính mình mã VBA. Sẽ chế tạo một hàm để lấy danh sách tất cả những thương hiệu tệp xuất phát điểm từ một thỏng mục trong Excel.

Function GetFileNames(ByVal FolderPath As String) As VariantDyên Result As VariantDim i As IntegerDlặng MyFile As ObjectDlặng MyFSO As ObjectDim MyFolder As ObjectDyên MyFiles As ObjectSet MyFSO = CreateObject("Scripting.FileSystemObject")Set MyFolder = MyFSO.GetFolder(FolderPath)Set MyFiles = MyFolder.Files ReDyên 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ẽ khởi tạo ra một hàm GetFileNames hoàn toàn có thể được thực hiện trong những bảng tính (giống hệt như những hàm thông thường).

Xem thêm: 2 Cách Làm Bột Trà Xanh Không Cần Máy Xay Vẫn Mềm, Mịn, Hướng Dẫn Cách Làm Bột Trà Xanh Đúng Cách

Nơi đặt những mã này

Thực hiện tại theo quá trình dưới đây để coppy mã này vào VB Editor. Chuyển đến tab Developer.
*
Nhấp vào nút Visual Basic. Vấn đề này vẫn msinh sống VB Editor.
*

Trong trình chỉnh sửa VB. Bnóng chuột đề xuất vào ngẫu nhiên đối tượng người dùng làm sao của sổ làm việc cơ mà nhiều người đang thao tác làm việc. Đi tới Insert và click chuột Module . Nếu bạn không rất có thể thấy Project Explorer. Hãy sử dụng phím tắt Control + R (giữ phím tinh chỉnh với thừa nhận phím ‘R,).

*
Nhấp lưu ban chuột vào đối tượng người dùng Module cùng Copy và Paste mã ngơi nghỉ trên vào cửa sổ mã Module.
*

Cách thực hiện công dụng này

Dưới đây là công việc nhằm sử dụng tính năng này trong một bảng tính:

Trong bất kỳ ô làm sao, nhập xúc tiến thư mục của thỏng mục mà bạn muốn liệt kê tên tệp.Trong ô vị trí bạn muốn có danh sách, hãy nhập cách làm sau: =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),””) (Tôi vẫn nhập nó vào ô A3):Copy với Paste phương pháp vào các ô dưới để có danh sách toàn bộ các tệp.
*

Lưu ý rằng tôi vẫn nhập địa điểm thỏng mục trong một ô và kế tiếp áp dụng ô đó trong phương pháp GetFileNames. Quý khách hàng cũng có thể mã cứng liên quan thỏng mục vào phương pháp nlỗi sau: =IFERROR(INDEX(GetFileNames(“C:UsersSumitDesktopTest Folder”),ROW()-2),””)

Trong phương pháp trên, Shop chúng tôi vẫn thực hiện ROW()-2 và Shop chúng tôi bước đầu trường đoản cú sản phẩm vật dụng cha trnghỉ ngơi đi. Điều này đảm bảo rằng Lúc tôi xào nấu công thức trong những ô dưới, nó sẽ tiến hành tăng thêm 1. Trong trường vừa lòng bạn cũng có thể nhập cách làm vào bậc nhất tiên của cột, bạn chỉ cần thực hiện ROW ().

Công thức này đã chuyển động như sau:

Công thức GetFileNames trả về một mảng đựng tên của toàn bộ những tệp vào thư mục.

Hàm INDEX được thực hiện để liệt kê một tên tệp cho mỗi ô, bắt đầu tự thương hiệu trước tiên.

Hàm IFERROR được thực hiện để trả về trống thế vì #REF! lỗi được hiển thị lúc một cách làm được xào luộc trong một ô cơ mà không có thêm thương hiệu 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 nuốm thể

Công thức trên vận động hoàn hảo nhất, Khi bạn muốn thừa nhận list tất cả các tên tệp xuất phát từ 1 tlỗi mục trong Excel. Nhưng nếu như bạn chỉ mong mỏi lấy thương hiệu của các tệp video hoặc chỉ các tệp Excel hoặc chỉ những thương hiệu tệp tất cả chứa một tự khóa rõ ràng.

Trong trường vừa lòng đó, bạn cũng có thể thực hiện một tính năng tương đối khác biệt. Dưới đây là mã đang chất nhận được các bạn có được tất cả các thương hiệu tệp với 1 trường đoản cú khóa ví dụ trong những số ấy (hoặc của 1 phần mở rộng cố gắng thể).

Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dyên i As Integer Dyên ổn MyFile As Object Dim MyFSO As Object Dlặng MyFolder As Object Dyên MyFiles As Object Set MyFSO = CreateObject(“Scripting.FileSystemObject”) Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDlặng 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 ReDlặng Preserve sầu Result(1 To i – 1) GetFileNamesbyExt = Result End Function

Đoạn mã trên sẽ tạo ra một hàm ‘GetFileNamesbyExt hoàn toàn có thể được thực hiện trong số bảng tính (giống như những hàm thông thường).

Hàm này có nhì đối số – vị trí thỏng mục và từ bỏ khóa mở rộng. Nó trả về một mảng các thương hiệu tệp khớp với phần mở rộng vẫn mang đến. Nếu không có phần mở rộng hoặc từ bỏ khóa được hướng đẫn, nó đang trả về tất cả những thương hiệu tệp trong tlỗi mục được hướng đẫn.

Công thức: = GetFileNamesbyExt (Vị trí thư mục trên vị trí, phần msống rộng

Nơi đặt mã

Thực hiện tại theo quá trình dưới đây để xào luộc mã này vào VB Editor.

Chuyển cho tab Developer Nhấp vào nút Visual Basic. Như vậy đang msinh sống VB Editor. Trong Trình sửa đổi VB, nhấp chuột đề xuất vào ngẫu nhiên đối tượng người tiêu dùng nào của sổ thao tác làm việc mà lại bạn đang thao tác làm việc, tiếp cận Chèn và nhấp chuột Module. Nếu các 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à dấn phím ‘R,). Nhấp đúp con chuột vào đối tượng người tiêu dùng Module với xào luộc và dán mã làm việc trên vào hành lang cửa số mã module.

Các áp dụng công dụng này

Dưới đấy là các bước để thực hiện tính năng này trong một bảng tính:

Trong ngẫu nhiên ô làm sao, nhập shop tlỗi mục của tlỗi mục mà lại bạn muốn liệt kê tên tệp. Tôi đã nhập đặc điểm 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ả những thương hiệu tệp. Tôi vẫn nhập điều này vào ô B1. Trong ô nơi bạn muốn gồm list, hãy nhập bí quyết sau =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),””) (Tôi sẽ nhập nó vào ô A3): Copy và paste the formula in the cells below to lớn get a list of all the files
*

Bất kỳ thủ thuật Excel làm sao bạn sử dụng cũng phần đông hỗ trợ cho quá trình trong cuộc sống tiện lợi.