Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel - makro kopiujące kolumnę z pominięciem pustch komórek do nowego pliku

UqashSL 06 Lip 2012 00:08 8161 22
  • #1 06 Lip 2012 00:08
    UqashSL
    Poziom 6  

    Witam serdecznie. Jestem nowym użytkownikiem trochę początkującym w makrach i VBA w Excelu aczkolwiek mam problem:

    Chciałbym napisać makro kopiujące kolumnę komórek z pominięciem pustych do NOWEGO pliku (ścieżka stała, nazwa zależna od 1 komórki w arkuszu) .xls

    Nawet nie wiem czy potrzebna jest pętla czy wystarczy sprytne makro.

    Proszę o przykład na takim pliku:

    1. Nazwa pliku: "Arkusz1.xls"
    2. Zakres komórek do skopiowania: Q2:Q1000
    3.Zadanie: kopię komórek z pominięciem pustych wkleić do nowego pliku .xls o nazwie "<nazwa_pobrana_z_komórki_np_A1.xls> w folderze o ścieżce: U:Test/05072012/

    Proszę o pomoc!

    Uqash

    0 22
  • #2 07 Lip 2012 17:48
    adamas_nt
    Moderator Programowanie

    Mnóstwo przykładów masz na forum. Patrz "podobne tematy" u dołu ekranu, lub użyj wyszukiwarki. A na czym ugrzęzłeś?

    0
  • #3 08 Lip 2012 21:11
    UqashSL
    Poziom 6  

    Nie wiem jak napisać pętlę, która będzie potrafiła
    kopiować tylko niepuste komórki ze wskazanej kolumny.
    Załóżmy, że w Arkuszu1 w kolumnie A jest 2000 komórek, z czego dane znajdują
    się tylko w 500 z pustymi w różnych miejscach pomiędzy.

    Chciałbym napisać makro, które utworzy nowy plik we wskazanej lokalizacji (stałej - U:\Lukasz\Raporty\) o zmiennej nazwie (zależnej od jednej komórki w Arkuszu1 - komórki B1, w której będzie funkcja łącząca teksty z trzech innych komórek - w tym m.in. datę i tu zaczyna się pierwszy problem, jeśli w formacie komórki wybiorę data, to oczywiście funkcją łącząca tekst rozumie ją jako liczbę... (przeglądałem wiele tematów, ale po prostu albo jestem za słaby w kodzie, że nie umiem tego zastosować by to dobrze działało, albo po prostu źle to robię) .
    Po utworzeniu nowego pliku w ww lokalizacji o nazwie bazującej na komórce B1 w Arkuszu1 (np. U:\Lukasz\Raporty\Raport_2012-07-08_I_ZM.xls), w nowym pliku ma skopiować dane z Arkusza1 (z zakresu A1:A2000 - tylko niepuste komórki) do kolumny D.
    Nawet nie jestem pewny, że potrzeba do tego pętli, może wystarczy sprytne makro ale to co nagrałem naprawdę zamula bo działa na zasadzie, utwórz nowy arkusz, otwórz go, skopiuj z Arkusza1 komórki do nowego arkusza, wstaw autofiltr - pokaż tylko niepuste, zapisz plik, zamknij plik) - trwa to około kilku minut. Gorzej - "Application.ScreenUpdating = False" nie działa, bo mimo zastosowania tej linijki, okienka skaczą, minimalizują się, maksymalizują, otwierają i zamykają na podglądzie!.

    Pomysły?

    0
  • Pomocny post
    #4 08 Lip 2012 21:38
    Aldrin
    Poziom 22  

    1. Załóż filtr w oryginalnej tabeli (niepuste) -> zaznacz całą tabelę i wtedy dopiero kopiuj dane. Może nawet lepiej wklejać "tylko wartości" niż dane z pełnym formatem każdej kopiowanej komórki. Zrobisz to w jednym ruchu zamiast korzystać z pętli (2000 razy).
    Po nagraniu makra można edytować zaznaczony zakres tak, żeby najlepiej nam odpowiadał.

    2. Może wygodniej będzie przemieszczać sie między arkuszami w obrębie tego samego skoroszytu?
    3. Czy potrzebne jest do tego makro skoro jest to czynność jednorazowa i do zrobienia "od ręki"?

    Biorąc jednak pod uwagę adres w B1 mam wrażenie, że to tylko część większej pracy i zaraz będą następne pytania. Jeśli tak, to lepiej od razu przedstawić całość ;)

    Cytat:
    jeśli w formacie komórki wybiorę data, to oczywiście funkcją łącząca tekst rozumie ją jako liczbę

    Data zawsze jest liczbą, nawet jeśli po formatowaniu takiej komórki widać "czwartek". Formatowanie to sposób prezentacji danych na ekranie - nie zmienia wartości komórki. Tak samo jest w Wordzie: jeśli napiszesz "czerwony młotek" i ustawisz kolor tekstu na zielony, to nadal będzie tam napis "czerwony młotek".

    Zerknij na funkcję Tekst, np. =TEKST(B2;"dd-mm-rrrr dddd")
    gdzie w komórce B2 jest data.

    0
  • #5 08 Lip 2012 21:54
    UqashSL
    Poziom 6  

    Pracuję nad arkuszem, który będzie służył jako baza danych i integralną częścią tego arkusza jest opcja zrobienia do niego raportu jako nowego pliku, owszem dobrze zauważyłeś, że można to zadanie wykonać ręcznie, lecz na tym pliku będą pracować ludzie, którzy nie mają zielonego pojęcia o komputerze, więc wszystko musi działać na jedno kliknięcie w odpowiednio opisany przycisk do którego będzie podpięte makro...
    Problem w tym, że nie mogę wkleić kodu na forum, gdyż nad plikiem pracuję w pracy, gdzie nie ma możliwości wrzucenia z zewnątrz czegoś na dysk lub wyeksportowania na pendrive :P, oczywiście net jest również niedopuszczany i niepodpięty (zabezpieczenia w bankowości :/), dlatego bawię się w opisywanie sytuacji niż wrzucić konkrety ;(

    Mimo wszystko byłbym wdzięczny za proste podpowiedzi.

    1) Czy potrzebna pętla czy styknie makro?

    2) No i jak poradzić sobie z poleceniem stworzenia nowego pliku xls. o nazwie zależnej od danej komórki (Wiem że dużo w necie o tym jest ale mi to po prostu nie działa, poproszę o dobry czysty bez dodatków przykład).

    3) No i co zrobić z tym problemem by nie uwzględniał w trakcie kopiowania pusty ch komórek - tu nie mam pojęcia jak sobie poradzić, bo na razie śmiga to łopatologicznie napisane (nagrane makro) ale to tak nie może hulać - migać i w ogóle, cała akcja musi się dziać w tle - to bardzo ważne.

    Jeszcze raz sorry, że nie mogę wrzucić konkretów i pojawiłem się tu jak filip z konopii i chciałbym pomoc nie dzieląc się swoją pracą - po prostu nie mogę, nie mam możliwości skopiowania kodu :/

    EDIT

    Aldrin napisał:
    Zerknij na funkcję Tekst, np. =TEKST(B2;"dd-mm-rrrr dddd")
    gdzie w komórce B2 jest data.


    - super działa ! No widzisz, takie proste a ja tepy nad tym ślenczę i ślenczę!

    Uqash

    0
  • #6 08 Lip 2012 21:56
    Aldrin
    Poziom 22  

    Nowy plik: zarejestruj makro, które tworzy nowy plik - kod jest jak na dłoni.
    Gorzej, że są z tym problemy:
    - może używasz niedozwolonych znaków?
    - może chciałbyś stworzyć plik w podkatalogu, który nie istnieje?

    Dużo lepiej pracuje się na jakimś przykładzie, zwłaszcza gdy "coś" nie działa, bo przyczyn może być wiele.

    Jeśli już nowy plik tam powstanie, to przy następnym uruchomieniu makra trzeba albo plik usunąć (Kill) albo go otworzyć i edytować. Nie/Obecność pliku sprawdzisz przez Dir.

    0
  • Pomocny post
    #8 08 Lip 2012 22:08
    adamas_nt
    Moderator Programowanie

    Dużo do wyjaśniania, więc przeanalizuj sobie taki przykład makra z pętlą

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #9 08 Lip 2012 22:19
    UqashSL
    Poziom 6  

    Rozumiem kod tylko coś nie działa, gdyż tworzy plik w dobrym miejscu o dobrej nazwie tylko nic nie kopiuje jak robię to na przykładowym arkuszu i jeszcze jedno z kodu wynika, że ma kopiować wiersz a mnie chodziło o kolumnę:

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    <- rozumiem, że to zmienić trzeba na kolumnę tylko jak to się wpisuje w kodzie?

    PS gdzie w kodzie jest napisane który wiersz ma kopiować bo tego nie widzę...


    EDIT

    Widzę, że jednak źle rozumiem. Dobrze jest napisane, że ma kopiowoać kolumnę 1 do nowego pliku do kolumny 4 ale od drugiego wiersza zaczynając? Tak ten kod należy rozumieć? :

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    ...tylko czmemu nie kopiuje ?

    0
  • Pomocny post
    #10 08 Lip 2012 22:38
    Aldrin
    Poziom 22  

    zamiast: If Cells(wrs, 1) <> "" Then
    powinno być: If .Cells(wrs, 1) <> "" Then

    (trzeba dostawić kropkę)

    Niemniej jednak proponuję najpierw odfiltrować całą oryginalną kolumnę (niepuste) i skopiować wszystko za jednym zamachem do nowego arkusza - trwa jedno mrugnięcie okiem.

    0
  • #11 08 Lip 2012 22:45
    UqashSL
    Poziom 6  

    Aldrin napisał:
    zamiast: If Cells(wrs, 1) <> "" Then
    powinno być: If .Cells(wrs, 1) <> "" Then

    (trzeba dostawić kropkę)


    SUPER ! Działa :) PS zauważyłem kropkę ;)

    A jeśli mógłbyś mi powiedzieć, co trzeba wpisać by makro pozwalało raz stworzony nowy arkusz nadpisać, czyli powiedzmy żeby pozwalało na stworzenie nowego pliku w tym samym miejscu z nowymi danymi a taką samą nazwą, bo narazie się buguje w razie takiej sytuacji?

    EDIT
    Aldrin napisał:

    Niemniej jednak proponuję najpierw odfiltrować całą oryginalną kolumnę (niepuste) i skopiować wszystko za jednym zamachem do nowego arkusza - trwa jedno mrugnięcie okiem.


    Szczerze, podoba mi się bardzo ta pętla bez filtrowania póki co. Na razie trenuję na czyściochu ale zaimplementuję ten kod w pracy na 100% tylko kilka dopracować szczegółów muszę. PS jaki pomysł z tym nadpisywaniem?

    COFAM ! Działa wszystko, zbugował się bo chciał nadpisać na otwartym pliku. (sic!) ;P

    0
  • #12 08 Lip 2012 22:53
    adamas_nt
    Moderator Programowanie

    Jedna zapomniana kropka a jaka różnica ;)
    :arrow: Aldrin punkty za spostrzegawczość!

    Do autora: polecenie SaveAs napotykając na plik z identyczną nazwą wyświetli standardowy komunikat. Można go pominąć wyłączając alerty, ale...

    0
  • #13 08 Lip 2012 23:03
    Aldrin
    Poziom 22  

    UqashSL napisał:
    Szczerze, podoba mi się bardzo ta pętla bez filtrowania póki co.

    Mnie też sie podoba, bo jest bardzo ładna :)
    Myślałem raczej o optymalizacji kodu.


    UqashSL napisał:
    A jeśli mógłbyś mi powiedzieć, co trzeba wpisać by makro pozwalało raz stworzony nowy arkusz nadpisać, czyli powiedzmy żeby pozwalało na stworzenie nowego pliku w tym samym miejscu z nowymi danymi a taką samą nazwą, bo narazie się buguje w razie takiej sytuacji?


    Właściwie to już napisałem - w poście 6:
    Cytat:
    Jeśli już nowy plik tam powstanie, to przy następnym uruchomieniu makra trzeba albo plik usunąć (Kill) albo go otworzyć i edytować. Nie/Obecność pliku sprawdzisz przez Dir.

    Powinieneś sprawdzić, np. przez Dir czy taki plik juz istnieje i jeśli jest, to go skasować (Kill).
    Jeśli istnieje, to dobrze byłoby ciut wcześniej sprawdzić czy przypadkiem ten plik nie jest otwarty, bo wtedy go nie skasujesz. Wtedy albo plik zamknąć albo... wyczyścić z danych i pracować na nim.

    Moim zdaniem, całkowicie subiektywnym, najlepiej byłoby stworzyć gotowy plik raportu i tylko edytować dane (kasować stare i zapisywać aktualne). Pomyśl, że przecież trzeba ten plik "ładnie" opakować: jakieś nagłówki, opisy, wielkość czcionki czy inne kolory... Dlatego zrobiłbym to raz.

    Druga sprawa: może warto przed skasowaniem starego raportu zrobić backup takiego pliku?

    (zmykam dalej do pracy, bo jeszcze trochę mi zostało)

    Dodano po 3 [minuty]:

    adamas_nt napisał:
    Jedna zapomniana kropka a jaka różnica ;)

    Dziekuję ;)
    Kropki potrafią napsuć krwi :!:

    0
  • #14 08 Lip 2012 23:11
    UqashSL
    Poziom 6  

    Aldrin napisał:
    Moim zdaniem, całkowicie subiektywnym, najlepiej byłoby stworzyć gotowy plik raportu i tylko edytować dane (kasować stare i zapisywać aktualne). Pomyśl, że przecież trzeba ten plik "ładnie" opakować: jakieś nagłówki, opisy, wielkość czcionki czy inne kolory... Dlatego zrobiłbym to raz.


    Na ten pomysł także wpadłem ale sobie już <chyba> poradzę dalej :) Jak będę miał wciąż problemy to znów się odezwę bo naprawdę rozwiązaliście mi problem nad którym spędziłem kupę czasu !!! Dzieeeeeeeeki wielkie ! Dziękuję bardzo za sugestie

    Aldrin napisał:
    Druga sprawa: może warto przed skasowaniem starego raportu zrobić backup takiego pliku?


    Akurat nie jest to potrzebne ale dziękuję za pomysł :)


    EDIT

    I już wracam z problemem, poniżej kod jaki aktualnie mam dzięki Wam napisany na którym trenuję:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    plik = Dir("D:\Lukasz\Raporty\"Raport " & Str(Date) & " adamas") ' tu mi wywala błąd, co jest z tą składnią nie tak???

    0
  • #15 09 Lip 2012 15:38
    adamas_nt
    Moderator Programowanie

    UqashSL napisał:
    plik = Dir("D:\Lukasz\Raporty\"Raport " & Str(Date) & " adamas") ' tu mi wywala błąd, co jest z tą składnią nie tak???
    Jest nie tak. Powinno być Np
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #16 09 Lip 2012 17:13
    UqashSL
    Poziom 6  

    Cytat:
    Jest nie tak. Powinno być Np

    Kod Visual Basic - [rozwiń]
    plik = Dir("D:\Lukasz\Raporty\Raport " & Str(Date) & " adamas.xls")


    Zdążyłem to wczoraj wybadać :) Teraz hula ja ta lala :) ale dziękuje.

    0
  • #17 11 Lip 2012 21:23
    UqashSL
    Poziom 6  

    No i nadszedł problem :/
    Mam taki kod:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Problem jest w tym miejsu:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Jak zmienić mu "For wrs = 1" na "For wrs = "zależne od ostatnio wczytanych danych - od ostatniej pozycji w kolumnie A, czyli Lp" - mam tu na myśli taką sytuację, jeżeli zastosuję pętlę, zapiszę plik, który ostatnią pozycję w zakładce do zczytywania czyli "NO_01" w kolumnie A zczyta jako pozycja nr 456 (456 to wartość w komórce), i chciałbym, by pętla uruchamiana ponownie zaczynała przeszukiwać zakładkę "Baza" nie od "For wrs = 1" tylko "For wrs = 456"; możecie jakoś pomóc?

    Uqi

    0
  • #18 11 Lip 2012 22:54
    adamas_nt
    Moderator Programowanie

    UqashSL napisał:
    Jak zmienić mu "For wrs = 1" na "For wrs = "zależne od ostatnio wczytanych danych - od ostatniej pozycji w kolumnie A
    Przez podstawienie Nr ostatniego wiersza do licznika pętli, 'For wrs = Range("A" & Rows.count).End(xlUp).Row to ....'

    Musiałbyś tylko sprawdzać, czy nie jest większy od drugiego argumentu (pętla pojedzie wstecz), lub dodawać stałą liczbę.

    0
  • #19 11 Lip 2012 23:01
    Aldrin
    Poziom 22  

    Niech zgadnę - ten kod z dodatkowym kopiowaniem 7 komórek w każdym wierszu działa strasznie wolno i chcesz zrobić to na raty?

    Tak czy inaczej: na końcu makra trzeba przechować numer ostatniego wiersza z pliku źródłowego, a skoro i tak coś jest przechowywane, to można też zapamietać numer ostatnio wpisywanego wiersza w pliku docelowym. Wartości te można zapisać choćby w pliku zewnętrznym i odczytać gdy zajdzie potrzeba, ale najprościej i najszybciej zapisać je w 2 komórkach arkusza i pobrać te wartości na początku makra. Dla ułatwienia można je przypisać do zmiennych.

    Jeśli zaś prawdziwe jest zdanie pierwsze z tego postu to optymalizowałbym kod.

    0
  • #20 12 Lip 2012 08:23
    adamas_nt
    Moderator Programowanie

    Aldrin napisał:
    Jeśli zaś prawdziwe jest zdanie pierwsze z tego postu to optymalizowałbym kod.
    Nie przyglądałem się wcześniej, a zawsze można coś poprawić.

    Zrobiłem sobie próby na 200 wypełnionych wierszach. Obadałem czas działania makra metodą 'copy' versus przypisanie.
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Wyniki: kopiowanie 0,2402 s. przypisanie 0,0928 s.
    Wyłączenie odświeżania wydłuża czas 'copy', odrobinkę skraca w drugim przypadku.
    Gdyby jeszcze ograniczyć zakres j.w. opisano...

    0
  • #21 12 Lip 2012 15:19
    Aldrin
    Poziom 22  

    W takim razie mamy zupełnie odmienne wyniki testów :!:

    Z testów, które robiłem gdy pisałem o optymalizacji (dane dla 2000 wierszy):
    - kopiowanie komórek pojedynczo (z jednej kolumny) - czas 5.26 - 5.6 s, z czego czasy krótsze dla liczb, a dłuższe dla komórek z tekstem.

    - kopiowanie komórek pojedynczo ze wszystkich 7 kolumn - czas 36.8 - 37,18 s.


    - kopiowanie zakresu 2000 x 7 w JEDNYM ruchu - czas na poziomie 0.06 - 0.08s, w tym utworzenie nowego pliku do którego kopiowane są dane.

    Wszystkie testy oczywiście przy wyłączonym odświeżaniu ekranu.

    0
  • #22 12 Lip 2012 23:44
    UqashSL
    Poziom 6  

    adamas_nt napisał:
    Przez podstawienie Nr ostatniego wiersza do licznika pętli, 'For wrs = Range("A" & Rows.count).End(xlUp).Row to ....'

    Musiałbyś tylko sprawdzać, czy nie jest większy od drugiego argumentu (pętla pojedzie wstecz), lub dodawać stałą liczbę.


    Jeszcze nie zdążyłem tego nanieść, jak tylko znajdę chwilkę, dam znać co z tego wyszło o ile znów się nie zatnę na czymś prostym.
    Dzięki

    0
  • #23 22 Sie 2012 18:02
    jomsborczyk
    Poziom 7  

    podłączę sie pod temat bo mam podobny roblem ;/
    potrzebuje makro ktore kopiowaloby komorki z kolumny z bazowego pliku do nowego skoroszytu ale co ktoras komorke (przesuniecie 'r' docelowo podawane z inputbox'a)
    mam cos takiego:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    podejrzewam ze nie kopiuje bo zaznaczeniu nowego arkusza nie wie skad te kom kopiowac. grzebie z vba od niecalych doch tyg wiec dla mnie to zagadka jak poprawnie sie odwolacw tym kodzie... prosze o pomoc :)

    edit:
    poradziłem sobie:
    Kod: vb
    Zaloguj się, aby zobaczyć kod


    ładnie sie generuje, problem tylko w tym ze pierwszy rekord w komorce (qty,1)...
    jak podac by zaczynal wypisywac od (1,1)

    0