W tym poście pokażemy, jak stworzyć listę hiperłączy do plików znajdujących się w konkretnym folderze przy użyciu makr.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1. Ważne jest, że jeżeli chcemy korzystać z tej funkcji, musimy stworzyć plik z rozszerzeniem .xlsm, czyli taki, który obsługuje makra.
Przede wszystkim musimy znać polską wersję funkcji, której chcemy użyć (funkcja PLIKI). Jeśli tej funkcji podamy ścieżkę dostępu i zakończymy to znakiem gwiazdki (*) lub odpowiednim rozszerzeniem, to będzie ona pobierać pliki z tego katalogu. Odpowiednie połączenie formuł zapewni nam zbudowanie hiperłączy.
Mamy kilka obostrzeń, jeśli chodzi o korzystanie z tych funkcji. Funkcja nazywa się PLIKI, ale jeśli w komórce zaczniemy pisać słowo "pliki" to takiej funkcji nie znajdziemy, co widać na rys. nr 2.
Tą funkcję możemy zbudować tylko w Menedżerze nazw. W tym celu wybieramy polecenie Menedżer nazw z karty Formuły (rys. nr 3). Możemy również użyć skrótu klawiszowego Ctrl+F3.
Otworzy nam się okno Menedżera nazw, gdzie możemy sobie zbudować funkcję PLIKI, która odwołuje się do komórki A2, czyli do ścieżki dostępu. Na tej podstawie funkcja zwraca listę plików z tego katalogu (rys. nr 4).
Jak widać na rysunku powyżej nazwaliśmy tą funkcję WszystkiePliki. Korzystając z tego, że używamy Excela tablicowego, po wpisaniu funkcji WszystkiePliki i jej zatwierdzeniu, funkcja zwróci nam listę wszystkich plików (rys. nr 5).
Jak widać na rysunku powyżej domyślnie lista ta jest zwracana w poziomie, w jednym wierszu, a nie w kolumnie. W Excelu tablicowym wystarczyłoby dołożyć funkcję TRANSPONUJ, żeby wyniki otrzymać w pionie, czyli w jednej kolumnie.
Zakładamy jednak, że jesteśmy we wcześniejszej wersji Excela i chcemy wyciągnąć takie informacje jak widać na rys. nr 1. W komórce A5 wpisujemy nazwę funkcji WszystkiePliki. Wiemy, że ona rozsuwa wyniki w poziomie, a my chcemy otrzymać je w pionie. W Klasycznym Excelu nie mamy możliwości transponowania wyników ani rozlewania formuły. Dlatego musimy użyć funkcji INDEKS. Pierwszym argumentem funkcji jest tablica, czyli wyniki z funkcji WszystkiePliki. Kolejny argument to nr_wiersza, czyli wpisujemy wartość 1. Zapis formuły powinien wyglądać następująco:
=INDEKS(WszystkiePliki;1)
Po zatwierdzeniu formuły otrzymamy nazwę jednego pliku przedstawioną na rys. nr 6.
Podając w funkcji kolejne numery wiersza możemy otrzymać zwrócone kolejne elementy/pliki. Możemy takie podawanie poszczególnych numerów wierszy zastąpić funkcją ILE.WIERSZY. Pierwszym argumentem funkcji jest tablica, czyli zakres od komórki $A$5 zablokowanej bezwzględnie do komórki A5 niezablokowanej. Jak będziemy formułę kopiować w dół, to komórka A5 nie zablokowana bezwzględnie będzie się przesuwać i tym samym zwracać kolejne numery wierszy. Zapis formuły powinien wyglądać następująco:
=INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5))
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy dane przedstawione na rys. nr 7.
Jeśli w ilości skopiowanych komórek w dół przekroczymy ilość elementów w folderze, to Excel zwróci nam błąd adresu #ADR!. Mamy wyciągnięte wszystkie pliki, które znajdują się w tym katalogu. Aby zbudować hiperłącze, potrzebujemy ścieżki dostępu do tych plików (C:\tmp\Obrazki dla Hani\*). Wtedy możemy połączyć nasze dane wklejając skopiowany adres po znaku =. Jako, że jest to tekst, musimy go wstawić w podwójnych cudzysłowach. Następnie połączyć wstawiony adres z resztą formuły za pomocą znaku ampersand (&). Zapis formuły powinien wyglądać następująco:
="C:\tmp\Obrazki dla Hani\"& INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5))
Po zatwierdzeniu powyższej formuły i skopiowaniu jej na komórki poniżej otrzymamy kompletne ścieżki dostępu do każdego z plików (rys. nr 8).
Po otrzymaniu kompletnych ścieżek dostępu pozostaje nam dołożenie funkcji HIPERŁĄCZE, której argumentem jest łącze_lokalizacja, czyli wynik naszej poprzedniej formuły. Jeśli chcemy, możemy dodać opcjonalny argument, czyli przyjazna_nazwa. Zapis formuły powinien wyglądać następująco:
=HIPERŁĄCZE("C:\tmp\Obrazki dla Hani\"& INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5)))
Po zatwierdzeniu formuły i skopiowaniu jej w dół otrzymamy hiperłącza do poszczególnych elementów katalogu przedstawione na rys. nr 9.
Hiperłącza zostały również dodane dla ostatnich dwóch wierszy zawierających błędy. Aby tego uniknąć, możemy dołożyć funkcję JEŻELI.BŁĄD. Pierwszym argumentem funkcji jest wartość, czyli wynik funkcji HIPERŁĄCZE. Drugi argument funkcji to wartość_jeśli_błąd, czyli co chcemy, aby zwróciła funkcja jeśli napotka błąd – u nas pusty ciąg tekstowy (""). Zapis formuły powinien wyglądać następująco:
=JEŻELI.BŁĄD(HIPERŁĄCZE("C:\tmp\Obrazki dla Hani\"& INDEKS(WszystkiePliki;ILE.WIERSZY($A$5:A5)));"")
Po zatwierdzeniu formuły otrzymamy prawidłowe hiperłącza przedstawione na rys. nr 10.
Jeśli klikniemy w dowolne hiperłącze, Excel otworzy nam odpowiedni katalog i wyświetli wybrany obrazek (rys. nr 11).
Funkcja PLIKI umożliwia filtrowanie danych. Jeśli do adresu katalogu w komórce A2 dopiszemy rozszerzenie .png, to Excel przefiltruje dane i wyświetli tylko hiperłącza zawierające pliki z tym rozszerzeniem (rys. nr 12).
W ten sposób możemy łatwo filtrować po jednym rodzaju rozszerzenia. Niestety, żeby filtrować po większej ilości rozszerzeń, musimy zaznaczyć wybrany zakres i kliknąć prawym przyciskiem myszki na nagłówki kolumn i z podręcznego menu wybrać opcję Odkryj (rys. nr 13).
Wyświetlą nam się "dodatkowe" dane przedstawione na rys. nr 14.
Po tymi danymi są dużo bardziej skomplikowane formuły, które są potrzebne do zbudowania listy plików spełniających wybrane warunki (rys. nr 15). Tutaj niezbędne okazują się funkcje z Excela tablicowego, bo Excel klasyczny potrzebowałby dużo bardziej skomplikowanych formuł.
Jeśli zależy nam na liście wszystkich plików z katalogu lub na jednym konkretnym rodzaju plików, to funkcja PLIKI jest tutaj najlepszym rozwiązaniem.
