Elektroda.pl
Elektroda.pl
X
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 3759 34
  • #1
    swiezak_
    Level 8  
    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
  • 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 8  
    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"
  • 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 8  
    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 8  
    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 8  
    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 8  
    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 8  
    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 8  
    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 8  
    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 26  
    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 8  
    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 26  
    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 8  
    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 8  
    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 8  
    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 37  
    Witam

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


    na
    Code: vbnet
    Log in, to see the code
  • #30
    swiezak_
    Level 8  
    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)"