Author Archives: Sławomir Jabłoński

Loading
loading..

Dziś przedstawię Wam prosty sposób na stworzenie w Excelu nowej funkcjonalności i przypisanie do niej skrótu klawiszowego. W końcu każdy z nas może rozbudowywać Excela i w pewnym sensie stać się jego developerem. 🙂

Zaznaczanie wiersza
W Excelu możemy zaznaczyć cały wiersz za pomocą skrótu Shift+SPACJA lub też całą kolumnę za pomocą Ctrl+SPACJA. Zdarza się jednak, że przeglądając szereg danych chcielibyśmy przechodzić w dół z jednoczesnym zaznaczeniem wiersza. Aby to zrobić musimy użyć kombinacji najpierw strzałka w dół a następnie Shift+SPACJA. Niestety nie ma skrótu, który robiłby to za jednym zamachem. Jak stworzyć taki skrót?

Najpierw umieśćmy poniższy KOD VBA w Module w Skoroszycie Makr Osobistych. Aby to zrobić należy znaleźć plik PERSONAL (Skoroszyt makr osobistych) i umieścić tam makro. Robimy to ponieważ chcemy, aby stworzone przez nas makro było dostępne dla wszystkich plików Excela na tym komputerze.

Jak stworzyć i znaleźć skoroszyt makr osobistych (plik PERSONAL) znajdziecie tutaj:
http://heap.pl/jak-zrobic-aby-nasze-makra-byly-widoczne-w-kazdym-pliku-excela/

Należy wkleić poniższy kod do Module w pliku PERSONAL.xlsb

(Jak umieszczać kod w pliku znajdziesz we wpisie http://heap.pl/kurs-vba-cz-0-jak-zaczac-pierwsze-makro-instrukcja-dla-zielonych/)

Sub WierszWGore()
    If Selection.Row <> 1 And Selection.Rows.Count = 1 Then
        Selection.Offset(-1, 0).EntireRow.Select
    End If
End Sub

Następnie (po wklejeniu makra) chcemy nadać skrót klawiszowy do powyższej procedury. W tym celu klikamy w zakładkę Deweloper (jak włączyć na wstążce Dewelopera zobaczycie tutaj: http://heap.pl/kurs-vba-cz-0-jak-zaczac-pierwsze-makro-instrukcja-dla-zielonych/). W Deweloperze znajdujemy przycisk Makra.

1

Następnie otworzy nam się okienko Makro gdzie widzimy naszą procedurę o nazwie WierszWGore.

Zaznaczanie 1 wiersza w górę

okienko

Klikamy w przycisk Opcje…

okienko2

Tutaj możemy nadać skrót do makra. My nadamy skrót Ctrl+Shift+W, dlatego w puste miejsce wpisujemy wielką literę W (ten skrót jest najczęściej wolny, więc bez obaw możemy go wykorzystać).

okienko3

Oto w ten sposób stworzyliśmy nową funkcjonalność w Excelu i teraz wciskając kombinację klawiszy Ctrl+Shift+W zaznaczamy wiersz powyżej.

Analogicznie możemy stworzyć kolejne skróty i tak np:

Zaznaczanie 1 wiersza w dół

Sub WierszWDol()
    If Selection.Row <> Application.Rows.Count And Selection.Rows.Count = 1 Then
        Selection.Offset(1, 0).EntireRow.Select
    End If
End Sub

Następnie nadajemy skrót klawiszowy np. Ctrl+Shift+S (zgodnie z powyższym opisem – zakładka Deweloper, następnie przycisk Makra)

Zaznaczanie 1 kolumny w prawo

Sub KolumnaWPrawo()
    If Selection.Column <> Application.Columns.Count And Selection.Columns.Count = 1 Then
        Selection.Offset(0, 1).EntireColumn.Select
    End If
End Sub

Następnie nadajemy skrót klawiszowy np. Ctrl+Shift+D (zgodnie z powyższym opisem – zakładka Deweloper, następnie przycisk Makra)

Zaznaczanie 1 kolumny w lewo

Sub KolumnaWLewo()
    If Selection.Column <> 1 And Selection.Columns.Count = 1 Then
        Selection.Offset(0, -1).EntireColumn.Select
    End If
End Sub

Następnie nadajemy skrót klawiszowy np. Ctrl+Shift+A (zgodnie z powyższym opisem – zakładka Deweloper, następnie przycisk Makra)

Dodatkowe wyjaśnienia
Dlaczego wklejamy powyższy kod a nie samą linijkę:

    Selection.Offset(1, 0).EntireRow.Select

Umieszczamy instrukcję warunkową IF, aby nie pojawiał się błąd gdy np. zaznaczymy 1 wiersz i będziemy chcieli przesunąć się w górę. W tym celu instrukcja warunkowa:

If Selection.Row <> 1 Then

sprawdza czy zaznaczony wiersz jest wierszem pierwszym, ponieważ nie da się zaznaczyć wiersza zerowego (inaczej skutkowałoby to pojawieniem się błędu).

Za każdym razem sprawdzamy też czy liczba wierszy lub kolumn jest równa 1. Dlatego jednocześnie sprawdzamy 2 warunki:

If Selection.Row <> 1 And Selection.Rows.Count = 1 Then
	Selection.Offset(-1, 0).EntireRow.Select
End If

W procedurze zaznaczania wiersza poniżej sprawdzamy czy zaznaczony wiersz nie jest ostatnim wierszem w Excelu oraz czy mamy zaznaczony jeden wiersz.

If Selection.Row <> Application.Rows.Count And Selection.Rows.Count = 1 Then
	Selection.Offset(1, 0).EntireRow.Select
End If

Analogicznie postępujemy z kolumnami.

Dla tych co nie wiedzą dodam tylko, że właściwość Offset(1,0) powoduje przesunięcie zakresu (w naszym przypadku Selection, czyli zaznaczenie) o 1 wiersz w dół. Właściwość EntireRow, czyli działamy na całym wierszu a metoda Select powoduje zaznaczenie (w naszym przypadku całego wiersza, ponieważ mamy EntireRow).

Analogicznie z kolumnami. Offset(0,1) to przesunięcie o 1 kolumnę w prawo. EntireColumn oznacza, że działamy na całej kolumnie, a metoda Select powoduje, że dokonujemy zaznaczenia całej kolumny (EntireColumn).

—————————-

Tutaj znajduje się gotowy plik PERSONAL.xlsb, który w razie potrzeby można wkleić do folderu:

..\Users\NAZWA_UŻYTKOWNIKA\AppData\Roaming\Microsoft\Excel\XLSTART

Dzięki temu będziemy mogli zaznaczać:
– wiersz w górę za pomocą skrótu Ctrl+Shift+W
– wiersz w dół za pomocą Ctrl+Shift+S
– kolumna w prawo za pomocą Ctrl+Shift+D
– kolumna w lewo za pomocą Ctrl+Shift+A

Dziś napiszemy o tworzeniu makr w skoroszycie makr osobistych.
Po co to robić?
Odpowiedź: aby nasze makra były dostępne w całym Excelu na danym komputerze (dla wszystkich skoroszytów Excela).

Skoro najlepiej uczyć się poprzez praktykę, to działamy… 🙂

Najpierw znajdźmy plik Personal.xlsb (czyli skoroszyt makr osobistych).

Plik Personal.xlsb powinien znajdować się w lokalizacji:

..\Users\NAZWA_UŻYTKOWNIKA\AppData\Roaming\Microsoft\Excel\XLSTART

Jeśli nie możesz znaleźć folderu AppData to włącz pokazywanie ukrytych plików i folderów:
http://windows.microsoft.com/pl-pl/windows/show-hidden-files#show-hidden-files=windows-7

Nie masz pliku PERSONAL?
Jeśli w powyżej lokalizacji nie ma pliku Personal.xlsb to należy najpierw nagrać makro. Plik ten powstaje w momencie zarejestrowania makra. Podczas rejestracji musimy wskazać miejsce przechowywania makr na „skoroszyt makr osobistych””.

W tym celu otwórzmy sobie nowy skoroszyt Excela.

W tym momencie powinniśmy mieć otwartego Excela i aby nagrać makro należy kliknąć w lewym dolnym rogu na przycisk nagrywanie makr.

nagrywanie

W tym momencie powinno nam się otworzyć okienko Rejestrowanie makra

Rejestrowanie

W Przechowuj makro w: wybieramy opcję Skoroszyt makr osobistych. Tak jak jest to zrobione na poniższym obrazku. Ponadto możemy nadać skrót wpisując w Klawisz skrótu np. dużą literę C. Następnie klikamy OK.

W tym momencie możemy nagrywać makro, więc wszystko co teraz zrobimy w Excelu będzie nagrywane aż do momentu zatrzymania rejestrowania makra. Przykładowo zmieńmy kolor zaznaczonej komórki na czerwono i zatrzymajmy nagrywanie makra klikając w to samo miejsce gdzie włączaliśmy nagrywanie makra.

zatrzym

Udało nam się nagrać makro w skoroszycie makr osobistych, teraz możemy zamknąć plik excela. Przy zamykaniu powinno nam się pojawić okno:

okno

Kliknijmy Zapisz

W tym momencie powinien utworzyć się plik Personal.xlsb. Możemy to sprawdzić poprzez ponowne otwarcie poniższej lokalizacji (oczywiście na waszym komputerze początek ścieżki może się różnić).

..\Users\NAZWA_UŻYTKOWNIKA\AppData\Roaming\Microsoft\Excel\XLSTART

Co więcej teraz gdy otworzymy jakikolwiek plik Excela to podczas pracy możemy użyć skrótu Ctrl+Shift+C.

Właśnie zostaliśmy developerem Excela 🙂
Pozdrawiamy i do następnego wpisu 🙂

Tutaj możecie ściągnąć plik ze stworzoną dwuetapową listą wybieralną.

Lista jest stworzona tak, że w 7 wierszu (zaznaczony na jasno zielony kolor) możemy dodawać kolejne regiony, a w polu zaznaczonym szarym kolorem możemy dodawać kolejne wydziały w tych regionach.
W zależności od tego ile dodamy pól, to lista wybieralna będzie odpowiednio dostosowana.

Lista wybieralna opiera się na poprawności danych. Klikamy na komórkę E2, wchodzimy w Dane/Poprawność danych i na karcie Ustawienia wybieramy opcję Lista. Różnica między standardową listą wybieralną polega na tym, że jako źródło nie podajemy zwykłej listy a formułę.

karta developer

Jako źródło listy wybieralnej regionu nie jest podany po prostu zakres $A$7:$D$7 tylko odpowiednia formuła z wykorzystaniem funkcji PRZESUNIĘCIE – kliknij tutaj aby przejść do wpisu gdzie opisujemy funkcję.

W komórce E2 (gdzie wybieramy region), w poprawności danych jako źródło listy wybieralnej mamy wpisaną formułę:

=PRZESUNIĘCIE($A$7:$I$7;0;0;;ILE.NIEPUSTYCH($A$7:$I$7))



karta developer

Taka formuła powoduje, że gdy dopiszemy kolejne regiony w zakresie A7:I7 to pojawią się one na liście wybieralnej. Gdyby jako źródło był podany bezpośrednio zakres $A$7:$I$7, to na liście pojawiłyby się również puste pozycje. Co więcej, dzięki powyższej formule zakres ma tyle kolumn ile jest niepustych komórek w zakresie $A$7:$I$7. W funkcji PRZESUNIĘCIE jako piąty argument jest wpisana formuła ILE.NIEPUSTYCH($A$7:$I$7), dzięki czemu szerokość zakresu jest zmieniona w naszym przykładzie ma on 4 kolumny (mamy wpisane 4 regiony). Czyli zwrócony przez funkcję zakres to: $A$7:$D$7 i takie jest źródło naszej listy wybieralnej.

W kolejnej komórce, gdzie mamy wydziały (komórka E3) jako źródło listy wybieralnej wpisaliśmy formułę:

=PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1;ILE.NIEPUSTYCH(PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1)))



karta developer

Funkcja PODAJ.POZYCJĘ(E2;$A$7:$I$7;0) zwraca pozycję wybranego przez nas wyżej regionu w zakresie a7:I7. I tak np. jeśli wybierzemy Południe, to podaj pozycje zwróci nam wartość 4. Dzięki temu wiemy, że interesująca nas lista wydziałów znajduje się w kolumnie 4, czyli musimy przesunąć zakres $A$8:$A$102 o 3 kolumny (czyli pozycja Południe – 1) w prawo. Właśnie w tym celu zastosowaliśmy funkcję PRZESUNIĘCIE. Powoduje ona, że zakres $A$8:$A$102 jest przesunięty o 0 wierszy w dół i odpowiednią ilość kolumn w prawo (w przypadku południe 3 kolumny). Znów formuła ILE.NIEPUSTYCH zapewnia nam, że na liście nie będą widoczne puste elementy, czyli wielkość zakresu będzie miała tyle wierszy ile jest niepustych komórek. Ostatecznie funkcja PRZESUNIĘCIE powoduje, że w źródle listy wybieralnej odwołujemy się do zakresu $D$8:$D$11.

Mam nadzieję, że to Wam pomogło. Zapraszamy za tydzień po kolejny wpis. 🙂

Plik Excela, w którym zastosowano funkcję PRZESUNIĘCIE znajdziecie tutaj:

Funkcja PRZESUNIĘCIE zwraca odwołanie do zakresu, który jest podaną liczbą wierszy lub kolumn począwszy od komórki lub zakresu komórek. Zwrócone obszar może być zarówno komórką jak i zakresem komórek.

Funkcja PRZESUNIĘCIE potrzebuje następujących parametrów:

odwołanie – komórka lub zakres od którego zaczynamy (od którego będziemy się przesuwać)
wiersze – o ile wierszy chcemy się przesunąć od komórki początkowej (lub odwołania)
kolumny – o ile kolumn chcemy się przesunąć od komórki początkowej (lub odwołania)
wysokość – jaka ma być wysokość zwróconego obszaru w ilości wierszy (możemy zmniejszyć lub zwiększyć zakres podany w odwołaniu) – argument opcjonalny
szerokość – analogicznie jak wysokość – jaka ma być szerokość zwróconego obszaru w ilości wierszy – argument opcjonalny

PRZYKŁAD 1:

Arkusz Przesuniecie1 w pliku do ściągnięcia.

karta developer

W prezentowanym powyżej przykładzie w komórce B6 wpisaliśmy formułę:

= PRZESUNIĘCIE(A2;1;1)

Jako pierwszy argument podajemy odwołanie do komórki A2, jako drugi i trzeci wpisujemy 1, czyli przesuwamy podany w odwołaniu zakres o jeden wiersz w dół i jedną kolumnę w prawo. W konsekwencji formuła zwróci wynik Nowak, czyli odwoła się do wartości znajdującej się w komórce B3. Dzieje się tak ponieważ komórka B3 znajduje się o jeden wiersz w dół i jedną kolumnę w prawo od komórki A2.

PRZYKŁAD 2

W arkuszu Przesuniecie2 tym razem bardziej zaawansowany przykład wykorzystania funkcji PRZESUNIĘCIE, który może być przydatny podczas tworzenia raportów. Za pomocą funkcji PRZESUNIĘCIE możemy stworzyć dynamiczny wykres.

W komórce D2 stworzyliśmy listę wybieralną (DANE/POPRAWNOŚĆ DANYCH), gdzie jako źródło podajemy zakres poszczególnych miesięcy podanych w tabeli.

W D2 wybieramy odpowiedni miesiąc, dla którego chcemy zobaczyć dane i odpowiedni wykres.

karta developer

Jednak najważniejsza w naszym przykładzie jest funkcja PRZESUNIĘCIE, która jest wykorzystana w zakresie B5:B11.

=PRZESUNIĘCIE($D$31:$D$37;0;MIESIĄC($D$2))

W pierwszym argumencie podaliśmy zakres $D$31:$D$37, czyli listę nazwisk (pierwsza kolumna w tabeli danych), drugi argument jest równy 0, ponieważ nie chcemy przesuwać zakresu w dół a jedynie w prawo, aby znaleźć dane na odpowiedni miesiąc. Jako argument 3 podaliśmy funkcję Miesiąc, która zwraca numer miesiąca, który wybraliśmy z listy wybieralnej znajdującej się w komórce D2. Jeżeli wybierzemy styczeń 15, to funkcja miesiąc zwraca 1, jak marzec 15 to funkcja miesiąc zwraca 3 itd. W związku z tym w trzecim argumencie funkcji PRZESUNIĘCIE mamy wartość zależną od wybranego przez nas miesiąca, i tak jeśli wybierzemy styczeń to przesuniemy się o 1 kolumnę w prawo i odwołamy się do zakresu $E$31:$E$37, czyli do wartości ze stycznia.

Co ważne formuła jest formuła tablicową. W praktyce od zwykłej formuły różni się to tylko tym, że przed wpisaniem formuły zaznaczamy cały zakres D5:D11 a nie jedną komórkę, a przy zatwierdzaniu funkcji wciskamy Ctrl + Shift + Enter zamiast samego Entera.

W ten sposób w komórkach D5:D11 uzyskujemy odpowiednie wartości dla wybranego przez nas miesiąca.

Stworzony przez nas wykres odwołuje się do danych z zakresu C5:D11, dzięki czemu osiągnęliśmy dynamiczny wykres, który zmienia się gdy zmienimy miesiąc w komórce D2

Dla zainteresowanych w arkuszu Przesuniecie + Sortowanie znajduje się przykład gdzie dane są automatycznie sortowane

Czasami pojawiały się pytania o funkcję „oczyszczającą”, która w tekście zostawiałaby wskazane przez nas znaki a pozostałe usuwała. Po tych pytaniach postanowiliśmy umieścić taką funkcję na blogu, aby ułatwić Wam pracę.:)

Przedstawiona poniżej funkcja pozostawia w tekście znaki, które są wskazane w drugim argumencie funkcji. Jako argument możemy podać ciąg znaków i tak np. jeśli wpiszemy poniższą formułę:

=Pozostaw(„Ala ma kota!”; „ma „)

to funkcja usunie wszystkie znaki poza m, a, oraz spacją. Wynikiem działania będzie „Aa ma a”


Function Pozostaw(PrzeszukiwanyTekst As String, Znak As String) As String
    'Funkcja pozostawia w tekście znaki, które są podane w drugim argumencie
    Dim Z As String
    Dim i As Long
    Dim NumerZnaku As Long
    For i = 1 To Len(PrzeszukiwanyTekst)
        NumerZnaku = NumerZnaku + 1
        Z = Mid(PrzeszukiwanyTekst, NumerZnaku, 1)
        If Not UCase(Znak) Like "*" & UCase(Z) & "*" And Z <> "" Then
            PrzeszukiwanyTekst = Replace(PrzeszukiwanyTekst, Z, "")
            NumerZnaku = NumerZnaku - 1
        End If
    
    Next
    
    Pozostaw = PrzeszukiwanyTekst
End Function

Jeśli nie wiesz jak wkleić powyższą funkcję to zapraszamy do wpisu jak zacząć pierwsze makro.
Wystarczy skopiować i wkleić powyższy kod a już będziemy mogli korzystać z formuły Podstaw.


Inne przykłady wykorzystania funkcji Pozostaw

Funkcja pozostaw może nam się np. przydać jeśli mamy podany serię i numer dowodu, a chcemy je oddzielić.
Przykładowo: ABS123456

Chcemy jednak wyciągnąć tylko cyfry z podanego ciągu. Wystarczy, że wywołamy funkcję

Pozostaw(„ABS123456″;”0123456789”)

Oczywiście jako argumenty funkcji możemy podawać odwołania do komórek.

Omówimy sposób pobierania nazwy pliku ze ścieżki z wykorzystaniem różnych opcji.

W ostatnim wpisie poruszyliśmy kwestię pobierania ścieżki za pomocą metod: GetOpenFilename oraz GetSaveAsFilename, ale co zrobić jeśli następnie chcemy pobrać samą nazwę pliku?

Link do poprzedniego wpisu: http://heap.pl/pobieranie-sciezki-pliku/

Istnieje kilka możliwości, aby pobrać nazwę pliku ze ścieżki. Proponowane przeze mnie w tym wpisie to:

1) użycie funkcji InStrRev, wyszukanie za jej pomocą ukośnika (ostatniego od prawej strony)
2) wykorzystanie funkcji Split (podział po ukośnikach) a następnie znalezienie ostatniego elementu
3) użycie funkcji DIR

W naszym przykładzie wpisaliśmy ścieżkę do pliku postaci: „C:\Users\Documents\makro\makro.xlsx”.


1) – użycie funkcji InStrRev

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    NazwaPliku = Right(Sciezka, Len(Sciezka) - InStrRev(Sciezka, "\"))
End Sub

Funkcja Right() zwraca określoną liczbę znaków od końca ciągu tekstowego. Jako argument podajemy ścieżkę, oraz wykorzystujemy funkcję InStrRev, aby określić liczbę znaków od prawej strony. InStrRev zwraca pozycję na której znajduje się ostatni ukośnik.

Jeśli chcemy usunąć rozszerzenie, to wystarczy użyć funkcji Mid()

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    NazwaPliku = Mid(Sciezka, InStrRev(Sciezka, "\") + 1, InStrRev(Sciezka, ".") - InStrRev(Sciezka, "\") - 1)
End Sub


opcja 2) – wykorzystanie funkcji Split

Funkcja VBA Split jest funkcją rozdzielającą łańcuch znaków.

Argumenty wejściowe Split() to:

  • Expression – wyrażenie do rozdzielenia
  • Delimiter – separator – znak rozdzielający (argument opcjonalny, wartość domyślna to spacja).
  • Limit – ilość pojedynczych łańcuchów wydzielonych z wyrażenia. Argument opcjonalny (wartość domyślna -1, zwracając wszystkie).
  • Compare – tryb porównania ciągów. Dla porównania tekstowego – wartość 1. (dla wartości domyślnej 0 – porównanie binarne).

Funkcja zwraca wartość tablicową. Poszczególne elementy tablicy indeksowane są począwszy od wartości. Podobny efekt jak funkcja

Zwracana przez funkcję Split wartość jest wartością tablicową. Poszczególne elementy są indeksowane począwszy od wartości zero.

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    Dim TabElementy() As String
    Dim Sciezka As String
    Sciezka = "C:\Users\Documents\makro\makro.xlsx"
    
    TabElementy() = Split(Sciezka, "\")
    NazwaPliku = TabElementy(UBound(TabElementy()))
End Sub

W powyższym przykładzie funkcja split() rozdziela ścieżkę po ukośniku. W efekcie zwraca nam tablicę, gdzie ostatnim elementem jest nazwa pliku wraz z rozszerzeniem. Dlatego użyliśmy polecenia Ubound() aby sprawdzić wielkość tablicy i zwrócić jej ostatni element.


opcja 3) – użycie funkcji DIR.

Jest jedną z bardziej popularnych metod, ale nie jest to wcale najlepszy sposób. Dlaczego?
1. Działa wolno, bo zawsze przeszukuje dysk.
2. Plik musi istnieć, aby działała poprawnie.
3. Jest niestabilna (jeśli tworzymy pętlę po wszystkich plikach danego folderu, to takie wywołanie dir zniszczy tę pętle)
4. Nie zadziała poprawnie jeśli plik jest ukryty

Jako argument funkcji Dir wpisujemy ścieżkę do pliku.
Przykład:

Sub PobierzNazwePliku()
    Dim NazwaPliku As String
    NazwaPliku = Dir("C:\Users\Documents\makro\makro.xlsx")
End Sub

Co ważne polecenie Dir zwróci nazwę pliku wraz z rozszerzeniem tylko wtedy gdy plik istnieje w podanym katalogu. Dlatego aby powyższy przykład zadziałał musimy najpierw stworzyć plik makro.xlsx w katalogu makro (C:\Users\Documents\makro\). Wtedy funkcja zwróci łańcuch marko.xlsx. Jeżeli plik nie istnieje lub w podanej ścieżce jest błąd to funkcja zwróci pusty łańcuch znaków.

Przykład z poprzedniego wpisu
Link do poprzedniego wpisu: http://heap.pl/pobieranie-sciezki-pliku/

Poniżej prezentujemy przykład z poprzedniego wpisu, ale tym razem dodamy linijkę gdzie wykorzystany będzie sposób 1 aby pobrać nazwę pliku.

Sub PobierzNazwePliku4()
    Dim NazwaPliku As Variant
    Dim FileFilter  As String
    Dim FilterIndex  As Integer
    Dim Title As String
    
    FileFilter = "Pliki Excel (*.xlsx;*.xls),*xlsx;*.xls," & _
    "Pliki Excel XLSX (*.xlsx),*.xlsx," & _
    "Pliki Excel 97-2003 (*.xls),*.xls," & _
    "Wszystkie pliki (*.*),*.*"
    
    FilterIndex = 4 'Wybieramy domyślny filtr. 4 oznacza że domyślnie wybieramy filtr Wszystkie pliki (*.*)
    Title = "Otwórz plik Excela"
    
    NazwaPliku = Application.GetOpenFilename(FileFilter, FilterIndex, Title)
    
    'Wyświetlenie  MessageBox, jeśli w oknie dialogowym zostanie naciśnięty przycisk Anuluj
    If NazwaPliku = False Then
        MsgBox "Nie wybrano żadnego plikU."
        Exit Sub
    End If
    
    NazwaPliku = Right(NazwaPliku, Len(NazwaPliku) - InStrRev(NazwaPliku, "\"))
End Sub

To tyle na dziś. 🙂

Po przeczytaniu tego wpisu będziesz wiedział jak w łatwy sposób pobrać pełną ścieżkę pliku. Do tego celu można wykorzystać metodę GetOpenFilename obiektu Application lub metodę GetSaveAsFilename obiektu Application.

Metoda GetOpenFilename wyświetla dobrze wszystkim znane okno dialogowe Otwieranie, gdzie wskazujemy folder oraz wybieramy plik, który nas interesuje.
okno otwierania

Co ważne metoda ta nie otwiera wskazanego pliku a jedynie pobiera jego ścieżkę oraz nazwę i zwraca jako łańcuch znaków.

Poniżej przykład implementacji metody GetOpenFilename

Sub PobierzSciezkePliku()
    Dim SciezkaPliku As Variant 'Deklaracja zmiennej, która będzie przechowywać ścieżkę pliku
    Dim FileFilter As String 'Deklaracja zmiennej, która będzie argumentem FileFilter
    
    FileFilter = "Pliki Excel XLSX (*.xlsx),*.xlsx," 'Tutaj podajemy łańcuch określający kryterium filtrowania plików
    
    SciezkaPliku = Application.GetOpenFilename(FileFilter) 'Otwarcie okna dialogowego Otwieranie oraz pobranie ścieżki pliku do zmiennej
End Sub

Wszystkie argumenty metody GetOpenFilename są opcjonalne.

Application.GetOpenFilename(FileFilter, FileIndex, Title, ButtonText, MultiSelect)

FileFilter – argument ten określa co pojawi się na liście rozwijanej okna dialogowego Otwieranie. Jeżeli pominiemy ten argument to w oknie będą wyświetlane Wszystkie pliki. Jest to równoznaczne z podaniem argumentu „Wszystkie pliki (*.*),*.*

W powyższym przykładzie wyświetlamy tylko pliki formatu .xlsx. Argument FileFilter jest równy: „Pliki Excel XLSX (*.xlsx),*.xlsx,”
Pierwsza część łańcucha: Pliki Excel XLSX  definiuje jaki tekst będzie wyświetlany na liście rozwijanej Pliki typu. Druga część: (*.xlsx),*.xlsx, określa jakie pliki zostaną wyświetlone w oknie (tylko o formacie .xlsx).

FilterIndex – argument ten definiuje domyślne kryterium filtrowania plików (Ma to znaczenie w przypadku gdy FileFilter posiada więcej niż jedno kryterium. Poniżej jest zaprezentowany przykład gdy argument FileFilter składa się z 3 kryteriów).

Title – tytuł okna dialogowego (jesli zostanie pominięty, tytułem będzie łańcuch Otwieranie

ButtonText – argument stosowany tylko w przypadku systemu Macintosh

MultiSelect – jeżeli argument ma wartość True, wybranych może być więcej niże jeden plik. Wartością domyślną jest False (możliwość wybierania tylko jednego pliku). Gdy argument będzie równy True to metoda GetOpenFilename będzie zwracać tablicę.

Poniżej przykład gdy mamy 3 kryteria filtrowania plików:

Sub PobierzSciezkePliku()
 Dim SciezkaPliku As Variant
 Dim FileFilter As String
 
 FileFilter = "Pliki Excel (*.xlsx;*.xls),*xlsx;*.xls,"
 "Pliki Excel XLSX (*.xlsx),*.xlsx,"
 "Pliki Excel 97-2003 (*.xls),*.xls,"
 
 SciezkaPliku = Application.GetOpenFilename(FileFilter)
End Sub

Następnie spróbujmy zdefiniować 4 kryteria filtrowania plików, ustawić czwarte kryterium jako domyślne i zmienić tytuł okna dialogowego.

Sub PobierzSciezkePliku()
    Dim SciezkaPliku As Variant
    Dim FileFilter  As String
    Dim FilterIndex  As Integer
    Dim Title As String
    
    FileFilter = "Pliki Excel (*.xlsx;*.xls),*xlsx;*.xls," & _
    "Pliki Excel XLSX (*.xlsx),*.xlsx," & _
    "Pliki Excel 97-2003 (*.xls),*.xls," & _
    "Wszystkie pliki (*.*),*.*"
    
    FilterIndex = 4 'Wybieramy domyślny filtr. 4 oznacza że domyślnie wybieramy filtr Wszystkie pliki (*.*)
    Title = "Otwórz plik Excela"
    
    SciezkaPliku = Application.GetOpenFilename(FileFilter, FilterIndex, Title)
    
    'Wyświetlenie  MessageBox, jeśli w oknie dialogowym zostanie naciśnięty przycisk Anuluj
    If SciezkaPliku = False Then
        MsgBox "Nie wybrano żadnego plikU."
        Exit Sub
    End If
End Sub

Metoda GetSaveASFilename jest prawie identyczna jak metoda GetOpenFilename. Różnica jest taka, że wyświetla ona okno dialogowe Zapisywanie jako i umożliwia wybranie pliku lub podanie nazwy pliku. Funkcja również zwraca ścieżkę pliku wraz z nazwą i nie podejmuje żadnej akcji. Wszystkie argumenty tej funkcji są opcjonalne. Argument InitialFilename pozwala określić sugerowaną nazwę pliku (wraz ze ścieżką do pliku).

ZADANIE

Zadanie polega na wyróżnieniu wszystkich osób o wieku poniżej 30 lat.
a) chcemy pokolorować tylko ich wiek
b) chcemy pokolorować cały wiersz w którym jest taka osoba

Do zadania będzie potrzebna baza osób kliknij tu (xlsx) lub tu (zip)

Najwygodniej rozwiązać to zadanie używając formatowania warunkowego.
Dzięki temu po zmianie danych kolorowanie komórek również się zmieni. (będzie aktualizowało się na bieżąco)

Rozwiązanie pkt a)

To do dzieła!

  1. Zaznaczamy wszystkie komórki zawierające wiek.
  2. z karty Narzędzia Główne wybieramy Formatowanie Warunkowe -> Nowa Reguła -> Formatuj komórki zawierające
  3. wpisujemy kryteria w kolejnych kratkach (konkretnie: wartość, mniejsza niż, 30)
  4. musimy jeszcze wybrać kolor na który mają sformatować się komórki spełniające kryteria, czyli Formatuj -> karta Wypełnienie -> wybieramy kolor
  5. Klikamy OK

Możesz zobaczyć to tutaj

Rozwiązanie pkt b)

  1. Zaznaczamy całą tabelę (bez nagłówków) a nie tylko wiek.
    (bo jeśli wszyscy mieliby poniżej 30 lat, to chcemy kolorować wszystkie komórki)
  2. z karty Narzędzia Główne wybieramy Formatowanie Warunkowe -> Nowa Reguła -> Użyj formuły do określenie komórek…
  3. wpisujemy formułę: =$E2 < 30
    Formułę piszemy tak, żeby była prawdziwa jeśli komóreka ma być kolorowana, w przeciwnym razie ma dać w wyniku fałsz.
    Formuła ma być napisana dla lewej górnej komórki (aktywnej) czyli w naszym przypadku dla A2
    A po co znaczek $ ?
    musimy wstawić go przed E, ponieważ Excel kopiując komórki powinien zawsze sprawdzać wiek (kolumnę E). Jeśli zabrakło by $ to Excel Dla liczb porządkowych działałoby prawidłowo, ale dla kolejnych kolumn przesuwałby kryteria w prawo (dla imion sprawdzałby zawód (kolumna F), a dla nazwisk kolumnę G)
  4. nie zapomnij o zdefiniowaniu formatowania komórek (ja wybrałem kolor niebieski)

Możesz zobaczyć to tutaj

Layout mode
Predefined Skins
Custom Colors
Choose your skin color
Patterns Background
Images Background

Zapisz się na nasz newsletter dotyczący nauki Excela

Wysyłaj mi newsletter (możesz się wypisać w każdym dowolnym momencie).

FreshMail e-mail marketing free online marketing software