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.

VBA Excel 2007 - kopiowanie danych pomiędzy parami plików

monitor78 28 Wrz 2014 17:44 1374 11
  • #1 28 Wrz 2014 17:44
    monitor78
    Poziom 6  

    Witam,

    proszę o pomoc w następującym problemie. Mam pewne koncepcje na realizację ale dopiero chcę rozpocząć naukę VBA i nie potrafię jeszcze tego oprogramować. Widziałem rozwiązania, które wydają się podobne ale nie potrafię ich zaimplementować.

    Założenie:
    1) Istnieją dwa zbiory/foldery plików (c:\zrodlo oraz c:\analiza),
    2) W każdym z nich znajduje się wiele plików, wymienianych co miesiąc na nowe,
    3) Pliki w c:\zrodlo mają powtarzalny układ i nazwy według schematu: zrodloXXX.xlsx, gdzie XXX jest niepowtarzalnym numerem,
    4) Pliki w c:\analiza mają powtarzalny układ i nazwy według schematu: analizaXXX.xlsx, gdzie XXX jest niepowtarzalnym numerem,
    5) Wszystkie pliki mają numer „XXX” wpisany w komórce A1 w arkusz1,

    Cel:
    Skopiowanie liczb i wklejenie jako wartości
    z zakładki „arkusz1”, zakres A10:A15 pliku zrodloXXX.xlsx
    do zakładki „arkusz2”, zakres A20:A25 pliku analizaXXX.xlsx
    elementem łączącym parę plików jest numer XXX

    Pomysł 1:
    1) w folderze c:\zrodlo istnieje plik zrodlo_razem.xlsx zbierający dane z zakresów arkusz1!A10:A15 wszystkich plików zrodloXXX.xlsx z folderu c:\zrodlo. Dane ułożone są w kolumnach, gdzie pierwszy wiersz zawiera numer XXX a dane są w wierszach 10:15 (taki plik mogę stworzyć dodatkiem RDBMerge),
    2) Jestem w pliku zrodlo_razem.xlsx, kolumna A, gdzie w A1 jest numer XXX,
    3) Uruchamiam makro które odczyta numer XXX z pierwszej komórki aktywnej kolumny, odnajdzie plik z numerem *XXX* w nazwie w folderze c:\analiza po czym skopiuje dane z wierszy 10:15 aktywnej kolumny pliku zrodlo_razem.xlsx i wklei wartości do odpowiedniego pliku [analizaXXX]arkusz2!A20:A25.
    4) Powtórzenie manualne kroków 1-3 dla każdej wypełnionej kolumny pliku zrodlo_razem.xlsx (ewentualnie oprogramowanie automatu który powiela czynność aż do ostatniej zapełnionej kolumny).

    Pomysł 2:
    1) Uruchamiam plik analizaXXX.xlsx,
    2) uruchamiam makro które sprawdza w arkusz1!A1 numer (na przykład „XXX”) i wyszukuje w folderze c:\zrodlo plik z numerem *XXX* w nazwie,
    3) kopiuje [zrodloXXX]arkusz1!A10:A15 i wkleja wartości do [analizaXXX]arkusz2!A20:A25,
    4) powtórzenie manualnie kroków 1-3 dla każdego pliku w folderze c:\analiza dla kolejnych numerów.

    Pytanie czy da się to opisać jako makro, bardziej praktyczny wydaje mi się pomysł 1. Oczywiście prośba również o to żeby ktoś napisał coś takiego, na moim poziomie nie jestem w stanie stworzyć takiego kodu, ewentualnie mogę sobie później podmienić ścieżki i nazwy plików na te właściwe.
    Być może ktoś ma inny pomysł na osiągnięcie celu?
    Załączam przykładowe pliki zgodne z założeniem.

    0 11
  • #3 28 Wrz 2014 22:06
    monitor78
    Poziom 6  

    Dziękuję JRV :)
    Szczerze mówiąc trochę mnie przytkało że kilka linijek kodu może robić taką robotę, tym bardziej ochota na start nauki VBA.

    Przetestowałem kilka zmian które będę musiał wykonać i wszystko działa szybko i zgodnie z założeniem. W sumie nawet lepiej niż w pomyśle 1, bo makro kopiuje dane bezpośrednio z plików "zrodlo", plik zbiorczy służy tylko jako lista numerów.

    Mam jeszcze dwa pytania dotyczące otrzymanego kodu. Doczytanie tego zajmie mi trochę czasu a gdyby udało się tutaj wprowadzić te zmiany to zaprzągłbym już makro do pracy.

    Dotyczy linijek "set bs/bd":
    1) widzę że adres arkuszy ustalony jest za pomocą indeksu, prośba o podpowiedź jak zmienić to na stałą nazwę arkusza (np. arkusz1, arkusz2). Pliki mogą różnić się ilością i kolejnością arkuszy ale nazwa będzie stała. nieaktualne, sam doczytałem.
    2) nazwa pliku dopasowana jest do maski z przykładu czyli "zrodlo/analiza & num(c) & .xlsx". W rzeczywistości pliki będą nazwane wg maski: słowo_XXX_słowo.xlsx, gdzie słowa mogą być różne, w związku z tym może coś w rodzaju: *XXX*.xlsx.

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Tak czy siak już jestem zadowolony :-)

    0
  • #4 29 Wrz 2014 17:25
    JRV
    Specjalista - VBA, Excel

    Sheets(1) - Jest to pierwszy arkusz (karta) m.in. tutaj to jest Sheets("Arkusz1")
    *XXX*.xlsx bezpośrednio więc nie można(IMHO)

    Code:

    plk=dir("zrodlo\*.xlsx")
    Do while Not plk Like "*" & num(c) & "*.xlsx"
      plk=dir
    Loop
    Set bs = Workbooks.Open("zrodlo" & plk).Sheets("Arkusz1").Range("A10:A15")
    '....

    Nie testowano...

    0
  • #5 29 Wrz 2014 18:59
    monitor78
    Poziom 6  

    Ten dodatkowy kod niestety wyrzuca błędy albo źle go dodałem (wstawiłem przed linijką "set bs"). Wczoraj i dzisiaj sporo czytałem na ten temat i nie natrafiłem niestety na nic co wyglądałoby jak metoda otwierania plików o masce jakies_slowo_XXX_jakies_slowo.xlsx. W ostateczności zrobię kilka makr do obsługi grup plików o powtarzalnych rodzajach nazw.

    Ostatecznie zmodyfikowałem sobie makro powielając jeszcze 2x polecenie przenoszenia kilku zestawów przykładowych danych, wygląda teraz tak:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Jeśli ktoś zna rozwiązanie problemu adresowania nazwy skoroszytu dla maski "jakies_slowo_XXX_jakies_slowo.xlsx" to będę wdzięczny, jeśli nie to główny problem i tak już rozwiązany :)

    0
  • Pomocny post
    #6 29 Wrz 2014 19:57
    JRV
    Specjalista - VBA, Excel

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Dodano po 9 [minuty]:

    monitor78 napisał:
    powielając jeszcze 2x polecenie

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #7 29 Wrz 2014 21:21
    monitor78
    Poziom 6  

    Jeszcze raz ogromne dzięki JRV za użyczenie wiedzy i poświęcony czas :)

    Te ostatnie dwa makra, które napisałeś, działają bez błędnie na rzeczywistych plikach, prawie skakałem na fotelu z radości :)

    Mam jeszcze (ostatnie mam nadzieję) pytanie: moje pliki małą łącza do innych plików i w trakcie działania makra podczas otwierania każdego pliku muszę potwierdzić komunikat:

    VBA Excel 2007 - kopiowanie danych pomiędzy parami plików

    Czy da się jakoś zautomatyzować kontynuowanie tego komunikatu? Kombinuję z różnymi poleceniami:
    Workbooks.Open(FileName, updatelinks:=0)
    albo
    Application.AskToUpdateLinks = False

    ale na razie bez sukcesu.

    0
  • Pomocny post
    #8 29 Wrz 2014 21:26
    JRV
    Specjalista - VBA, Excel

    Sprobuj Application.DisplayAlerts = False

    0
  • #9 29 Wrz 2014 21:37
    monitor78
    Poziom 6  

    Pomogło :)

    Dzięki jeszcze raz, teraz wszystko śmiga zgodnie z planem :)

    0
  • #10 30 Wrz 2014 10:36
    monitor78
    Poziom 6  

    Życie przyniosło jeszcze jedno pytanie.

    Czy w tej ostatniej wersji kodu, dla przypomnienia:

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    można dołożyć polecenie sumowania wartości z jakiegoś zakresu bs do jednej komórki bd?

    Coś w rodzaju suma bs A10:A15 do komórki bd A20?
    bd.Sheets(2).Range("A20") = bs.Sheets(1).Range("A10:A15").Value

    0
  • #11 30 Wrz 2014 19:04
    JRV
    Specjalista - VBA, Excel

    Code:

    bd.Sheets(2).Range("A20") = application.sum(bs.Sheets(1).Range("A10:A15"))

    0
  • #12 30 Wrz 2014 19:36
    monitor78
    Poziom 6  

    Jak zwykle niezawodnie, dzięki kolejny raz :-)

    Próbowałem wprowadzać różne podobne polecenia ale niestety wszystko wprowadzałem między "bs" a "sheets", zamiast przed "bs" i oczywiście były błędy.

    0