Vào ngày trước tiên lúc thao tác trong một công ty tư vấn nhỏ tuổi, tôi đã làm được bố trí một dự án nlắp trong cha ngày. Trong tệp tin công việc được giao có không ít thư mục trên ổ đĩa mạng. Mỗi tlỗi mục có hàng nghìn tệp trong đó.

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

Tôi đã đề nghị làm theo tía bước sau:

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

Nghe dường như đơn giản và dễ dàng buộc phải không? Nó thật sự dễ dàng và đơn giản với lãng phí thời hạn không nhỏ. Ttuyệt vày có tác dụng tôi mất cha ngày. Thì tôi có thể được triển khai trong vài phút ít giả dụ tôi biết những kỹ thuật cân xứng.

Trong giải đáp này, tôi vẫn chỉ cho bạn các biện pháp khác nhau. Để tạo cho tổng thể quy trình này trsống bắt buộc hết sức nkhô giòn cùng rất tiện lợi (bao gồm cùng không tồn tại VBA). Hướng dẫn này bao gồm:

Dùng công dụng FILES nhằm dấn list thương hiệu tệp tự tlỗi mục Sử dụng VBA Nhận list tất cả thương hiệu tệp trường đoản cú thư mục Sử dụng VBA Nhận list toàn bộ các thương hiệu tệp cùng với phần mở rộng thế thể

Hạn chế của những cách tiến hành được hiển thị trong hướng dẫn này:

Với những kỹ thuật được hiển thị tiếp sau đây, bạn sẽ chỉ có thể nhận thấy các thương hiệu tệp trong tlỗi mục thiết yếu. quý khách hàng sẽ không còn nhận thấy tên của các tệp trong số thư mục bé vào tlỗi mục chủ yếu. Đây là cách để mang thương hiệu tệp trường đoản cú tlỗi mục cùng thư mục con bằng Power nguồn Query

Dùng tính năng FILES nhằm nhấn danh sách thương hiệu tệp từ bỏ thỏng mục

Mặc cho dù bí quyết này không chuyển động trong những ô của bảng tính. Nhưng nó vẫn chuyển động trong những phạm vi chọn cái tên. Chúng tôi vẫn sử dụng thực tiễn này để lấy list tên tệp xuất phát từ một tlỗi mục được chỉ định.

Bây tiếng, giả sử bạn bao gồm một tlỗi mục có tên-Folder. Tlỗi mục đánh giá bên trên màn hình hiển thị nền với bạn muốn nhận list thương hiệu tệp mang lại toàn bộ các tệp vào thư mục này. Dưới đây là công việc vẫn cung ứng cho chính mình tên tệp từ thỏng mục này:

1. Trong ô A1, nhập liên tưởng rất đầy đủ của thỏng mục theo sau là dấu hoa thị (*)

Ví dụ: Nếu tlỗi mục của công ty trong ổ C, thì cửa hàng đang nhỏng sau: C: Users Sumit Desktop Tlỗi mục thí nghiệm *

Nếu chúng ta không chắc chắn là làm cho nắm nào để lấy ảnh hưởng tlỗi mục, hãy áp dụng cách thức sau:

Trong thư mục mà bạn có nhu cầu lấy tên tệp. Hãy tạo Sổ làm việc Excel new hoặc mnghỉ ngơi sổ thao tác làm việc hiện gồm. Trong tlỗi mục cùng áp dụng công thức:=REPLACE(CELL(“filename”),FIND(<“,CELL(“filename”)),LEN(CELL(“filename”)),”*”).

Tvào bất kỳ ô như thế nào. Công thức này đã cung ứng cho bạn thúc đẩy thư mục cùng thêm vết hoa thị (*) nghỉ ngơi cuối. Bây giờ đồng hồ chúng ta cũng có thể sao chép dán(paste as value). Địa chỉ này vào bất kỳ ô như thế nào (A1 trong những ví dụ này) trong sổ làm việc cơ mà bạn có nhu cầu tên tệp.

2. Đi tới tab ‘Formulas’ cùng lựa chọn ‘Define Name’.

*

3. Trong hộp thoại Tên new, áp dụng những cụ thể sau

Tên: FileNameList (dễ chịu chọn ngẫu nhiên tên nào chúng ta thích) Phạm vi: Workbook Tsi mê chiếu đến:=FILES(Sheet1!$A$1)

*

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

Cửa Hàng chúng tôi đã thực hiện phạm vi được đặt tên vào hàm INDEX. Chuyển cho ô A3 (hoặc bất kỳ ô làm sao bạn có nhu cầu danh sách tên bắt đầu) với nhập bí quyết 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 toàn bộ các tên tệp trong thư mục

*
Bạn mong bung file tập tin cùng với 1 phần không ngừng mở rộng gắng thể?

Nếu bạn có nhu cầu dìm toàn bộ các tệp có phần mở rộng cụ thể. Chỉ cần thay đổi vết hoa thị cùng với phần mở rộng tệp kia. Ví dụ: nếu như bạn chỉ ý muốn những tệp excel, bạn có thể thực hiện * xls * rứa vì *. lúc kia, can hệ thỏng mục bạn phải thực hiện vẫn là: C:UsersSumit DesktopTest Directory*xls*.

Tương từ, đối với những tệp tư liệu từ, sử dụng * doc *

Cách tiến hành chuyển động này

Công thức FILES mang thương hiệu của toàn bộ các tệp của app không ngừng mở rộng sẽ chỉ định và hướng dẫn vào thư mục vẫn hướng dẫn và chỉ định.

Trong công thức INDEX, công ty chúng tôi vẫn viết tên tệp là mảng cùng chúng tôi trả về tên 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 sẽ áp dụng ROW () – 2, Lúc công ty chúng tôi bắt đầu tự mặt hàng đồ vật bố trsinh hoạt đi. Vì vậy, ROW()-2 sẽ là một trong cho ngôi trường hòa hợp thứ nhất, 2 mang lại trường thích hợp đồ vật nhị Khi số sản phẩm là 4, v.v.

Sử dụng VBA dấn danh sách toàn bộ tên tệp trường đoản cú thỏng mục

Bây tiếng, tôi đề xuất nói rằng cách thức bên trên hơi tinh vi (cùng với một số trong những bước). Tuy nhiên, giỏi rộng các so với làm điều đó bằng tay.

Nhưng trường hợp chúng ta có thể áp dụng VBA (hoặc nếu khách hàng tốt theo các bước đúng đắn mà tôi đã liệt kê bên dưới). quý khách hàng rất có thể tạo một công dụng thiết lập cấu hình (UDF) rất có thể thuận lợi rước cho chính mình thương hiệu của toàn bộ các tệp.

Lợi ích của bài toán sử dụng Hàm vì chưng người dùng xác minh (UDF). Quý Khách có thể lưu giữ hàm trong sổ làm việc macro cá thể và thực hiện lại thuận lợi cơ mà không cần lặp đi tái diễn quá trình. quý khách cũng có thể chế tác một hỗ trợ cùng share tính năng này với những người dân khác.

Bây giờ đồng hồ hãy nhằm tôi cung cấp cho mình mã VBA. Sẽ chế tạo ra một hàm để đưa danh sách tất cả các tên tệp xuất phát từ một thỏng mục vào Excel.

Function GetFileNames(ByVal FolderPath As String) As VariantDyên Result As VariantDyên i As IntegerDim MyFile As ObjectDlặng 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ã bên trên sẽ khởi tạo ra một hàm GetFileNames hoàn toàn có thể được áp dụng trong số bảng tính (giống hệt như những hàm thông thường).

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

Thực hiện theo các bước sau đây để coppy mã này trong VB Editor. Chuyển mang lại tab Developer.
*
Nhấp vào nút Visual Basic. Như vậy đang mnghỉ ngơi VB Editor.
*

Trong trình sửa đổi VB. Bấm loài chuột bắt buộc vào ngẫu nhiên đối tượng người sử dụng như thế nào của sổ làm việc mà nhiều người đang thao tác làm việc. Đi tới Insert với bấm vào Module . Nếu các bạn ko rất có thể thấy Project Explorer. Hãy thực hiện phím tắt Control + R (duy trì phím điều khiển với nhận phím ‘R,).

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

Cách sử dụng tính năng này

Dưới đây là công việc nhằm thực hiện chức năng này trong một bảng tính:

Trong bất kỳ ô làm sao, nhập địa chỉ thỏng mục của tlỗi mục nhưng mà bạn muốn liệt kê tên tệp.Trong ô nơi bạn có nhu cầu gồm list, 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à Paste cách làm vào các ô dưới để có list tất cả những tệp.
*

Lưu ý rằng tôi vẫn nhập địa chỉ thỏng mục trong một ô và tiếp nối áp dụng ô kia trong bí quyết GetFileNames. quý khách cũng rất có thể mã cứng ảnh hưởng thư mục vào bí quyết nlỗi sau: =IFERROR(INDEX(GetFileNames(“C:UsersSumitDesktopTest Folder”),ROW()-2),””)

Trong cách làm bên trên, công ty chúng tôi sẽ sử dụng ROW()-2 với Cửa Hàng chúng tôi bắt đầu trường đoản cú sản phẩm trang bị bố trnghỉ ngơi đi. Điều này bảo đảm rằng khi tôi coppy công thức trong số ô dưới, nó sẽ được tạo thêm 1. Trong trường thích hợp bạn có thể nhập công thức vào số 1 tiên của cột, bạn chỉ cần thực hiện ROW ().

Công thức này vẫn vận động nlỗi sau:

Công thức GetFileNames trả về một mảng cất tên của toàn bộ các tệp trong tlỗi mục.

Hàm INDEX được thực hiện nhằm liệt kê một thương hiệu tệp cho từng ô, ban đầu từ thương hiệu thứ nhất.

Hàm IFERROR được sử dụng nhằm trả về trống cầm vì #REF! lỗi được hiển thị lúc một công thức được sao chép vào một ô nhưng mà không tồn tại thêm thương hiệu tệp nhằm liệt kê.

Sử dụng VBA dìm list toàn bộ những tên tệp cùng với phần không ngừng mở rộng cầm thể

Công thức trên hoạt động hoàn hảo và tuyệt vời nhất, Lúc bạn có nhu cầu dấn danh sách tất cả các thương hiệu tệp xuất phát điểm từ một thỏng mục trong Excel. Nhưng nếu bạn chỉ ý muốn rước tên của những tệp video hoặc chỉ những tệp Excel hoặc chỉ các thương hiệu tệp bao gồm đựng một từ khóa rõ ràng.

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

Function GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dyên i As Integer Dyên MyFile As Object Dyên ổn MyFSO As Object Dim 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 Result(1 To 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 thực hiện trong số bảng tính (y như những hàm thông thường).

Hàm này có hai đối số – địa điểm thỏng mục cùng từ bỏ khóa không ngừng 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 vẫn đến. Nếu không có phần mở rộng hoặc từ khóa được chỉ định, nó vẫn trả về tất cả những thương hiệu tệp trong tlỗi mục được chỉ định.

Công thức: = GetFileNamesbyExt (Vị trí tlỗi mục trên vị trí, phần mở rộng

Nơi đặt mã

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

Chuyển mang lại tab Developer Nhấp vào nút ít Visual Basic. Vấn đề này vẫn mnghỉ ngơi VB Editor. Trong Trình sửa đổi VB, click chuột đề xuất vào ngẫu nhiên đối tượng người sử dụng như thế nào của sổ thao tác làm việc cơ mà nhiều người đang làm việc, tiếp cận Cnhát với bấm vào Module. Nếu bạn không rất có thể thấy Project Explorer, hãy thực hiện phím tắt Control + R (duy trì phím điều khiển cùng nhận phím ‘R,). Nhấp lưu ban loài chuột vào đối tượng người tiêu dùng Module với sao chép và dán mã nghỉ ngơi trên vào hành lang cửa số mã module.

Xem thêm: Mẹo Tạo Tài Khoản App Store Không Cần Visa, Mastercard Nhanh Nhất Hiện Nay

Các sử dụng công dụng này

Dưới đấy là các bước để áp dụng công dụng này trong một bảng tính:

Trong bất kỳ ô làm sao, nhập shop thư mục của thư mục mà lại bạn muốn liệt kê thương hiệu tệp. Tôi đã nhập tính năng này vào ô A1. Trong một ô, nhập phần mở rộng (hoặc từ bỏ khóa) nhưng mà bạn có nhu cầu toàn bộ những tên tệp. Tôi vẫn nhập cái này vào ô B1. Trong ô địa điểm bạn muốn bao gồm danh sách, hãy nhập cách làm sau =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),””) (Tôi đã 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ỳ mẹo nhỏ Excel như thế nào bạn áp dụng cũng mọi giúp cho quá trình trong cuộc sống đời thường dễ dãi.