Pobierz listę nazw plików z folderu w programie Excel (z VBA i bez)

Pierwszego dnia pracy w małej firmie konsultingowej przez trzy dni pracowałem nad krótkim projektem.

Praca była prosta.

Na dysku sieciowym było wiele folderów, a każdy folder zawierał setki plików.

Musiałem wykonać te trzy kroki:

1. Wybierz plik i skopiuj jego nazwę.
2. Wklej tę nazwę w komórce w Excelu i naciśnij Enter.
3. Przejdź do następnego pliku i powtórz kroki 1 i 2.

Brzmi prosto, prawda?

To było - proste i ogromna strata czasu.

To, co zajęło mi trzy dni, można było zrobić w kilka minut, gdybym znał odpowiednie techniki.

W tym samouczku pokażę Ci różne sposoby, aby cały ten proces był super szybki i super łatwy (z VBA i bez).

Ograniczenia metod przedstawionych w tym samouczku: Dzięki technikom pokazanym poniżej będziesz mógł uzyskać tylko nazwy plików w głównym folderze. Nie otrzymasz nazw plików w podfolderach w folderze głównym. Oto sposób na uzyskanie nazw plików z folderów i podfolderów za pomocą dodatku Power Query

Używanie funkcji PLIKI do uzyskania listy nazw plików z folderu

Słyszał o funkcja PLIKI przed?

Nie martw się, jeśli nie.

Pochodzi z dzieciństwa arkuszy kalkulacyjnych Excela (formuła wersji 4).

Chociaż ta formuła nie działa w komórkach arkusza, nadal działa w nazwanych zakresach. Wykorzystamy ten fakt, aby uzyskać listę nazw plików z określonego folderu.

Załóżmy teraz, że masz folder o nazwie - „Testuj folderna pulpicie i chcesz uzyskać listę nazw plików dla wszystkich plików w tym folderze.

Oto kroki, które dadzą ci nazwy plików z tego folderu:

1. W komórce A1 wprowadź pełny adres folderu, a następnie znak gwiazdki (*)
   • Na przykład, jeśli twój folder na dysku C, adres będzie wyglądał tak
     C:\Użytkownicy\Sumit\Pulpit\Folder testowy\*
   • Jeśli nie masz pewności, jak uzyskać adres folderu, użyj następującej metody:
     • • W folderze, z którego chcesz uzyskać nazwy plików, utwórz nowy skoroszyt programu Excel lub otwórz istniejący skoroszyt w folderze i użyj poniższej formuły w dowolnej komórce. Ta formuła poda adres folderu i doda na końcu znak gwiazdki (*). Teraz możesz skopiować i wkleić (wkleić jako wartość) ten adres w dowolnej komórce (w tym przykładzie A1) w skoroszycie, w którym chcesz uzyskać nazwy plików.

         =REPLACE(KOMÓRKA("nazwapliku")),ZNAJDŹ("[",KOMÓRKA("nazwapliku")),LEN(KOMÓRKA("nazwapliku")),"*")

         [Jeśli utworzyłeś nowy skoroszyt w folderze, aby użyć powyższej formuły i uzyskać adres folderu, możesz go usunąć, aby nie znajdował się na liście plików w tym folderze]
2. Przejdź do zakładki „Formuły” i kliknij opcję „Zdefiniuj nazwę”.
3. W oknie dialogowym Nowa nazwa użyj następujących informacji
   • Nazwa: FileNameList (możesz wybrać dowolną nazwę)
   • Zakres: skoroszyt
   • Odnosi się do: =PLIKI(Arkusz1!$A$1)
4. Teraz, aby uzyskać listę plików, użyjemy nazwanego zakresu w funkcji INDEX. Przejdź do komórki A3 (lub dowolnej komórki, w której chcesz rozpocząć listę nazw) i wprowadź następującą formułę:

   =JEŻELI.BŁĄD(INDEKS(ListaNazwPlików;WIERSZ()-2);"")

5. Przeciągnij to w dół, a otrzymasz listę wszystkich nazw plików w folderze

Chcesz wyodrębnić pliki z określonym rozszerzeniem?

Jeśli chcesz uzyskać wszystkie pliki z określonym rozszerzeniem, po prostu zmień gwiazdkę z tym rozszerzeniem pliku. Na przykład, jeśli chcesz tylko plików Excela, możesz użyć *xls* zamiast *

Tak więc adres folderu, którego musisz użyć, będzie C:\Użytkownicy\Sumit\Pulpit\Folder testowy\*xls*

Podobnie w przypadku plików dokumentów Word użyj *doc*

Jak to działa?

Formuła PLIKI pobiera nazwy wszystkich plików o określonym rozszerzeniu w określonym folderze.

W formule INDEKS podaliśmy nazwy plików jako tablicę i zwracamy nazwy plików 1., 2., 3. itd. za pomocą funkcji WIERSZ.

Zauważ, że użyłem WIER()-2, ponieważ zaczęliśmy od trzeciego rzędu. Tak więc ROW()-2 będzie równa 1 dla pierwszego wystąpienia, 2 dla drugiego wystąpienia, gdy numer wiersza wynosi 4, i tak dalej i tak dalej.

Obejrzyj wideo - pobierz listę nazw plików z folderu w programie Excel

Korzystanie z VBA Uzyskaj listę wszystkich nazw plików z folderu

Teraz muszę powiedzieć, że powyższa metoda jest nieco skomplikowana (z wieloma krokami).

Jest to jednak o wiele lepsze niż robienie tego ręcznie.

Ale jeśli nie masz nic przeciwko używaniu VBA (lub jeśli jesteś dobry w wykonywaniu dokładnych kroków, które wymienię poniżej), możesz utworzyć funkcję niestandardową (UDF), która może łatwo uzyskać nazwy wszystkich plików.

Korzyści z używania User Dwyrafinowany FFunkcja (UDF) polega na tym, że można zapisać funkcję w skoroszycie makr osobistych i łatwo jej ponownie użyć bez ciągłego powtarzania kroków. Możesz także utworzyć dodatek i udostępnić tę funkcję innym.

Teraz pozwól, że najpierw podam kod VBA, który utworzy funkcję, aby uzyskać listę wszystkich nazw plików z folderu w programie Excel.

Funkcja GetFileNames(ByVal FolderPath As String) Jako wariant Dim Wynik jako wariant Dim i jako Integer Dim MyFile jako obiekt Dim MyFSO jako obiekt Dim MyFolder jako obiekt Dim MyFiles jako obiekt Ustaw MyFSO = CreateObject("Scripting.FileSystemObject") Ustaw MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result (1 do MyFiles.Count) i = 1 Dla każdego MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Następny MyFile GetFileNames = Funkcja zakończenia wyniku

Powyższy kod utworzy funkcję GetFileNames, której można używać w arkuszach (tak jak zwykłe funkcje).

Gdzie umieścić ten kod?

Wykonaj poniższe czynności, aby skopiować ten kod w edytorze VB.

• Przejdź do zakładki Deweloper.
• Kliknij przycisk Visual Basic. Spowoduje to otwarcie edytora VB.
• W Edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, w którym pracujesz, przejdź do Wstaw i kliknij Moduł. Jeśli nie widzisz Eksploratora projektów, użyj skrótu klawiaturowego Control + R (przytrzymaj klawisz Control i naciśnij klawisz „R”).
• Kliknij dwukrotnie obiekt Module i skopiuj i wklej powyższy kod do okna kodu modułu.

Jak korzystać z tej funkcji?

Poniżej znajdują się kroki, aby użyć tej funkcji w arkuszu:

• W dowolnej komórce wprowadź adres folderu, z którego chcesz wyświetlić listę nazw plików.
• W komórce, w której chcesz wyświetlić listę, wprowadź następującą formułę (wprowadzam ją w komórce A3):

  =JEŻELIBŁĄD(INDEKS(GetFileNames($A$1);WIERSZ()-2),"")

• Skopiuj i wklej formułę w komórkach poniżej, aby uzyskać listę wszystkich plików.

Zwróć uwagę, że wprowadziłem lokalizację folderu w komórce, a następnie użyłem tej komórki w Pobierz nazwy plików formuła. Możesz także na stałe zakodować adres folderu w formule, jak pokazano poniżej:

=IFERROR(INDEKS(GetFileNames("C:\Users\Sumit\Desktop\Test Folder");ROW()-2),"")

W powyższym wzorze użyliśmy ROW()-2 i zaczęliśmy od trzeciego wiersza wzwyż. Dzięki temu, gdy skopiuję formułę do komórek poniżej, zostanie ona zwiększona o 1. Jeśli wpisujesz formułę w pierwszym wierszu kolumny, możesz po prostu użyć ROW().

Jak działa ta formuła?

Formuła GetFileNames zwraca tablicę zawierającą nazwy wszystkich plików w folderze.

Funkcja INDEX służy do wylistowania jednej nazwy pliku w komórce, zaczynając od pierwszej.

Funkcja IFERROR służy do zwracania wartości pustej zamiast #REF! błąd, który jest wyświetlany, gdy formuła jest kopiowana w komórce, ale nie ma więcej nazw plików do wyświetlenia.

Korzystanie z VBA Uzyskaj listę wszystkich nazw plików z określonym rozszerzeniem

Powyższa formuła działa świetnie, gdy chcesz uzyskać listę wszystkich nazw plików z folderu w programie Excel.

Ale co, jeśli chcesz uzyskać nazwy tylko plików wideo lub tylko plików Excela lub tylko nazw plików zawierających określone słowo kluczowe.

W takim przypadku możesz użyć nieco innej funkcji.

Poniżej znajduje się kod, który pozwoli Ci uzyskać wszystkie nazwy plików z określonym słowem kluczowym (lub określonym rozszerzeniem).

Funkcja GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Wynik 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") Ustaw MyFolder = MyFSO.GetFolder(FolderPath) Ustaw MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Dla każdego MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) 0 Then Result(i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Zachowaj wynik (1 do i - 1) GetFileNamesbyExt = funkcja zakończenia wyniku

Powyższy kod utworzy funkcję ‘Pobierz nazwy plików przezExt‘, które można wykorzystać w arkuszach (tak jak zwykłe funkcje).

Ta funkcja przyjmuje dwa argumenty - lokalizację folderu i słowo kluczowe rozszerzenia. Zwraca tablicę nazw plików pasujących do podanego rozszerzenia. Jeśli nie określono rozszerzenia ani słowa kluczowego, zwróci wszystkie nazwy plików w określonym folderze.

Składnia: =GetFileNamesbyExt("Lokalizacja folderu", "Rozszerzenie")

Gdzie umieścić ten kod?

Wykonaj poniższe czynności, aby skopiować ten kod w edytorze VB.

• Przejdź do zakładki Deweloper.
• Kliknij przycisk Visual Basic. Spowoduje to otwarcie edytora VB.
• W Edytorze VB kliknij prawym przyciskiem myszy dowolny obiekt skoroszytu, w którym pracujesz, przejdź do Wstaw i kliknij Moduł. Jeśli nie widzisz Eksploratora projektów, użyj skrótu klawiaturowego Control + R (przytrzymaj klawisz Control i naciśnij klawisz „R”).
• Kliknij dwukrotnie obiekt Module i skopiuj i wklej powyższy kod do okna kodu modułu.

Jak korzystać z tej funkcji?

Poniżej znajdują się kroki, aby użyć tej funkcji w arkuszu:

• W dowolnej komórce wprowadź adres folderu, z którego chcesz wyświetlić listę nazw plików. Wpisałem to w komórce A1.
• W komórce wprowadź rozszerzenie (lub słowo kluczowe), dla którego chcesz uzyskać wszystkie nazwy plików. Wpisałem to w komórce B1.
• W komórce, w której chcesz wyświetlić listę, wprowadź następującą formułę (wprowadzam ją w komórce A3):

  =JEŻELI.BŁĄD(INDEKS(PobierzNazwyPlikubyExt($A$1,$B$1);WIERSZ()-2);"")

• Skopiuj i wklej formułę w komórkach poniżej, aby uzyskać listę wszystkich plików.

Jak o tobie? Wszelkie sztuczki Excela, których używasz, aby ułatwić życie. Chciałbym się od ciebie nauczyć. Udostępnij to w sekcji komentarzy!