Lista plików z katalogu za pomocą funkcji makr 4.0 — porada #305

utworzone przez Adam Kopeć | lis 7, 2017 | Excel | 0 komentarzy

Przed nami niezwykle trudne zadanie – potrzebujemy zrobić w Excelu listę wszystkich plików ze wskazanego katalogu. Spokojnie nie będzie, aż tak źle. Nawet nie użyjemy VBA, a przynajmniej nie zajrzymy do edytora VBA i nie będziemy tworzyć, żadnych nowych makr.

Rys. 1 — Lista plików z katalogu

Technika, na wypisanie wszystkich plików ze wskazanego katalogu, jaką chcę Ci przedstawić opiera się na funkcjach makr 4.0.
Informacja o nich trafiła do mnie w przeciągu miesiąca z dwóch źródeł.
1) Ze strony https://www.myonlinetraininghub.com/, z której dostałem olbrzymi plik o funkcjach makr 4.0
2) Oraz ze strony https://trumpexcel.com/ gdzie był opisany przedstawiony problem wypisania wszystkich plików ze wskazanego folderu:

Na podstawie tego problemu chcę Ci przybliżyć sposób działania funkcji makr 4.0 i opowiedzieć o trudnościach, z nimi związanymi, jakie czekają polskiego użytkownika Excela (i innych nie angielskich narodowości). 

Pierwsza trudność jest taka, że nie możesz ich wpisać bezpośrednio w komórki Excela. Może znasz funkcję DATA.RÓŻNICA, której Excel nie podpowiada, że istnieje, ale jak wpiszesz ją w komórkę Excela to ona działa. Funkcje makr 4.0 nie działają w komórkach arkusza Excela.

Żeby z nich skorzystać, chyba najprostszym sposobem, jest stworzenie na ich podstawie nazwy (Menadżer nazw karta dane).

Rys. 2 — Menadżer nazw

Przy tworzeniu tej nazwy pojawia się kolejny problem, bo ciężko znaleźć nazwę tych funkcji po polsku (ogólnie w innym języku niż angielski). Źródła, które podałem na początku podają potrzebną nam funkcję w języku angielskim – nazywa się FILES. Ale jeśli spróbujemy z niej skorzystać polski Excel jej nie rozpozna. Potrzebujemy jej nazwę po polsku. W wielu przypadkach zadziała bezpośrednie tłumaczenie – tak mi się udało znaleźć polski odpowiednik dla tej funkcji – PLIKI. O innych sposobach pomówimy na koniec wpisu. Teraz zajmiemy się rozwiązaniem postawionego problemu.

Wiemy już jak funkcja nazywa się po polsku (PLIKI), ale nie znamy jej składni. Excel nam jej nie podpowie. Na szczęście składnia tej funkcji jest prosta – wystarczy, że podasz pełną ścieżkę do katalogu, z którego chcesz uzyskać listę plików (pamiętaj na jej końcu dodać gwiazdkę, która oznacza, że bierzemy wszystko):

C:\Users\PC\Documents\Pliki\*

Ścieżkę do katalogu najlepiej, żebyś wstawił do komórki Excela, a dopiero w funkcji makr 4.0 PLIKI się do niej odwoływał. Ułatwi Ci to jej modyfikację.

rys. 3 – Wstawiona ścieżka do komórki A2

Czasami może być ciężko odnaleźć dokładną ścieżkę katalogu, bo nie widać jej na pierwszy rzut oka, ale wystarczy, że w ekspoatora Windowsa i klikniesz na jego górę, gdzie widać ścieżkę w formie bardziej graficznej.

rys. 4 – eksplorator windows i kopiowanie ścieżki

Wyświetli Ci się wtedy pełna ścieżka (nawet jeśli jesteś w swoich dokumentach) i będziesz mógł ją łatwo skopiować.
Mamy ścieżkę, mamy nazwę funkcji, więc możemy ją w końcu stworzyć w Menadżerze nazw (Ctrl + F3). Naciskamy przycisk nowy i przechodzimy do okna tworzenia nazwy:

rys. 5 – nowa nazwa

Jak stworzyliśmy nazwę możemy się do niej odwoływać w komórkach Excela. Ponieważ funkcja PLIKI zwraca listę, więc nie możemy jej bezpośrednio wstawić do komórki Excela, bo zobaczymy tylko pierwszą wartość (pierwszy plik) z listy. Na szczęście wystarczy skorzystać z funkcji INDEKS, której będziemy mówić, który element z listy ma wyświetlać. Najczęściej w podobnych sytuacjach korzystam z funkcji ILE.WIERSZY i dynamicznego zakresu danych, który się rozrasta jak przeciągamy formułę:

=INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4))

rys. 6 – Lista plików za pomocą funkcji

Jeśli przeciągniemy formułę wystarczająco daleko w dół zaczną się pojawiąć błędy adresu – #ADR!, ponieważ nie ma już elementów na liście. Najprościej sobie z tym poradzić wstawiając naszą formułę do funkcji JEŻELI.BŁĄD, które karzemy zwracać pusty ciąg znaków (dwa podwójne cudzysłowy), jeśli wyjdziemy poza listę.

=JEŻELI.BŁĄD(INDEKS(WszystkiePliki;ILE.WIERSZY($A$4:A4));"")

rys. 7 – jeżeli.błąd

Możemy nawet policzyć ilość pliku w katalogu (w stworzonej przez nas nazwie) np. za pomocą funkcji ILE.NIEPUSTYCH:

=ILE.NIEPUSTYCH(WszystkiePliki)

Udało nam się rozwiązać postawione na początku zadanie i dochodzimy do zapisywania pliku. Pojawia się kolejny problem — funkcje makr 4.0, to jednak makra i skoroszyt je zawierający musi być zapisany w formacie, który obsługuje makra.

rys. 8 – błąd przy zapisywaniu pliku z funkcjami makr 4.0

Na koniec wpisu warto wspomnieć, że przy funkcji PLIKI działają proste filtry, a dokładniej symbole wieloznaczne. Musieliśmy napisać na końcu ścieżki gwiazdkę (rys. 3), żeby pobrać wszystkie pliki. W podobny sposób możemy ograniczyć wybór plików z katalogu tylko do plików Excela ustawiając końcówkę:

C:\Users\PC\Documents\Pliki\*.xls*

Końcówka (*.xls*) oznacza, że nazwa pliku może zaczynać się od dowolnego ciągu znaków, po którym pojawia się ciąg tekstu ‘.xls’, a za nim może się jest coś pojawić, ale nie musi (symbol wieloznaczny gwiazdka oznacza dowolny ciąg tekstowy – nawet pusty). Efekty tak nałożonego filtru widać od razu w wynikach:

rys. 9 – przefiltrowane pliki

Drugim, oprócz gwiazdki, symbolem wieloznacznym w Excelu jest znak zapytania ‘?’ – zastępuje on dowolny pojedynczy znak.
Mieliśmy problem z odnalezieniem nazwy funkcji makr 4.0 po polsku, ale mamy źródła, które podają te nazwy po angielsku. Wystarczy, że zmienimy język w Excelu na angielski (Menu Plik -> Opcje -> zakładka Język), zapiszemy plik z angielską nazwą pliku, a następnie zmienimy język Excela na polski i ponownie otworzymy plik – teraz już z polską nazwą funkcji.
Dodanie nowego języka do Excela różni się w zależności od wersji Excela, którą masz, dlatego podaję link do strony Microsoftu dokładnie opisujący proces w zależności od Twojej wersji Excela.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP