Elektroda.pl
Elektroda.pl
X

Search our partners

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

[VBA] Łączenie procedury i funkcji

swiezak_ 27 Feb 2016 12:47 3594 34
  • #1
    swiezak_
    Level 7  
    Witam,

    Po kilku dniach spędzonych na różnych stronach poświęconych programowaniu w języku VBA postanowiłam poprosić specjalistów o pomoc, bo nie mam pomysłu jak sobie z tym poradzić. A czasu na wykonanie zadania pozostało mi naprawdę niewiele. Dodam jeszcze standardowy tekst osób piszących o pomoc, że w temacie makr pisanych w vba jestem początkująca :)
    Otóż znalazłam makro które tworzy pliki o nazwie takiej samej jak arkusze w skoroszycie źródłowym a następnie wysyła każdy utworzony plik w oddzielnej wiadomości e-mail jako załącznik . I działa. Problem polega na tym, ze nazwy, które musza być użyte do nazwania plików zawierają niedozwolone znaki
    .
    Znalazłam funkcje , która usuwa niedozwolone znaki ale zupełnie nie potrafię sobie poradzić i połączyć wszystkiego razem
    Stad goraca prośba o pomoc

    procedura:
    Code: vbscript
    Log in, to see the code


    funkcja:
    Code: vbscript
    Log in, to see the code


    Pozdrawiam
    Ethernet jednoparowy (SPE) - rozwiązania w przemyśle. Szkolenie 29.09.2021r. g. 11.00 Zarejestruj się za darmo
  • Eltrox Hurton
  • Helpful post
    #2
    marcinj12
    Level 40  
    Lista niedozwolonych znaków dla nazwy pliku w Windowsie jest krótsza, w dodatku domyślnie nie można niektórych z nich wstawić jako nazwy arkusza, więc finalnie można ją skrócić jeszcze bardziej. Możesz sobie sprawdzić listę i wstawić własne znaki.
    Pełen przykład poniżej:
    Code: vbscript
    Log in, to see the code
  • #3
    swiezak_
    Level 7  
    dziękuje, zapomniałam jeszcze dodać najważniejszego mam makro, które w pierwszym kroku w pliku źródłowym (o nazwie Arkusz1) tworzy arkusze, do których kopiuje wiersze wybierane po wartości z kolumny A z arkusza1.
    Problem polega na tym, ze nazwy w kolumnie A czasami sa dłuższe niż 31 znaków i wywala mi bład w tej linijce:
    temp.name = ark.Cells(i, 1)

    Całość na ta chwile wyglada nastepujaco:
    Code: vbscript
    Log in, to see the code


    Oprócz problemu z stworzeniem arkuszu po wszystkich nazwach występujący w koulmnie A. Na razie ominęłam to w taki sposób, ze stworzyłam dodatkową kolumnę i nazwy ograniczyłam funkcją [=LEWY(B1;31)] i w ten sposób wszystkie arkusze się utworzyły ( jest ich 37) ale wysyłają się tylko dwa (arkusz1 i podmioty zewnętrzne - czyli arkusz utworzony na podstawie nazwy kolumny A. Oba arkusze tak na dobra sprawę nie powinny się wysyłać...A pozostałe nie wysyłają się. Wywala komunikat
    Run _ time erroe '9'. Subscript out of range.
    A całe makro zatrzymuje się na linijce

    With Workbooks(cleanName & ".xls")

    Czy ktoś może podpowiedzieć gdzie szukać błędów....
  • #4
    JRV
    VBA, Excel specialist
    Jezeli Excel >=2007 wtedy cleanName & ".xlsx"

    Dlaczego If UCase(Arkusz.Name) <> "Arkusz1"
    nie ma byc If UCase(Arkusz.Name) <> "ARKUSZ1"
  • Eltrox Hurton
  • Helpful post
    #5
    marcinj12
    Level 40  
    swiezak_ wrote:
    Problem polega na tym, ze nazwy w kolumnie A czasami sa dłuższe niż 31 znaków i wywala mi bład w tej linijce:
    temp.name = ark.Cells(i, 1)

    Wystarczy zrobić:
    Code: vbscript
    Log in, to see the code
    , choć nie zagwarantuje to unikalności nazw arkusza w pliku, o ile to problem...

    Code: vbscript
    Log in, to see the code
    Ten warunek nigdy nie będzie spełniony, bo nie wpisałaś nazwy dużymi literami.
    Przerób go na warunki z AND i nazwami arkuszy, których nie chcesz wysyłać. Ucase nie potrzebujesz:
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    Run _ time erroe '9'. Subscript out of range.
    A całe makro zatrzymuje się na linijce

    With Workbooks(cleanName & ".xls")
    Wydaje mi się, że chcesz osiągnąć inny efekt, niż próbujesz. Chcesz wysyłać skopiowane arkusze czy cały plik?
    Jeśli skopiowane arkusze, to przerób kod na:
    Code: vbscript
    Log in, to see the code
  • #6
    swiezak_
    Level 7  
    dziękuje za wszystkie wskazówki. Wszystkie dodałam do makro. jednak nadal ni działa tak jak powinno .
    Podczas tworzenia arkuszy z wybranymi po nazwie z kolumny a w arkuszu 1 nazwie pojawia się komunikat ze :

    RUN - TIME ERROR '1004'

    Nie można nadać arkuszowi nazwy, którą posiada już inny arkusz (chodź takich arkuszy nie ma, bo co prawda nazwy w Kolumnie A nazwy się powtarzają ale jest 37 unikatowych , po których tworzą się nazwy arkuszy. Nie widzę, żeby gdzieś arkusz na którym makro się zatrzymuje był już zapisany. Bo zapisują się na pulpicie
    i tu moje kolejne pytanie , w jaki sposób mogę wskazać, aby pliki zapisywały się w innej lokalizacji ?

    i drugi problem wcześniej makro wysyłało utworzone arkusze jako załączniki teraz wywala błąd w linijce

    Code: vbscript
    Log in, to see the code


    i po pytaniu czy wysłać ---> wybieram zezwól i nic się nie wysyła ...

    No i jeszcze jedna kwestia, w tak zwanym międzyczasie czekając na Panów odpowiedzi i cenne wskazówki, staram się dodać w makrze , aby kopiowały się do każdej tabeli również nagłówki i formaty z tabeli źródłowej .
    Wzorując się na makrze znalezionym na jednym z popularnych forum:
    niestety na razie bez powodzenia:(

    Code: vbscript
    Log in, to see the code


    Proszę o pomoc
  • #7
    marcinj12
    Level 40  
    swiezak_ wrote:
    Podczas tworzenia arkuszy z wybranymi po nazwie z kolumny a w arkuszu 1 nazwie pojawia się komunikat ze :
    RUN - TIME ERROR '1004'
    Na której dokładnie linijce podświetla się ten błąd?

    swiezak_ wrote:
    i tu moje kolejne pytanie , w jaki sposób mogę wskazać, aby pliki zapisywały się w innej lokalizacji ?

    Zmień:
    Code: vbscript
    Log in, to see the code
    na
    Code: vbscript
    Log in, to see the code
  • #8
    swiezak_
    Level 7  
    W TYM MIEJSCU :

    Code: vbscript
    Log in, to see the code
  • #9
    marcinj12
    Level 40  
    swiezak_ wrote:
    Code: vbscript
    Log in, to see the code
    Czyli kod na którym teraz pracujesz jest już inny niż ten na forum... A zmieniłaś też
    Code: vbscript
    Log in, to see the code
    na
    Code: vbscript
    Log in, to see the code
    ?
  • #10
    swiezak_
    Level 7  
    dopiero teraz zmieniłam :(

    teraz przy tworzeniu arkusza wywala się tutaj:

    Code: vbscript
    Log in, to see the code



    cały kod - stan obecny


    Code: vbscript
    Log in, to see the code


    Dodano po 18 [minuty]:

    jeszcze raz puściłam program i teraz wywaliło w tym samym miejscu

    Code: vbscript
    Log in, to see the code


    ale arkusze zostały utworzone , nie zapisały się w folderze "wyniki" i e-mail nie wysłał się
  • #11
    marcinj12
    Level 40  
    swiezak_ wrote:
    Sheets(i).name = i - 1 & "x" & Sheets(i).Range("a65536").End(xlUp).Row & "_" & Sheets(i).name
    Ale po co te całe kombinacje, co chcesz osiągnąć tym kodem?? Celowo chcesz pozmieniać nazwy wszystkich arkuszy na jakieś wymyślne numerki? Pamiętnej, że zmieniasz w pętli nazwy wszystkich arkuszom, łącznie ze swoim źródłowym - Arkusz1
  • #12
    swiezak_
    Level 7  
    no fakt , bezmyślnie przekopiowałam kod.
    wiec zakomentowałam ta linijkę

    Code: vbscript
    Log in, to see the code


    Wynik jest taki, ze arkusze pięknie się tworzą, ale nie zapisują w folderze Wyniki, który umieściłam na pulpicie, ani nie wysyłają się e-mail :(
  • #13
    marcinj12
    Level 40  
    No to po kolei...
    Cały ten kod:
    Code: vbscript
    Log in, to see the code

    możesz zamienić na:
    Code: vbscript
    Log in, to see the code

    a tworzenie nagłówków dodać tam, gdzie rzeczywiście się tworzy arkusze z danymi, czyli:
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    Wynik jest taki, ze arkusze pięknie się tworzą, ale nie zapisują w folderze Wyniki, który umieściłam na pulpicie
    Kolejna nieuwaga przy przepisywaniu kodu :) W podanym przykładzie ścieżka katalogu kończy się znakiem backslash'a, u Ciebie - nie.
  • #14
    swiezak_
    Level 7  
    DZIĘKUJE BARDZO, BEZ POMOCY NIE UDAŁO BY MI SIĘ WYKONAĆ TEGO ZADANIA.

    W projekcie pozostało mi jeszcze kilka spraw tzw. kosmetycznych. Choć jak dla mnie nie do ogarnięcia 
    I tak:
    Arkusz rozszerzyłam i ma teraz 18 kolumn (A:S)
    ---> kolumny automatycznie się dostosowywały do zawartości (tutaj chyba użyteczna będzie Columns ("1:18").AutoFit);
    ---> włączenie opcji zawijania tekstu .
    ---> Każda komórka była obramowana jak w pliku źródłowym
    ---> kolumna R zsumowała wartości z kolumn L, N, O i była zapisana w formacie księgowym. Kolumna E i F – format daty (dd-mm-yy).
    --->I jeszcze jedna kwestia w tabeli źródłowej w kolumnie A znajduje się liczba porządkowa. Podczas wybierania danych po wartości z kolumny b wycina te wiersze i wkleja do arkusza docelowego, z tym ze i co jest logiczne wstawia tez liczbę porządkowa z arkusza źródłowego. Jest jakaś szansa aby w nowo powstałych arkuszach numerowanie w kolumnie A rozpoczynało się od początku (1,2,3,itd.)
    --->No i sprawa dla mnie ważna: z założenia ostatni wiersz ma
    W kolumnie G zawierać obliczona średnią a w kolumnie L , N,O,R – sumę .
    Przy czym użytkownik ,aby miał możliwość dostawiać wiersze , jeżeli by zestawienie nie obejmowało wszystkich zrealizowanych zleceń. Choć nie zawsze będzie z tego korzystał
    Może w tym przypadku należy skorzystać z tej procedury lub podobnej procedury , która wyszuka ostatni wiersze doda 1 albo 2 zapasu a w następnej zsumuje wskazane wartości

    Code: vbscript
    Log in, to see the code


    I chyba już ostatnia sprawa, czy jest możliwość wybiorczo blokować kolumny (np. kolumnę H), albo komórki przed możliwością edycji oraz czy można w całej kolumnie I (rodzaj zadania dodać listę rozwijana z możliwości a „pojazdu sprawcy” miejsca szkody”???

    nie oczekuje absolutnie gotowca, bo mój wkład w to jest praktycznie żaden . Proszę jedynie o wskazówkę, bo nie wiem jak to ugryźć temat, a do oddania pliku pozostały mi dwa dni. największa trudność sprawia mi wklejenie procedur i funkcji w odpowiednie miejsca, bo mam nadzieje, że korzystając z dobrodziejstw Internetu uda mi się odszukać te które będą użyteczne w moim pliku. Może jakąś rada , gdzie konkretnie szukać??? I co można zastosować w moim pliku .


    a cały kod wygląda tak:

    Code: vbscript
    Log in, to see the code



    w załączniku jeszcze dodaje szablon - tak bym chciała aby wyglądały wszystkie tabele w utworzonych arkuszach

    Dodano po 1 [minuty]:

    jeszcze raz dodaje szablon
  • #15
    marcinj12
    Level 40  
    swiezak_ wrote:
    ---> kolumny automatycznie się dostosowywały do zawartości (tutaj chyba użyteczna będzie Columns ("1:18").AutoFit);
    ---> włączenie opcji zawijania tekstu .
    ---> Każda komórka była obramowana jak w pliku źródłowym
    ---> kolumna R zsumowała wartości z kolumn L, N, O i była zapisana w formacie księgowym. Kolumna E i F – format daty (dd-mm-yy).

    Jak rozumiem, ten arkusz ROZLICZENIE to inaczej źródłowy arkusz Arkusz1 ? W takim razie w funkcji esql zostaw tylko wklejenie danych, w procedurze dzialaj() po Call esql(ark.Cells(i, 2), temp.Name), doklej kopiowanie formatów. Zawijanie i obramowanie też zostanie przekopiowane.
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    kolumna R zsumowała wartości z kolumn L, N, O
    Doklej kolejną linijkę - w zależności od potrzeb możesz wybrać, czy wypełnić formułą aż do ostatniego wiersza, czy cały zakres podany na sztywno:
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    --->I jeszcze jedna kwestia w tabeli źródłowej w kolumnie A znajduje się liczba porządkowa. Podczas wybierania danych po wartości z kolumny b wycina te wiersze i wkleja do arkusza docelowego, z tym ze i co jest logiczne wstawia tez liczbę porządkowa z arkusza źródłowego. Jest jakaś szansa aby w nowo powstałych arkuszach numerowanie w kolumnie A rozpoczynało się od początku (1,2,3,itd.)

    Dodajesz kolejne linijki, np. pętla:
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    No i sprawa dla mnie ważna: z założenia ostatni wiersz ma
    W kolumnie G zawierać obliczona średnią a w kolumnie L , N,O,R – sumę .
    Przy czym użytkownik ,aby miał możliwość dostawiać wiersze , jeżeli by zestawienie nie obejmowało wszystkich zrealizowanych zleceń. Choć nie zawsze będzie z tego korzystał
    Nie rozumiem do końca, o co chodzi. Jeżeli skopiujesz sumę z szablonu, to ona będzie w ostatnim wierszu...


    swiezak_ wrote:
    w całej kolumnie I (rodzaj zadania dodać listę rozwijana
    Nie wiem czy zadziała też pod 2003. Jak nie - nagraj makro z tworzenia listy i popraw analogicznie do tego.
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    czy jest możliwość wybiorczo blokować kolumny (np. kolumnę H)
    ...i kolejne... Blokuje się cały arkusz, zaś wskazuje się, co ma pozostać niezablokowane.
    Code: vbscript
    Log in, to see the code


    swiezak_ wrote:
    Może jakąś rada , gdzie konkretnie szukać???
    Najlepiej nagrywać fragmenty rejestratorem makr a potem samemu uzyskany kod przeanalizować i poprawić.
  • #16
    swiezak_
    Level 7  
    No trochę źle wyjaśniłam o co mi chodzi 
    Wiec Arkusz1 to wzór, tak bym chciała, aby wyglądały wszystkie tabele, ale to rozwiązanie sprawia, ze kopiuje się znacznie więcej wierszy niż jest to potrzebne w danejtabeli (po 1700 tyle ile jest w tabeli źródłowej (tabela źródłowo, może być bez obramowani a i nagłówków, bo i tak dane będę importowane z csv z SASA ważne aby tabele w utworzonych arkuszach tak wyglądały. A czasami tabela będzie się składała z nagłówka i jednego wiersza 
    Wiec to rozwiązanie, ze wiersze z sumami tez się skopiują nie sprawdzi się, bo chciałabym aby ilość sformatowanych wierszy była zmienna w zależności od ilości danych 
    Numerowanie kolumny a nie działa u mnie (wkleiłam w makro działaj() ) zaraz po utworzeniu listy wyboru

    Chciałabym, aby była wysyłana wraz z załącznikiem krótka wiadomość zmieniłam metodę wysyłki ( bo chyba w poprzedniej nie byłoby możliwości wysłania Emila z treścią (???) 1 e-mail wysłało prawidłowo a teraz wywala błąd (w miedzy czasie chyba cos kliknęłam bo nie działa  a nie potrafię dojść gdzie jest błąd… wysyła 1 e-maila a później pojawia się komunikat :
    ten element został przeniesiony lub usunięty \

    i zatrzymuje się na linijce
    .Close savechanges:=False
    Code: vbscript
    Log in, to see the code
    Z Informacja, że named argument not found

    Za Pana rada nagrałam sobie makro które ustawia zoom na 80 % nic prostszego z tym, że wkleiłam je za procedurą działaj() i niestety u mnie nie działa , heeeeh
    Code: vbscript
    Log in, to see the code

    Musze jeszcze dużo czasu spędzić na pisaniu makr, aby uzmysłowić sobie , gdzie co należy wpisać, aby zadziałało

    Proszę o jeszcze trochę uwagi i pomocy
    Pozdrawiam

    Dodano po 52 [minuty]:

    cały kod


    Code: vbscript
    Log in, to see the code
    [/syntax]

    Dodano po 59 [sekundy]:

    a i jeszcze jedno, podczas nowoutworzonych arkuszach tworzy się dodatkowo pusta kolumna A - niepotrzebna
  • Helpful post
    #17
    marcinj12
    Level 40  
    Ech... Spróbuj czegoś takiego:
    Code: vbscript
    Log in, to see the code
  • #18
    swiezak_
    Level 7  
    bardzo dziękuje ,wszystkie teraz chodzi jak powinno , a utworzone tabele w oddzielnych plikach wyglądają tak jak chciałam, ogromne dzięki.
    W poprzednim miesiącu projekt nie został wdrożony i teraz powróciłam do jego realizacji.
    Próbuje rozwiązania, które zaproponowałeś i przy uruchomieniu makra z wysyłką pojawia sie komunikat
    "compile error: USER - DEFINED TYPE NOT DEFINED"
    A folder, do którego odwołuje się makro jest pusty, nie zapisują się w nim utworzone arkusze.
    Czy mogę jeszcze liczyć na jakaś podpowiedź, dlaczego tak się dzieję?

    Moje makro wygląda teraz tak:

    Code: vbscript
    Log in, to see the code
  • #19
    kinggustav
    Level 24  
    A gdzie zapisuje? Może w C:\Users\xxx\Desktop i dodaje "Wyniki" na początku nazwy pliku?
    Jeżeli tak jest to dodaj znak \ na końcu scieżki. Powinno być tak: "C:\Users\xxx\Desktop\Wyniki\".
  • #20
    swiezak_
    Level 7  
    No własnie tak, było, już w tak zwanym miedzy czasie poprawiłam zapis ścieżki, ale niestety nadal nie działa. Pojawia się błąd w tym samym miejscu i ten sam komunikat. A w folderze "wyniki " tworzy się jeden plik (spośród 37 utworzonych arkuszy)
  • #21
    kinggustav
    Level 24  
    To ustaw breakpointa przed tą linijką i zobacz co masz jako nazwa pliku. Tak będzie najszybciej. Możesz ją pobrać do jakiejś zmiennej pomocniczej, żeby nie wchodzić w funkcję. Może jest pusta, albo za każdym razem taka sama. Sorry, ale nie chce mi się analizować kodu.
    Brak tego \ to nie był błąd, który vba zgłosi. On zgłaszał to samo co teraz, dlatego komunikat taki sam.
  • #22
    marcinj12
    Level 40  
    swiezak_ wrote:
    Próbuje rozwiązania, które zaproponowałeś i przy uruchomieniu makra z wysyłką pojawia sie komunikat
    "compile error: USER - DEFINED TYPE NOT DEFINED"
    Na którą linijkę kodu wskazuje błąd?

    Było:
    Code: vbscript
    Log in, to see the code

    jest:
    Code: vbscript
    Log in, to see the code

    coś się zmieniło, że zmienił się adres?
  • #23
    swiezak_
    Level 7  
    dodałam w Arkusz1 jeszcze kolumnę z lp, dlatego zmieniłam na F2, bo teraz w Kolumnie B znajdują się wartości, po których filtrowane sa dane i tworzone poszczególne arkusze.
    Przy uruchomieniu makra Wysyłanie
    Podświetla na żółto Sub Wysylanie()

    a na niebiesko zaznacza objOutlook As Outlook.Application
  • #24
    marcinj12
    Level 40  
    A masz na tym komputerze zainstalowanego Outlooka? Zaznaczyłaś referencję do Microsoft Outlook xx.x Object Library ?
  • #25
    swiezak_
    Level 7  
    To komputer służbowy, pracuje stale na Outlooku, ale szczerze powiem, ze nie wiem jak sprawdzić czy referencje sa zaznaczone :/
    Poprosiłam koleżankę o sprawdzenie na swoim komputerze i po dostosowaniu makra, pojawia sie ten sam problem w tym samym miejscu
  • #26
    marcinj12
    Level 40  
    W edytorze VBA wchodzisz w menu Tools->References, musisz mieć tam "zaptaszoną" bibliotekę o nazwie jak powyżej.
  • #27
    cbrman
    Level 27  
    Zmień deklaracje:
    Code: vbnet
    Log in, to see the code

    na
    Code: vbnet
    Log in, to see the code


    Poniższe dwie deklaracje zakomentuj (nie widzę, żeby były gdzieś użyte):
    Code: vbnet
    Log in, to see the code
  • #28
    swiezak_
    Level 7  
    dzięki, nie było "zaptaszone" :) poprawiłam
    zmieniłam też zapis

    Code: vbscript
    Log in, to see the code



    Code: vbscript
    Log in, to see the code



    no i teraz jest tak, ze wywala się na linijce
    Code: vbscript
    Log in, to see the code



    a w folderze Wyniki utworzył się jeden plik ale bez rozszerzenia .xls i w ogóle bez jakiegokolwiek rozszerzenia.

    Dodano po 4 [minuty]:

    poprawiłam i teraz moje makro do wysyłki wyglada tak

    Code: vbscript
    Log in, to see the code



    i dalej pojawia sie ten sam bład, który opisałam w poprzednim poscie
  • Helpful post
    #29
    clubs
    Level 36  
    Witam

    Tu zmień z
    Code: vbnet
    Log in, to see the code


    na
    Code: vbnet
    Log in, to see the code
  • #30
    swiezak_
    Level 7  
    Dziękuję, działa :)
    jest tylko jeszcze jeden problem w utworzonych arkuszach nie działa sumowanie w kolumnie R i błędnie sumuje wartości z przedostatnich wierszy zamiast ostatnich. Dodatków chciałabym, aby w kolumnie G wartości zaokrąglały się do 0 miejsc po przecinku
    Chciała to q taki sposób załatwić , ale nie działa :(
    "=ZAOKR(SUM(L" & ost_wiersz & ":O" & ost_wiersz & ");0)"

    Moge prosic jeszcze o troche uwagi ?

    Dodano po 1 [minuty]:

    jakas emotikona sie wkradła :)


    "=ZAOKR(SUM(L" & ost_wiersz & ": O" & ost_wiersz & ");0)"