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

     

Vào ngày đầu tiên khi thao tác làm việc vào một công ty support bé dại, tôi đã được sắp xếp một dự án nđính trong cha ngày. Trong file quá trình được giao có khá nhiều thư mục trên ổ đĩa mạng. Mỗi thư mục bao gồm hàng ngàn tệp trong các số ấy.

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

Tôi vẫn cần làm theo cha bước sau:

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

Nghe có vẻ đơn giản và dễ dàng nên không? Nó thật sự đơn giản và dễ dàng và lãng phí thời gian không hề nhỏ. Thay bởi làm cho tôi mất bố ngày. Thì tôi rất có thể được triển khai vào vài ba phút nếu như tôi biết các kỹ thuật tương xứng.

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

Dùng tác dụng FILES để nhận list thương hiệu tệp tự tlỗi mục Sử dụng VBA Nhận list tất cả tên tệp từ bỏ tlỗi mục Sử dụng VBA Nhận list tất cả những thương hiệu tệp với phần mở rộng vắt thể

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

Với các chuyên môn được hiển thị dưới đây, các bạn sẽ chỉ hoàn toàn có thể nhận ra các tên tệp vào thỏng mục chính. quý khách sẽ không còn nhận ra tên của các tệp trong các tlỗi mục bé vào thư mục chủ yếu. Đây là cách để đem thương hiệu tệp tự tlỗi mục với thư mục bé bằng Power nguồn Query

Dùng công dụng FILES để thừa nhận list thương hiệu tệp từ thư mục

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

Bây tiếng, mang sử các bạn bao gồm một tlỗi mục bao gồm tên-Folder. Thỏng mục khám nghiệm trên màn hình hiển thị nền và bạn có nhu cầu nhấn list tên tệp mang đến tất cả những tệp trong thư mục này. Dưới đây là công việc đã cung cấp cho mình tên tệp tự thỏng mục này:

1. Trong ô A1, nhập liên hệ không hề thiếu của thỏng mục theo sau là lốt hoa thị (*)

Ví dụ: Nếu thỏng mục của người tiêu dùng vào ổ C, thì thúc đẩy sẽ nlỗi sau: C: Users Sumit Desktop Thỏng mục phân tách *

Nếu các bạn ko chắc chắn làm cho nạm làm sao để mang cửa hàng thư mục, hãy sử dụng phương thức sau:

Trong thỏng mục nhưng bạn muốn rước tên tệp. Hãy sản xuất Sổ làm việc Excel mới hoặc mở sổ thao tác làm việc hiện nay gồm. Trong tlỗi mục cùng 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 sẽ cung ứng cho mình liên tưởng thỏng mục với thêm vệt hoa thị (*) làm việc cuối. Bây tiếng bạn 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 ví dụ này) trong sổ làm việc mà bạn muốn thương hiệu tệp.

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

*

3. Trong vỏ hộp thoại Tên new, thực hiện những cụ thể sau

Tên: FileNameList (dễ chịu và thoải mái chọn ngẫu nhiên thương hiệu nào chúng ta thích) Phạm vi: Workbook Tham mê chiếu đến:=FILES(Sheet1!$A$1)

*

4. Bây giờ để có được list những tệp

công ty chúng tôi đang sử dụng phạm vi được lấy tên vào hàm INDEX. Chuyển mang lại ô A3 (hoặc bất kỳ ô nào bạn muốn list tên bắt đầu) cùng nhập bí quyết sau: =IFERROR(INDEX(FileNameList,ROW()-2),””)

5. Kéo xuống và nó vẫn cung cấp cho bạn một danh sách toàn bộ các tên tệp trong thỏng mục

*
Bạn muốn giải nén tập tin với 1 phần không ngừng mở rộng cầm thể?

Nếu bạn muốn nhận tất cả những tệp gồm phần không ngừng mở rộng cụ thể. Chỉ yêu cầu chuyển đổi vệt hoa thị với phần không ngừng mở rộng tệp đó. Ví dụ: nếu khách hàng chỉ muốn những tệp excel, bạn cũng có thể thực hiện * xls * cầm cố do *. khi đó, hệ trọng thư mục bạn phải sử dụng đã là: C:UsersSumit DesktopTest Directory*xls*.

Tương trường đoản cú, đối với các tệp tư liệu từ, thực hiện * doc *

Cách triển khai hoạt động này

Công thức FILES rước tên của tất cả những tệp của phầm mềm không ngừng mở rộng đã chỉ định và hướng dẫn vào tlỗi mục đang hướng đẫn.

Trong công thức INDEX, chúng tôi sẽ đánh tên tệp là mảng cùng Shop chúng tôi trả về thương hiệu tệp trước tiên, 2, 3, v.v. bằng phương pháp áp dụng hàm ROW.

Lưu ý rằng tôi vẫn thực hiện ROW () – 2, lúc Cửa Hàng chúng tôi bước đầu từ hàng máy cha trlàm việc đi. Vì vậy, ROW()-2 đã là 1 trong đến ngôi trường đúng theo thứ nhất, 2 cho ngôi trường hợp thiết bị nhì Lúc số hàng là 4, v.v.

Sử dụng VBA nhận danh sách toàn bộ thương hiệu tệp trường đoản cú tlỗi mục

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

Nhưng nếu như bạn cũng có thể áp dụng VBA (hoặc nếu như bạn xuất sắc theo các bước chính xác mà lại tôi vẫn liệt kê bên dưới). quý khách có thể tạo ra một công dụng thiết lập (UDF) rất có thể thuận tiện đem cho bạn tên của toàn bộ những tệp.

Lợi ích của việc áp dụng Hàm vì người dùng xác minh (UDF). quý khách hàng có thể lưu giữ hàm vào sổ thao tác macro cá thể và sử dụng lại dễ ợt cơ mà ko buộc phải lặp đi tái diễn quá trình. quý khách cũng có thể chế tạo một bổ trợ cùng chia sẻ chức năng này với những người không giống.

Bây giờ hãy nhằm tôi cung ứng cho mình mã VBA. Sẽ tạo nên một hàm để đưa list toàn bộ những thương hiệu tệp xuất phát từ một thư mục trong Excel.

Function GetFileNames(ByVal FolderPath As String) As VariantDlặng Result As VariantDim i As IntegerDyên ổn MyFile As ObjectDyên ổn MyFSO As ObjectDlặng MyFolder As ObjectDyên MyFiles As ObjectSet MyFSO = CreateObject("Scripting.FileSystemObject")Set MyFolder = MyFSO.GetFolder(FolderPath)Set MyFiles = MyFolder.Files ReDyên ổ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ẽ tạo ra một hàm GetFileNames có thể được sử dụng trong các bảng tính (y hệt như các hàm thông thường).

Xem thêm: Hệ Thống Đặt Vé Bóng Đá Online, Uy Tín Tại Thị Trường Việt Nam

Nơi đặt các mã này

Thực hiện nay theo quá trình tiếp sau đây để coppy mã này trong VB Editor. Chuyển mang lại tab Developer.
*
Nhấp vào nút ít Visual Basic. Điều này sẽ msinh hoạt VB Editor.
*

Trong trình sửa đổi VB. Bnóng loài chuột yêu cầu vào ngẫu nhiên đối tượng nào của sổ làm việc nhưng mà bạn đang làm việc. Đi cho tới Insert với bấm vào Module . Nếu chúng ta ko rất có thể thấy Project Explorer. Hãy sử dụng phím tắt Control + R (duy trì phím tinh chỉnh và dấn phím ‘R,).

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

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

Dưới đây là quá trình nhằm sử dụng tác dụng này vào một bảng tính:

Trong ngẫu nhiên ô như thế nào, nhập hệ trọng tlỗi mục của tlỗi mục mà lại bạn muốn liệt kê tên tệp.Trong ô chỗ bạn có nhu cầu có list, hãy nhập phương pháp sau: =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),””) (Tôi đang nhập nó vào ô A3):Copy và Paste cách làm vào các ô dưới để có list tất cả các tệp.
*

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

Trong công thức trên, Shop chúng tôi đã thực hiện ROW()-2 và chúng tôi ban đầu từ mặt hàng máy cha trngơi nghỉ đi. Điều này bảo đảm rằng Lúc tôi xào luộc cách làm trong các ô bên dưới, nó sẽ được tạo thêm 1. Trong ngôi trường phù hợp chúng ta có thể nhập bí quyết vào số 1 tiên của cột, các bạn chỉ việc áp dụng ROW ().

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

Công thức GetFileNames trả về một mảng cất tên của tất cả các tệp vào thư mục.

Hàm INDEX được sử dụng nhằm liệt kê một thương hiệu tệp cho mỗi ô, ban đầu từ bỏ tên trước tiên.

Hàm IFERROR được áp dụng để trả về trống nạm bởi #REF! lỗi được hiển thị khi một bí quyết được xào nấu vào một ô dẫu vậy không có thêm tên tệp nhằm liệt kê.

Sử dụng VBA nhấn danh sách tất cả các thương hiệu tệp cùng với phần mở rộng ráng thể

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

Trong ngôi trường đúng theo đó, chúng ta cũng có thể thực hiện một công dụng khá khác nhau. Dưới đây là mã vẫn cho phép các bạn đã đạt được toàn bộ những thương hiệu tệp với 1 tự khóa rõ ràng trong những số đó (hoặc của một phần không ngừng mở rộng cố thể).

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

Hàm này có nhì đối số – địa điểm tlỗi mục với trường đoản cú khóa không ngừng 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 sẽ mang lại. Nếu không có phần mở rộng hoặc tự khóa được hướng dẫn và chỉ định, nó đang 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í thỏng mục tại nơi, phần msinh sống rộng

Nơi đặt mã

Thực hiện theo các bước sau đây nhằm xào nấu mã này vào VB Editor.

Chuyển mang lại tab Developer Nhấp vào nút Visual Basic. Vấn đề này đã msinh hoạt VB Editor. Trong Trình sửa đổi VB, nhấp chuột phải vào bất kỳ đối tượng làm sao của sổ thao tác nhưng mà nhiều người đang thao tác làm việc, tiếp cận Ckém cùng nhấn vào Module. Nếu chúng ta không rất có thể thấy Project Explorer, hãy áp dụng phím tắt Control + R (giữ lại 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 sử dụng Module cùng xào luộc với dán mã nghỉ ngơi bên trên vào cửa sổ mã module.

Các áp dụng tính năng này

Dưới đây là các bước nhằm sử dụng công dụng này vào một bảng tính:

Trong ngẫu nhiên ô nào, nhập liên tưởng thư mục của thỏng mục nhưng mà bạn có nhu cầu liệt kê tên tệp. Tôi vẫn nhập tính năng này vào ô A1. Trong một ô, nhập phần không ngừng mở rộng (hoặc từ bỏ khóa) mà lại bạn muốn tất cả những tên tệp. Tôi sẽ nhập điều này vào ô B1. Trong ô địa điểm bạn muốn có danh sách, hãy nhập bí quyết 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 to lớn get a danh mục of all the files
*

Bất kỳ thủ pháp Excel nào các bạn sử dụng cũng phần lớn hỗ trợ cho công việc trong cuộc sống thường ngày dễ dàng.