[VBA] Łączenie procedury i funkcji

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:


funkcja:


Pozdrawiam

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:

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:


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....

Jezeli Excel >=2007 wtedy cleanName & ".xlsx"

Dlaczego If UCase(Arkusz.Name) <> "Arkusz1"
nie ma byc If UCase(Arkusz.Name) <> "ARKUSZ1"

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ć: , choć nie zagwarantuje to unikalności nazw arkusza w pliku, o ile to problem...

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:

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:

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



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:(



Proszę o pomoc

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ń:na

W TYM MIEJSCU :

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ż na ?

dopiero teraz zmieniłam

teraz przy tworzeniu arkusza wywala się tutaj:




cały kod - stan obecny




Dodano po 18 [minuty]:

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



ale arkusze zostały utworzone , nie zapisały się w folderze "wyniki" i e-mail nie wysłał się

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

no fakt , bezmyślnie przekopiowałam kod.
wiec zakomentowałam ta linijkę



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

No to po kolei...
Cały ten kod:

możesz zamienić na:

a tworzenie nagłówków dodać tam, gdzie rzeczywiście się tworzy arkusze z danymi, czyli:

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.

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



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:




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

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.

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:

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:

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.

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.

swiezak_ wrote:

Może jakąś rada , gdzie konkretnie szukać???

Najlepiej nagrywać fragmenty rejestratorem makr a potem samemu uzyskany kod przeanalizować i poprawić.

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
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

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


[/syntax]

Dodano po 59 [sekundy]:

a i jeszcze jedno, podczas nowoutworzonych arkuszach tworzy się dodatkowo pusta kolumna A - niepotrzebna

Ech... Spróbuj czegoś takiego:

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:

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\".

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)

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.

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:
jest:
coś się zmieniło, że zmienił się adres?

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

A masz na tym komputerze zainstalowanego Outlooka? Zaznaczyłaś referencję do Microsoft Outlook xx.x Object Library ?

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

W edytorze VBA wchodzisz w menu Tools->References, musisz mieć tam "zaptaszoną" bibliotekę o nazwie jak powyżej.

Zmień deklaracje:

na


Poniższe dwie deklaracje zakomentuj (nie widzę, żeby były gdzieś użyte):

dzięki, nie było "zaptaszone" poprawiłam
zmieniłam też zapis







no i teraz jest tak, ze wywala się na linijce



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




i dalej pojawia sie ten sam bład, który opisałam w poprzednim poscie

Witam

Tu zmień z


na

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 & "" & 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)"