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

VBA Excel - Kopiowanie arkuszy z wielu plikow

ikarus21 19 Paź 2013 18:34 4986 15
  • #1 19 Paź 2013 18:34
    ikarus21
    Poziom 6  

    Cześć wszystkim,

    Z góry uprzedzam, ze moja wiedza w zakresie VBA jest znikoma - ogranicza się jedynie do pętli, odwołań do komórek, tworzenia mało skomplikowanych funkcji czy makr.

    Niektórym z was mój problem może wydać się prosty. Przeszukałem parę stron w poszukiwaniu odpowiednich makr lub przerobieniu istniejących, jednak było to bezowocne.

    Chciałbym prosić was o pomoc w stworzeniu makra, które pozwalałoby mi w jednym pliku gromadzić arkusze z kilku innych.

    W pliku nazwijmy go "source" w kolejnych komórkach znajdują się dane plików z których będziemy wyciągać arkusze. Te dane to odpowiednio

    - folder,
    - nazwa pliku,
    - hasło do pliku,
    - nazwa arkusza który chcemy skopiować,
    - nazwa arkusza do którego chcemy skopiować dane.

    Chciałbym aby makro działało w tle, to znaczy nie otwierało plików z których następowałoby kopiowanie. Wystarczy jak makro pobierze same wartości - nie potrzebne są formuły.

    W załączniku przesyłam przykładowe pliki. W pliku "source" znajduje się szkielet makra (heh szkielet to może za dużo powiedziane). Wiem że łatwiejsze jest przekopiowanie arkusza, niż przekopiowanie samej zawartości, dlatego załączone jest tam makro, które usunie zbędne arkusze. Jednak i w takim prostym makrze pojawiły się błędy.

    Z góry dzięki za zainteresowanie tematem i wszelką pomoc.

    0 15
  • SterControl
  • #2 19 Paź 2013 19:35
    cbrman
    Poziom 27  

    Z tego co wiem to nie pobierzesz danych z zahasłowanych plików bez ich otwierania.

    0
  • SterControl
  • #3 19 Paź 2013 20:57
    adamas_nt
    Moderator Programowanie

    ikarus21 napisał:
    Chciałbym aby makro działało w tle
    Ukryta instancja? Będzie "prawie" w tle.
    Zgadzam się z przedmówcą. Musisz pliki otwierać, ale nie musi to być widoczne.
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #4 19 Paź 2013 21:48
    ikarus21
    Poziom 6  

    Dzięki za powyższy kod - ten pomysł z działaniem niewidocznym jest OK. Dodałem go do mojego pliku, jednak na końcu pojawia się błąd " Run-time error '91': ". Nie rozumiem też co chciałeś powiedzieć przez to:

    adamas_nt napisał:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Z tego co widzę, to pozostaje jedynie napisać polecenie, które skopiuje arkusz z jednego pliku do drugiego. Bardzo proszę o pomoc z tym.

    Poniżej zamieszczam zaktualizowany plik z makrem.

    0
  • Pomocny post
    #5 19 Paź 2013 22:58
    adamas_nt
    Moderator Programowanie

    No tak. Mój błąd, bo powinno być w innej kolejności. Sorry, ale dzisiaj sobota ;)

    Coś tam (w załączniku) kopiuje. Przeanalizuj, dostosuj do własnych potrzeb.

    0
  • #6 20 Paź 2013 10:12
    ikarus21
    Poziom 6  

    Wielkie dzięki za pomoc!

    Zmieniłem trochę twój kod do moich celów.

    W twoim kodzie makro kończyło pracę gdy w pierwszej kolumnie napotkało pierwsza pustą komórkę. Potrzebowałem mieć pewność, że wszystkie dane zostaną skopiowane. Dlatego też zmieniłem pętle DO WHILE na pętle FOR i użyłem funkcji

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    W dużym uproszczeniu funkcja ta zwraca ostatnią komórkę, z najmniejszego prostokątnego zakresu zaczynającego się w A1, w którym znajdują się wszystkie niepuste komórki. Może nie jest to do końca jasne, dlatego użyję przykładu. Jeżeli w naszym arkuszu niepuste są tylko komórki D9 oraz G6, to powyższa funkcja zwróci nam komórkę G9. Dlatego też funkcje

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    zwracają odpowiednio numer ostatniego niepustego wiersza i numer ostatniej niepustej kolumny.

    Poniżej zamieszczam cały kod, który został napisany przy pomocy użytkownika adamas_nt.

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    EDIT:
    Jeszcze proszę o małą wskazówkę. Powyższe makro kopiuje same wartości - jak je zmodyfikować, by kopiowało również formatowanie (pogrubianie, kolorowanie itp) oraz formuły (funkcje funkcje typu sum, vlookup).

    0
  • #7 20 Paź 2013 12:46
    adamas_nt
    Moderator Programowanie

    Trzeba będzie wprowadzić copy-paste w miejsce przypisania. Raczej nie obędzie się bez select'ów, ponieważ pliki mamy w różnych instancjach... Spróbuj (nie testowane)

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Najlepiej przerób sobie na kopiowanie całego zakresu (nie komórka po komórce w pętli).

    0
  • #8 21 Paź 2013 22:23
    ikarus21
    Poziom 6  

    OK, popracuje nad tym.

    Makro działa, jednak nie zawsze. Problem pojawia się gdy próbuję otworzyć plik znajdujący się na dysku sieciowym, a który w tym czasie otwarty jest przez innego użytkownika. Jak można ominąć tą kwestię? Próbowałem polecenia

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Jednak to nie eliminuje tego problemu. Gdy samodzielnie otwieram plik w użyciu otrzymuję komunikat
    VBA Excel - Kopiowanie arkuszy z wielu plikow

    0
  • #9 21 Paź 2013 23:14
    adamas_nt
    Moderator Programowanie

    Spróbuj otwierać w trybie 'read only'

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Niczego przecież nie zmieniasz - SaveChanges:=False przy zamknięciu...

    0
  • #10 29 Paź 2013 22:35
    ikarus21
    Poziom 6  

    Przerobilem to makro tak, ze powinno juz dzialac tak jak chce. Kod ponizej

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Pojawia się jednak pewien problem. O ile na moim kompie wszystko działa i się ładnie kopiuje, to jednak gdy chcę użyć tego makra w pracy to ono nie działa.

    Makro przestaje działać już przy pierwszym pliku, a mianowicie:

    - stara zawartość arkusza zostaje wyczyszczona,
    - otwiera się plik z którego będziemy kopiować,
    - niestety nic nie kopiuje się z pliku docelowego,
    - pojawia się komunikat czy zapisać zmiany w pliku z którego kopiowaliśmy mimo, że ustawiliśmy SaveChanges = False,
    - pojawia się komunikat o zawartości schowka mimo, że użyliśmy polecenia oExcel.CutCopyMode = False, które ma czyścić zawartość schowka.

    Tak jak pisałem wyżej, na komputerze domowym nie obserwuje takich problemów - wszystko zostaje skopiowane jak należy. W obu przypadkach pracuje na MS Office 2010. Jakieś pomysły jak to rozwiązać?

    0
  • #11 29 Paź 2013 23:38
    adamas_nt
    Moderator Programowanie

    Skoro czyści arkusz, to dochodzi do linii przed 'oExcel.Sheets(mySfrom).UsedRange.Copy'...
    Wygląda na to, że nie znajduje arkusza i zgodnie z instrukcją "goni" do etykiety 'qInst'.

    Przejdź makro krokowo [F8], lub postaw apostrof przed 'On Error', uruchom i podaj treść komunikatu błędu.

    0
  • #12 29 Paź 2013 23:48
    ikarus21
    Poziom 6  

    OK dam znać jutro. O tyle to dziwne, że to samo makro w którym zamiast kopiowania jest przypisanie działa poprawnie. To każe nam wnioskować, że zarówno plik jak i arkusz istnieją.

    0
  • #13 30 Paź 2013 18:51
    ikarus21
    Poziom 6  

    Gdy okomentuje "On Error.." pojawia sie blad " run time error 1004 Microsoft Excel cannot paste the data".

    Gdy uruchomilem makro krokowo to:

    - po dojsciu do polecenia otworzenia pliku, otwiera go i prosi o haslo. Wpisuje je recznie,
    - po dojsciu do polecenia "ActiveSheet.Paste" przechodzi automatycznie do "qInst:
    oExcel.Quit" nie zwracajac przy tym zadnego bledu.
    - po kolejnej probie pojawia sie blad " run time error 1004 Microsoft Excel cannot paste the data".

    Tak jak mowilem makro sie wysypuje, jednak w pamieci przechowuje dane ktore skopiowal z pliku zrodlowego, a ktorych nie wkleil do pliku z makrem.
    Przy probie wklejenia zawartosci schowka do plik dostaje komunikat "Microsoft Excel cannot paste the data".

    PS. Przepraszam za brak polskich znakow - w pracy nie mam polskiej klawiatury.

    0
  • #14 30 Paź 2013 19:52
    adamas_nt
    Moderator Programowanie

    ikarus21 napisał:
    po dojsciu do polecenia "ActiveSheet.Paste" przechodzi automatycznie
    Spróbuj wcześniej Thisworkbook.Activate (przed sheets(mySto).select)
    Makro umieściłeś w module?

    Może właściwiej (dla copy-paste) będzie otwierać pliki w jednej instancji? Jeśli wyłączysz odświeżanie, nie będzie znowu tak bardzo rzucało się w oczy...

    0
  • #15 02 Lis 2013 18:25
    ikarus21
    Poziom 6  

    Cześć.

    Przepisałem jeszcze raz makro do poniższej postaci:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Nie sprawdzałem jego działania w pracy, ale na komputerze domowym działa. Jedyne ale to takie, że na sam koniec pojawia się komunikat jak w załączniku. Macie pomysł jak to naprawić?

    0
  • #16 02 Lis 2013 19:27
    JRV
    Specjalista - VBA, Excel

    row = Cells.SpecialCells(xlCellTypeLastCell).row - może być puste
    row=cells(rows.count, 3).End(xlUP).row

    Aby przetestować:
    row = Cells.SpecialCells(xlCellTypeLastCell).row
    MsgBox row

    0