Elektroda.pl
Elektroda.pl
X

Search our partners

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

[Excel] Pobieranie danych z zamknietych plików

Gamblee 31 Oct 2011 23:57 9593 14
  • #1
    Gamblee
    Level 9  
    Cześć,
    Chciałbym stworzyć makro, które pobiera pewien zakres danych i wkleja do jednego pliku. Chciałbym, żeby to odbywało się automatycznie, gdyż dziennie takich plików jest około 120.

    1. Pliki mają zawsze datę na końcu, czyli z dzisiaj było by ...31.10.2011.xls
    2. Pliki zawsze są umieszczane w jednym folderze - np. c:\dane\



    Zrobienie makra, żeby otwierał jeden plik nie jest problemem. Gorzej jak ma pobrać z kilku. Na forum czytałem gdzieś o funkcji FileSearch, ale wyskakuje mi jakiś bład (nie wiem czy to wina VBA v.7 - tamten temat był z przed dwóch lat).

    Na razie mam coś takiego:

    Code: vb
    Log in, to see the code
  • Helpful post
    #2
    adamas_nt
    Moderator of Programming
    Osobiście wolę korzystać z funkcji Dir. FileSerch potrzebuje referencji i lubi stwarzać problemy.
    Poniżej przykład z pętlą. Jeśli daty w nazwach są w stałym formacie można sobie ułatwić podstawiając dzisiejszą datę. Dla wczoraj będzie to Date-1, itd Oczywiście dla wielu plików przypisanie nazwy odbywać się będzie wewnątrz pętli bez wychodzenia, ale z tym sobie pewnie poradzisz.
    Jeśli nie kopiujesz formatu komórki wystarczy przypisanie.
    Code: vb
    Log in, to see the code
  • #3
    Gamblee
    Level 9  
    Chciałbym, żeby to makro wyszukiwało mi ostatni wolny wiersz i tam wklejało dane z plików, czyli:
    Code: vb
    Log in, to see the code

    Niestety moja znajomość VBA jest na razie (mam nadzieje :D ) na niskim poziomie i dołączenie tej części kodu wywala mi błąd.

    Druga rzecz. Pobierane dane nie zawsze są tej samej długości. Czyli z jednego pliku 25 wierszy, a z drugiego 10. Dane mają być pobierane też z kilku kolumn. Chciałbym więc mieć możliwość ominięcia, np kolumny C.
    W załączniku jest przykład pliku, z którego chce pobierać dane.
  • #5
    Gamblee
    Level 9  
    Code: vb
    Log in, to see the code


    Chciałbym trochę poprawić to makro, a mianowicie:
    1. Teraz kopiuje mi komórki z zakresu C7:O81. Chciałbym aby były to tylko komórki niepuste. Mógłbym dodać, żeby szukał ostatniej nie pustej w kolumnie C. Jeśli tak to, w którym miejscu najlepiej to zrobić?
    2. Czas działania danego makra też pozostawia dużo do życzenia. Dziennie kopiowanych jest około 10 tysięcy wierszy. Na pewno było by ich mniej gdyby nie kopiowało pustych, wtedy około 4-5 tysięcy. Czytałem, że ReDim Preserve pochłania dużo pamięci i spowalnia cały proces.

    Zastanawiam się nad wstawieniem tego:
    Code: vb
    Log in, to see the code
  • #6
    adamas_nt
    Moderator of Programming
    Musiałbyś zmienić licznik pętli zmiennej wiersza:
    For k = 1 To ostatnia komórka
    Range(wiele_kolumn).End przeszukuje tylko skrajną, lewą kolumnę. Wyszukuj od pojedynczej i najlepiej od dołu (jeśli zdarzają się "okienka"). Np ostWrs=Range("B65536")>End(xlup).row

    Inne sposoby
    ostW=ActiveSheet.UsedRange.Rows.Count
    ostW=Columns(2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Działanie z tablicą jest dość szybkie. Zmiana rozmiaru (redim) praktycznie nie wpływa na czas działania. Przy przepisywaniu z tablicy można sporo zaoszczędzić wyłączając odświeżanie. Nie zaszkodzi wyłączyć dla całej procedury:
    Application.screenupdating=False i na końcu =True
  • #7
    Gamblee
    Level 9  
    Pomógłbyś mi przerobić to makro przy założeniach:
    1. Ma pobierać wszystkie pliki *.xls i *.xlsx z folderu - na szczęście to makro już to potrafi;
    2. Z arkusza "Ogólne" pobiera imię pracownika z komórki A1, która jest scalona z trzech komórek. Przy próbie zmodyfikowania makra, zaczęło mi pobierać wszystkie 3 i wklejać od kolumny A do C.
    3. Z arkusza "Dane" pobiera dane z zakresu C7:O81, pomijając puste wiersze na podstawie kolumny C.
    4. Wklejać to do nowego skoroszytu bez żadnych odstępów.

    I teraz najgorsze. W tym momencie imię pracownika w nowym skoroszycie jest w kolumnie A, następnie dane są wklejane od kolumny B. Chciałbym, żeby tak zostało. Dodatkowo obecnie imię przypisywane jest do każdego "rekordu" ze skoroszytu danego pracownika:

    A B C
    Imie 1 10 xxx
    Imie 1 20 xxx
    Imie 1 20 xxx
    Imie 2 20 xxx
    Imie 2 30 xxx
    Imie 3 10 xxx
    Imie 3 15 xxx
  • #9
    Gamblee
    Level 9  
    Dzięki wielkie. Imię dodaje tylko do pierwszego wiersza z danego skoroszytu. Ale najważniejsza jest prędkość. Zrobię testy i zaprezentuje wyniki:)

    Czas na wyniki testu:ść
    Ilość plików: 142
    stare makro:
    1. wczytywanie z formularzy: 35sek
    2. Dodawanie: 1min 2sek
    Nowe makro:
    1. wczytanie plus dodanie 20sek :D

    Podsumowanie:
    Podczas używania starego makra musiałem używać dodatkowego makra, które usuwało puste wiersze. Dopóki nie uporam się z dodawaniem imienia do każdego wiersza z tego samego formularza będę to musiał robić albo ręcznie albo korzystać z kolejnego makra, bądź poprawić to (co jest mało prawdopodobne :| )

    Jeszcze raz WIELKIE DZIĘKI :!:
  • #10
    adamas_nt
    Moderator of Programming
    Wyrzuć deklarację tablicy i przypisanie rozmiaru (ostało się), linia: i = i + 1 też niepotrzebna...
  • #11
    Gamblee
    Level 9  
    adamas_nt wrote:
    Wyrzuć deklarację tablicy i przypisanie rozmiaru (ostało się), linia: i = i + 1 też niepotrzebna...


    Czyli? Nie chce usunąć czegoś co jest potrzebne.
  • Helpful post
    #12
    adamas_nt
    Moderator of Programming
    Usuń:
    kom() As Variant
    ReDim kom(22, 1) As Variant
    i = i + 1
  • #13
    Gamblee
    Level 9  
    A mógłbyś mi jeszcze pomóc z tym automatycznym przepisywaniem imienia z pierwszego wiersza każdej tabeli do następnego imienia? Chciałem na koniec zastosować funkcje autofill, ale zmienna jest ilość wierszy. Chyba, że dało by rade zrobić to od razu wklejając arkusze.
  • Helpful post
    #14
    adamas_nt
    Moderator of Programming
    Najprościej. Tę linię
    Code:
    wkb.Sheets(ark).Cells(ostWrs, 1) = ActiveWorkbook.Sheets("Ogólne").Range("A1")
    
    przesuń do bloku pętli For j
    Code: vb
    Log in, to see the code
  • #15
    Gamblee
    Level 9  
    Super. Działa wyśmienicie.