Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.pl
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

[vba Excel] makro do tworzenia przefiltrowanej listy zakładek

Korek_123 07 Feb 2016 13:24 2307 9
  • #1
    Korek_123
    Level 11  
    Witam,

    Stworzyłem makro które w skoroszycie tworzy listę zakładek i na tej podstawie tworzy pole wyboru.

    Makro jest w załączeniu i zasadniczo (w tej wersji podstawowej) działa poprawnie.

    Potrzebuje jednak aby lista zakładek była tworzona z filtrem, tak aby na liście były tylko zakładki, które w nazwie mają co najmniej 6 cyfr i wśród nich jest ciąg cyfr „2016” (ale tylko jeden ciąg cyfr „2016”). Cyfry mogą nie być oddzielone albo mogą zostać oddzielone spacjami, kropkami, albo ” _ ” (są to daty, które inni mogą wpisać właśnie z taką dowolnością – nie mam na to najmniejszego wpływu).

    Przyznam, że nie mam pojęcia jak w VBA stworzyć takie formuły/polecenia, które będą sprawdzały warunki dotyczące nazwy „bieżąco odczytanej” nazwy zakładki – nigdy tego nie robiłem.

    Uprzejmie proszę o pomoc / podpowiedź.

    Pozdrawiam,
    Korek
    Ethernet jednoparowy (SPE) - rozwiązania w przemyśle. Szkolenie 29.09.2021r. g. 11.00 Zarejestruj się za darmo
  • Helpful post
    #2
    JRV
    VBA, Excel specialist
    Code:

    j = 1
    For i = j To Sheets.Count
        If Sheets(i).Name Like "*2016*" Then
            Doc.Cells(w_doc - 1 + j, k_doc) = Sheets(i).Name
            j = j + 1
        End If
    Next i


    Quote:
    tylko jeden ciąg cyfr „2016
    - niestety nie gra, potrzebujesz majstra wyrażeń regularnych
    http://vbamania.blogspot.com/2009/10/regexp-jak-to-wykorzystac.html
    http://excelperfect.pl/wp-content/uploads/2014/10/Wyra%C5%BCenia-Regularne.doc
  • Helpful post
    #3
    adamas_nt
    Moderator of Programming
    A może wystarczy podstawić separator i sprawdzić czy data?
    Code: vbscript
    Log in, to see the code
  • #4
    Korek_123
    Level 11  
    Witam,

    @ JRV – dziękuje za pomoc!

    Kod działa super, gdy zmienna to tylko „rok”.


    @ adamas_nt – bardzo dziękuję za pomoc!

    „Chylę czoła ” – _SERIO_ - ja bym czegoś takiego nie dał rady napisać! (nie jestem programistą!!! i do Twojej wiedzy to mi brakuje „lat świetlnych” !!!)

    Zaproponowany przez Ciebie kod działa super i w 95% przypadków.
    W załączeniu przykład z Twoim kodem wpisanym do makra.

    Kod niestety „nie łapie” dwóch przypadków i nie wiem dlaczego (zakładki zaznaczone na „żółto” – w załączonym przykładzie) – gdy „rok” jest na końcu daty oraz gdy miesiąc i dzień są przestawione. I nie wiem jak sobie z tym poradzić.


    Mam jeszcze jeden przykład - z prośbą o pomoc (też jest w załączonym pliku).
    Chodzi o generowanie listy dni roboczych w roku z uwzględnieniem weekend-ów oraz dni świątecznych (wolnych od pracy).

    I ponownie …. potrafię napisać podstawowe makro. Makro generuje listę dni roboczych z uwzględnieniem weekend-ów i świąt w PL w 2016 roku (z zadaną liczbą dni do wygenerowania / skokiem – to zmienna).

    Ale chciałbym (uzyskać coś więcej ;-) ) aby poprawić czytelność tej listy – i tu dwa warianty:

    A. - Chciałbym aby „poniedziałki” były zaznaczone innym kolorem tła i to niby jest możliwe przez:

    „If Weekday(kolejny_dzien) = vbMonday Then”

    Ale gdy chce (do formatowania komórki) wyciągnąć numer wiersza przez „Nr_wiersza = kolejny_dzien.Row” - to pojawia się kod błędu – i nie rozumiem dlaczego !?!? . Robię jakiś (chyba prosty) błąd ale nie rozumiem jaki.

    albo

    B. - Chciałbym aby tygodnie „od poniedziałku do piątku” (z uwzględnieniem Świąt) były zaznaczane naprzemiennie innym kolorem tła (ale tego zupełnie nie wiem jak zrobić ;-( ).

    Ponownie bardzo uprzejmie proszę o pomoc / podpowiedź.

    Pozdrawiam,

    Korek
  • #5
    adamas_nt
    Moderator of Programming
    To drugie - to formatowanie warunkowe. Przyjrzę się za chwilę.
    Z datami mieliśmy już kiedyś zabawę :)
    31-01-2016 (po naszemu), 01-31-2016 (USA) -wiadomo ostatni stycznia

    03-04-2016 - jeśli nie wiesz kto (nasz, czy ichni) pisał, to można tylko (fifty-fifty) zgadywać. Kwiecień, czy marzec?

    Chyba, żeby kombinować z 8 liczbami zawierającymi rok tak długo, aż wyjdzie data i jeśli OK - dołożyć do odfiltrowanych, bez względu na to czy wiosna, czy lato ;)
    Ale w takim przypadku, zdaje się, Like lub InStr wystarczy.

    Edit: Nie rozumiem za bardzo co chcesz "kolorować", albo zbyt późno już jest :(
    Który zakres ma być formatowany, ten od M24 w dół?
  • #6
    marcinj12
    Level 40  
    Korek_123 wrote:
    które w nazwie mają co najmniej 6 cyfr i wśród nich jest ciąg cyfr „2016”
    Jeżeli Kolega nie potrzebuje sprawdzać czy to data czy nie, to można uprościć ten warunek jako:
    Code: vbscript
    Log in, to see the code
    (zakładając, że ma być co najmniej 6 ZNAKÓW, nie cyfr, czyli zakładka kolory2016 też ma się załapać). Swoją drogą - nie powinno być 8 znaków / cyfr? rrrr-mm-dd

    Jeżeli ma sprawdzać cyfry, to bez wyrażenia regularnego można taką partyzantką:
    Code: vbscript
    Log in, to see the code
  • #7
    JRV
    VBA, Excel specialist
    Korek_123 wrote:
    Nr_wiersza = kolejny_dzien.Row” - to pojawia się kod błędu – i nie rozumiem dlaczego

    Dlatego ze wlasciwosc(property) Row - jest numer wiersza, data(tj kolejny_dzien) nie ma takej wlasciwosci
    WorkDay - Returns a number that represents a date (Zwraca liczbę reprezentującą datę)

    Dodano po 3 [minuty]:

    wtedy
    Doc.Cells(p_w + i, p_k).Interior.Color = RGB(22, 54, 92)

    Dodano po 15 [minuty]:

    Lekko zmodyfikowany kod @adamas_nt
    Code: vb
    Log in, to see the code
  • #8
    Korek_123
    Level 11  
    Witam,

    Bardzo dziękuje za pomoc i podpowiedzi.

    @JRV
    Masz całkowitą rację - wystarczy odwołać się do

    If Weekday(kolejny_dzien) = vbMonday Then

    przez dzień tygodnia, dla:

    Doc.Cells(p_w + i, p_k).Interior.Color = RGB(122, 122, 122)

    i wtedy „dopisać kolor tła” (jest w załączonym przykładzie ;-) )

    Nie wiem dlaczego „zafiksowałem się” (pewnie ze stosowania mechanizmu „.Find” w innych makrach) na „odczytanie” numeru wiersza.

    Ponownie okazała się, że (Twoje) prostsze rozwiązanie działa lepiej – jednak zawsze „Brzytwa Ockhama” niezmiennie działa :-D . Bardzo dziękuję za podpowiedź !


    @ adamas_nt

    Napisałeś:
    >> Nie rozumiem za bardzo co chcesz "kolorować"

    Kolorować chce kolejne dni tygodnia na tworzonej liście – tak aby zwiększyć (IMHO oczywiście) czytelność tej listy. Przykład jak to ma działać (po podpowiedzi JRV –jest w załączeniu).

    @ marcinj12

    Niestety muszę sprawdzać czy nazwa zakładki zawiera datę. W plikach, które otrzymuje, czasem są inne zakładki z przedziwnymi nazwami i mało standardową zawartością.

    @ all
    Jeszcze raz dziękuję JVR, adamas_nt i marcinj12 za udzieloną pomoc i podpowiedzi. Ale (zawsze jest jakieś ale ;- P ) mam równocześnie prośbę o ocenę pomysłu przedstawionego poniżej.

    Jak napisał adamas_nt : „Z datami mieliśmy już kiedyś zabawę” – dlatego chciałbym ograniczyć tą „zabawę” – czyli zmienność.

    I w związku z tym pomysł jest taki:

    A.Jak jest teraz:

    Otrzymuje raporty (pliki) w formacie MS Excel. Raporty są generowane / wypełniane przez różne osoby. Nazwy zakładek w plikach mają nadane daty (najczęściej) - ale nie mam wpływu na to jak te nazwy zakładek „wyglądają” (jest np.: yyyy-mm-dd, coś_tam_ yyyy_mm_dd, zest01.dd.mm.yyyy itp. iItd.).
    Wariantów jest wiele i nie da się jakoś sensownie i powtarzalnie tego uchwycić / ogarnąć / zbudować reguły.

    B.Pomysł na rozwiązanie:

    Osoby które przygotowują zestawienia otrzymają przygotowany szablon (plik MS Excel z makrami) którego będą używać. Użytkownicy nie będą mogli nic zmienić w tym pliku – będą mogli tylko wypełnić obszar przeznaczony do wpisani danych.

    I tu bardzo proszę o pomoc, w szczególności JRV, adamas_nt, marcinj12 oraz innych – oraz o ocenę funkcjonalności / koncepcji która założyłem.

    Nie chodzi mi o napisanie „gotowego” rozwiązania –stworzę taki plik / makro / wiele makr ….. ale potrzebuję rady / podpowiedzi czy funkcjonalność, którą założyłem da się osiągnąć przez VAB Excel.

    C. Funkcjonalność: (teraz z punktu widzenia użytkownika = User)


    1. User dostaje mailem WZÓR raportu (dalej WZ) - plik MS Excel z wbudowanymi makrami.

    2. User nie może w WZ nic zmienić ani dodać.

    3. User zapisuje WZ w swoim katalogu (domowym / domyślnym) .

    4. Gdy przyjdzie odpowiedni czas (np. w piątek) User uruchamia WZ.

    5. Po uruchomieniu WZ, User _musi_ wybrać „ID” (identyfikator punktu sprzedaży/sklepu) – to jest warunek konieczny. „ID” jest wybierane z listy wyboru (ID ma postać np. Sklep_001, Sklep_002 itd.). Ta lista jest generowana dynamicznie na podstawie danych „ukrytych” w jakiejś zakładce w WZ (User nie ma dostępu do tych danych / listy).

    6. WZ automatycznie generuje listę wierszy zawierających daty dni roboczych w tygodniu zakończonym w piątek – w zależności od daty uruchomienia WZ.

    7. WZ automatycznie generuje listę produktów w kolumnach, które zostały albo mogły zostać sprzedane w bieżącym tygodniu, którego dotyczy zestawienia (produkty dla uproszczenia maja nazwy „Produkt_001, Produkt_002 itd., lista produktów jest zapisana w ukrytej zakładce WZ, do tej zakładki User nie ma dostępu).

    8. User wpisuję (ręcznie „wpalcowuje”) wartość sprzedanych produktów dla poszczególnych dat (wiersze) i produktów (kolumny), w złotych polskich np. z dwoma miejscami po przecinku (format danych jest zadany z „góry”).

    9. Po zakończeniu (męczącej czynności wpisywania danych o sprzedaży) User „klika” na przycisk makra „Zapisz” i wtedy:

    9.1 Pojawia się „okno dialogowe” z komunikatem „Czy potwierdzasz poprawność wpisanych danych” – możliwa odpowiedź „Tak” albo „Nie”. Jeżeli „Tak” makro „idzie” dalej, jeżeli „Nie” makro wraca do pkt 8 powyżej.

    9.2 wpisane w WZ makro tworzy nowy (osobny plik MS Excel – dalej nowy raport = NR, plik jest tworzony bez możliwości ingerencji ze strony Usera), NR zawiera wszystkie dane wpisane w WZ (z datami i nazwami produktów i wartościami sprzedaży, ale bez makr – tylko sformatowane dane).

    9.3 w NR pierwsza (i jedyna) zakładka z danymi „otrzymuję” nazwę „ID__DP” – gdzie „DP” to data pierwszego dnia(poniedziałek - najczęściej) tygodnia roboczego, którego dotyczy NR, data zapisana w formacie „yyyy-mm-dd”, nazwa zakładki powinna być taka: ”ID__yyyy_mm_dd”.

    9.4 NR jest zapisywany, w formacie MS Excel w tym samym katalogu, w którym jest zapisany WZ, z nazwą pliku ID__data_zapisania__godzina_zapisania.

    9.5 NR jest zapisywany, w formacie PDF w tym samym katalogu, w którym jest zapisany WZ, z nazwą pliku ID__data_zapisania__godzina_zapisania.PDF (w PDF zapisywany jest tylko obszar zawierający dane wpisane w WZ.

    9.6 WZ czyści swoją zawartość z danych i kończy działanie bez zapisywanie zmian w WZ.

    Następnie User wysyła mi mailem dwa pliki ID__data_zapisania__godzina_zapisania.xls (albo .xlsx) oraz ID__data_zapisania__godzina_zapisania.PDF .

    I tu jeszcze raz prośba o wskazanie – czy to jest do osiągnięcia za pomocą VBA Excel ?

    Jeżeli tak – to będę szukał i eksperymentował aż osiągnę ten efekt ;-)

    Jeżeli nie – to proszę o wskazanie czego nie da się zrobić za pośrednictwem VBA Excel (mam Excel-a 2007 w domu o 2010 w pracy – tam ma to działać).

    Pozdrawiam,

    Korek
  • #9
    JRV
    VBA, Excel specialist
    Korek_123 wrote:
    czy to jest do osiągnięcia za pomocą VBA Excel ?

    Excel moze WSZYSTKO !
    Ponadto raport nie zapisac na dysku, tylko naprosto wyslac mailem do Ciebie
    Z PDF-em nie mam doswiadczenia, tu nic nie mowie
    Problem może być, jako zasadę, gdy korzystać różne wersje programu Excel w uzytkownikow.
    Wiem, bo zrobiłem różnych zautomatyzowanych stanowisk pracy i znacznie bardziej skomplikowane niż Twój projekt.

    Dodano po 1 [minuty]:

    a co jest „Brzytwa Ockhama” ? Nie znam to wyrazenie...
  • #10
    marcinj12
    Level 40  
    Czytam, czytam i jakoś mi się to nie chce w głowie ułożyć...
    Moim zdaniem: można, ale jest za dużo rzeczy, które mogą pójść nie tak. Excel jest fajny, ale do pewnego stopnia. Do niektórych zastosowań lepiej nadają się inne języki.
    Nie wiem czy dobrze rozumiem - chcesz stworzyć jakieś narzędzie do zbierania danych sprzedażowych? Jak duża to ma być skala, ilu potencjalnych użytkowników? Czy to są kluczowe dane? Jeśli tak => Excel, w takiej formie jak przedstawiłeś, odpada.

    Parę pytań do przemyślenia:
    Korek_123 wrote:
    1. User dostaje mailem WZÓR raportu (dalej WZ) - plik MS Excel z wbudowanymi makrami.
    A jak użytkownik nie włączy makr? Albo coś mu się zawiesi w najmniej odpowiednim momencie?

    Korek_123 wrote:
    2. User nie może w WZ nic zmienić ani dodać.
    Jak to zagwarantujesz? Co, jeśli coś jednak doda lub zmieni?

    Korek_123 wrote:
    4. Gdy przyjdzie odpowiedni czas (np. w piątek) User uruchamia WZ.
    Co jeśli zapomni? Albo w piątek wypadnie święto? Makro musiało by to uwzględnić.

    Korek_123 wrote:
    5. Po uruchomieniu WZ, User _musi_ wybrać „ID” (identyfikator punktu sprzedaży/sklepu) – to jest warunek konieczny. „ID” jest wybierane z listy wyboru (ID ma postać np. Sklep_001, Sklep_002 itd.). Ta lista jest generowana dynamicznie na podstawie danych „ukrytych” w jakiejś zakładce w WZ (User nie ma dostępu do tych danych / listy).
    Dlaczego musi coś wybierać ręcznie? Czemu pani Gienia ze sklepu_001 ma mieć możliwość wybrania siebie jako pani Halina za sklepu_002? Jeśli już, czemu nie umieścisz nazwy sklepu dla każdego pliku osobno, w kodzie?

    Korek_123 wrote:
    7. WZ automatycznie generuje listę produktów w kolumnach, które zostały albo mogły zostać sprzedane w bieżącym tygodniu, którego dotyczy zestawienia (produkty dla uproszczenia maja nazwy „Produkt_001, Produkt_002 itd., lista produktów jest zapisana w ukrytej zakładce WZ, do tej zakładki User nie ma dostępu).
    Jak zagwarantujesz, że lista produktów jest kompletna? Co, jeśli ulegnie zmianie? Co, jeśli jednak znajdzie się produkt, którego nie ma na liście? Każdorazowo będziesz poprawiał listę i rozsyłał plik do wszystkich użytkowników?

    Korek_123 wrote:
    8. User wpisuję (ręcznie „wpalcowuje”) wartość sprzedanych produktów dla poszczególnych dat (wiersze) i produktów (kolumny), w złotych polskich np. z dwoma miejscami po przecinku (format danych jest zadany z „góry”).
    Już to widzę... :] Nie wiem czym tam handlujecie, ale o ile nie są to Bentleye :], gwarantuję Ci, że żadna pani Halinka ani pani Gienia nie wpisze Ci po tygodniu dokładnej - ani nawet przybliżonej - listy sprzedanych produktów. Jak by to miała zrobić, bo chyba nie z głowy?
    Chcesz podawać każdorazowo ceny produktów? Dlaczego ich nie przypisać ich na stałe do produktu? Co Cię zabezpieczy przed wpisaniem błędnej ceny, przecinka w złym miejscu czy "jednego zera za dużo"? Samo ustawienie formatu nie wystarczy...

    Korek_123 wrote:
    9. Po zakończeniu (męczącej czynności wpisywania danych o sprzedaży) User „klika” na przycisk makra „Zapisz” i wtedy:
    Jak wyżej - już widzę jak ktoś skrupulatnie wypełnia taki plik ręcznie... Nie macie tam kasy fiskalnej? Czytnika kodów kreskowych? Czegokolwiek, z czego można by pobierać te dane "online"?

    Korek_123 wrote:
    9.1 Pojawia się „okno dialogowe” z komunikatem „Czy potwierdzasz poprawność wpisanych danych” – możliwa odpowiedź „Tak” albo „Nie”. Jeżeli „Tak” makro „idzie” dalej, jeżeli „Nie” makro wraca do pkt 8 powyżej.
    Jest piątkowe popołudnie, chcę już iść do domu, ale jeszcze muszę jakiś pos***ny plik wypełniać... Wpisywałem dane przez 5 godzin. Program się mnie pyta, czy na pewno dane są poprawne... Jak myślisz, co kliknę?? :]

    Korek_123 wrote:
    9.5 NR jest zapisywany, w formacie PDF w tym samym katalogu, w którym jest zapisany WZ, z nazwą pliku ID__data_zapisania__godzina_zapisania.PDF (w PDF zapisywany jest tylko obszar zawierający dane wpisane w WZ.
    Po co zapisujesz te dane jeszcze do "nieobrabialnego" PDFa?

    Korek_123 wrote:
    9.6 WZ czyści swoją zawartość z danych i kończy działanie bez zapisywanie zmian w WZ.
    Świetny pomysł - chyba że coś pójdzie nie tak (albo będę chciał coś poprawić) i stracę wszystkie dane, które tak długo wklepywałem... Gwarantuje wiele "ciepłych słów" od "szczęśliwych" użytkowników. :]

    To tak na gorąco, co widzę. Moim skromnym zdaniem próbujesz zrobić coś skomplikowanego nieodpowiednimi narzędziami. Jeśli chcesz zbierać dane sprzedażowe, albo pobieraj je online z jakiegoś urządzenia - kasy, czytnika kodów, programu - choć pewnie tego akurat nie macie, bo inaczej byś tak nie kombinował... Wiec może warto jakiś zakupić?
    Albo, to druga opcja, stwórz jakąś aplikację webową, z jedną, centralną bazą. Przez prosty formularz www można wprowadzać dane na bieżąco z wielu lokalizacji. Zresztą tu również masz różne gotowe rozwiązania...