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

[Excel 2007] - makro do pobierania danych z innych plików *.xlsx

26 Paź 2013 20:45 12360 17
  • Poziom 12  
    Witam.
    Ponieważ jestem zupełnie zielony w tworzeniu makr w excelu, chciałbym prosić Was o pomoc =) Od dwóch dni walczę ze stworzeniem arkusza, który ma mi ułatwić pracę; otóż codziennie będę wprowadzał dane do kilkunastu plików xlsx, a raz w miesiącu suma danych z poszczególnych plików będzie przenoszona do pliku o nazwie Raport.xlsx. Z samymi arkuszami sobie poradziłem, chociaż było ciężko po tak długiej przerwie od excela, no i musiałem poznać kilka nowych dla mnie formuł. Ale udało się =)
    Teraz chodzi o to, żeby stworzyć w pliku Raport.xlsx makro, które;
    - będzie pobierało dane z tych 30 plików w podfolderze - lokalizacja bezwzględna - nie wiem czy to jest możliwe, żeby skrypt szukał plików o danej nazwie w we wszystkich podfolderach? Znaczy bez podawania nazwy konkretnego folderu (?),
    - pobierane będą dane z zakresu komórek B34:S34 - z każdego pliku ten sam zakres, ale w pliku Raport.xlsx będą one wklejane pod sobą czyli, zakres B34:S34 z pliku 1.xlsx będzie wklejony w zakres C2:T2 pliku Raport.xlsx; plik2.xlsx - C3:T3; plik3.xlsx - C4:T4, itd.
    - button w kolumnie B2:B30 "Pobierz".
    Wiem, że proszę o wiele - nie jestem zwolennikiem gotowców, ale niestety brakuje już mi czasu, tworzenie tych arkuszy trochę mnie przerosło i pochłonęło więcej czasu niż się spodziewałem...
    W każdym razie z góry dziękuję za każdą pomoc.
    Pozdrawiam.
  • Specjalista - VBA, Excel
    1. Nie jest jasne, gdzie znajdują się pliki - " tych 30 plików w podfolderze " łub "plików o danej nazwie w we wszystkich podfolderach" ?
    2. Kolejność wstawiania danych jest ważna?
    3. Jeśli nie, gdzie należy zauważyć w Raport.xlsx, który plik danych?

    Jeśli naprawdę pliki mają nazwy plik1.xlsx, plik2.xlsx i t.d. to następnie sekwencji jest jasne

    Dodano po 46 [minuty]:

    z drugiej strony to wszystko można zrobić bez makro. prawie jeden raz kiedy otworzyć plik1.xlsx w Report.xlsx w C2 wprowadzić formułę =[plik1.xlsx]Arkusz1!B34 w D2 =[plik1.xlsx]Arkusz1!C34. i tak do T2.
    Zamknij plik 1.
    Otworzyć plik 2 i zrobić podobnie w C3... T3

    Po otwarciu pliku Report w kwestii lub odnowić powiązanych danych odpowiedzi tak
    Przepraszam za złe gramatyki to tłumaczenie maszynowe
  • Poziom 12  
    1. Ścieżki plików również jeszcze nie znam - to znaczy na pewno będzie ona zmieniana, dlatego chciałbym zastosować adresowanie bezwzględne - kojarzę z dawnych czasów, że w powerpoincie była taka możliwość, ale też nie jestem do końca pewien, bo być może po prostu plik był integrowany z prezentacją... Najlepiej by to wyszło w ten sposób, że jest folder np. o nazwie "test" i w nim znajduje się plik raport.xlsx i folder o nazwie "test2" w którym znajdują się pliki xlsx z których będą pobierane dane.
    2. Kolejność jest nieistotna, ważne, żeby pobrało dane ze wszystkich plików.
    3. W raport.xlsx nie musi być informacji z którego pliku dane pobrano - planuję w pierwszej kolumnie zrobić liczbę porządkową.

    Szczerze pisząc już próbowałem z tą formułą, którą zaproponował szanowny kolega, ale nie spodobało mi się to, że jakakolwiek modyfikacja nazwy czy też ścieżki podanego w formule pliku powoduje wykasowanie całej formuły przy kolejnym otwarciu. Czy można tutaj zastosować adresowanie bezwzględne lokalizacji pliku? Wstępnie próbowałem z "$" ale nic nie pomogło. Szukałem trochę w materiałach pomocniczych, ale żadnych konkretów na ten temat nie ma...
    Też odświeżanie danych działa tak, że trzeba kliknąć w komórkę i dopiero wtedy pobierze nowe dane - chyba, że jest możliwość, żeby jedną formułką odświeżyć wszystko?

    Dziękuję za zainteresowanie =)
    Pozdrawiam.
  • Moderator Programowanie
    Potrzebna byłaby formuła oparta o ADR.POŚR, ale tu znowu zbyt wiele niewiadomych.
    Jeśli makro, mogłoby być coś podobnego do
    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Oczywiście zamiast pobierać z Imputbox'a, możesz przypisać na stałe.
  • Specjalista - VBA, Excel
    w związku z powyższym następnie trzeba makro. Ale dziś jest zmęczony, to lepiej robić na nową głowę

    dobrze... Podczas pisania, niektóre już opublikowane
  • Pomocny post
    Moderator Programowanie
    W związku z pytaniem na pw.
    Utworzyłem sobie plik w c:\ o nazwie plik1.xlsx, w którym wpisałem tekst w komórki Od B34 w prawo. Następnie uruchomiłem makro (c:\ w InputBox'ie). Dane pięknie skopiowały się do "Arkusz2", komunikat: "skopiowano z 1 pliku".

    Możliwe przyczyny błędu:
    -podajesz złą lokalizację
    -lokalizacja jest dobra ale nie ma tam plików
    -nazwy plików nie są zgodne z założeniami.

    Edit: błąd był również w makro :( oba wywołania były z path2. Sorry.
    Załącznik podmieniłem
  • Poziom 12  
    Ok, w warunkach testowych wszystko działa - sorry nie zrozumiałem tego, że nazwa pliku jest wstępnie zdefiniowana "plik..." - mój arkusz nazywał się inaczej. Teraz jeśli będę chciał zmienić nazwę pliku to rozumiem, że kodzie muszę podmienić wszystkie "plik" na nazwę którą nadam plikom? Czy coś przeoczyłem?
    Chciałbym jeszcze dodać jakiś przycisk-odwołanie do tego makra na arkuszu - od czego powinienem zacząć?

    EDIT:
    Jeszcze jeden problem - to polecenie kopiuje mi całe formuły z komórek - jest możliwość, żeby kopiowane były tylko wyniki? Jeśli to to jakoś to uda mi się obejść =)
    EDIT2:
    Zauważyłem też, że przy większej ilości plików, dane są wstawiane w ten sam zakres komórek, a nie pod sobą - przez co dane są zastępowane przez kolejne odczyty.
  • Specjalista - VBA, Excel
    Jeśli go przy założeniu, że jest folder i w nim znajduje się plik raport.xlsm i folder w którym znajdują się pliki xlsx z których będą pobierane dane, następnie prosty wariant makro

    nazwy plików nie ma znaczenia, ważne jest, aby mieć w jednym folderze, (jeden)
  • Poziom 12  
    JRV napisał:
    Jeśli go przy założeniu, że jest folder i w nim znajduje się plik raport.xlsm i folder w którym znajdują się pliki xlsx z których będą pobierane dane, następnie prosty wariant makro

    nazwy plików nie ma znaczenia, ważne jest, aby mieć w jednym folderze, (jeden)


    Witam =)
    Ustawiłem wszystko wg założeń i pobieranie danych działa bardzo ładnie.
    Niestety jest pewien problem, ale z mojej winy. Przeoczyłem jedną sprawę - żeby dane były wpisywane wg odpowiedniej kolejności - w innym przypadku powstanie chaos... Stworzyłem 10 testowych plików i w każdym z nich umieściłem inne dane w tym zakresie komórek - dla pliku o nazwie "1" wpisywałem same "1", w pliku o nazwie "2" same "2", itd. W arkuszu o nazwie "Report" stworzyłem kolumnę Lp. od 1 do 10 i teraz z mojego założenia, o który wcześniej nie pomyślałem, powinno być tak, że dane z pliku o nazwie "1" są wpisywane do wiersza zaczynającego się od Lp. 1, plik o nazwie "2" - Lp. 2, itd. Oczywiście pliki nie będą się tak nazywać - ale zawsze będą ustawione w folderze wg określonej kolejności i teraz bardzo ważne aby wg tej samej kolejności zostały pobrane dane.
    Wybaczcie - całkowicie wyleciało mi to z głowy :(
    Jeszcze jedna kwestia - w chwili obecnej folder musi się nazywać "jeden", żeby dane zostały pobrane. Gdzie mogę edytować tą nazwę w kodzie? Nie znalazłem w nim frazy "jeden" i na tym się kończy moja wiedza w tym temacie :/
    Dziękuje za pomoc - i tak już dla mnie bardzo dużo zrobiliście w tym temacie :-)

    Pozdrawiam.
  • Specjalista - VBA, Excel
    Miałem na myśli że plików nie może być więcej niż jednego folderu,(na tym etapie)
    przez tłumaczenie maszynowe Pan mógł nie zrozumieć mnie...

    o kolejności spytałem specjalnie... kolejność może dostarczyć na dwa sposoby: albo nazwa pliku będzie zawierać numer (na początku lub na końcu), lub pliki są w ich własnych folderów, które są również tytuł powinien zawierać numer, lub nazwę arkusza może zawierać informacje o kolejności, lub definovana komorka,
    lub jak Pan sam określa jego sposób, tylko nie delegowania w folderze(cytat: "ustawione w folderze wg określonej kolejności") - bo to jest nie zawsze jednoznacznie
  • Poziom 12  
    To bardzo dziwny zbieg okoliczności - bo jak nazwę folder inaczej niż "jeden" to wyskakuje mi błąd "Run-time error "76" : Path not found". Sprawdzałem kilka różnych nazw i tylko "jeden" działa bezbłędnie.
    Co do kolejności odczytu to faktycznie mój błąd - wcześniej pliki testowe zostały nazwane od 1..11 i rzekomy chaos powstawał przez to, że najpierw odczytywane były plik 1,10,11. Problem rozwiązałem dając w nazwie "001, 002, ..." na początku.

    EDIT:
    Problem z nazwą folderu polega na tym, że musi być to nazwa liczbowa - skrypt działa jak nazwę folder: "jeden" lub "1" "dwa" lub "2", itd.
    W takim wypadku nie jest to już duży problem dla mnie, ale bardzo bardzo mnie ciekawi dlaczego tak się dzieje? =)
    Pozdrawiam.
  • Pomocny post
    Specjalista - VBA, Excel
    makro zakłada, że w folderze jest Report.xlsm i folder z plikami danych. Być może jest coś jeszcze oprócz Report. i tego folerza

    edit:
    błąd: wyrażenie "GetAttr(fld) And vbDirectory" musi być w nawiasie,
    chyli (GetAttr(fld) And vbDirectory)
  • Poziom 12  
    Faktycznie w folderze był jeszcze jeden plik xlsx. Przeniesienie go poza folder pomogło.
    Na chwilę obecną wszystko pięknie działa =)
    Chciałbym bardzo kolegom podziękować za poświęcenie mi swojego czasu =) Czasy są jakie są, ale to miłe, że nadal można polegać na innych ludziach :-)
    Przy okazji nauczyłem się kilku nowych rzeczy.

    Problem uważam za rozwiązany.

    Jeszcze raz wielkie dzięki.
    Pozdrawiam.
  • Poziom 9  
    Witam, stworzyłem makro które pobiera dane z zamkniętego pliku i wkleja dane w wyznaczone miejsce w drugim pliku.
    Z makrami dopiero początki, więc kopiuje gotowe kody i próbuje je odpowiednio dopasować


    Kod: vbnet
    Zaloguj się, aby zobaczyć kod


    Potrzebuje pomocy jak zrobić, aby kopiowane wartości nie były sformatowane jak w pliku źródłowym. Jak zaciągam dane to zamiast wielkość czcionki 8(jak ustawiłem w całym arkuszu) w zaciągniętych komórkach jest wielkość 11, czyli tyle ile w oryginalnym pliku.

    Jak zaciągałem pliki za pomocą kodu:

    Kod: vbnet
    Zaloguj się, aby zobaczyć kod


    Formatowanie się nie zmieniało, ale w pustych wierszach zamiast zostawić puste wpisywało 0.
    Dla specjalistów to pewnie pestka, ale poddałem się.
    Z góry dzięki za pomoc.
  • Poziom 33  
    Witam

    Jeżeli chcesz żeby były kopiowane tylko wartości to zmień linijkę z
    Kod: vbscript
    Zaloguj się, aby zobaczyć kod


    na

    Kod: vbscript
    Zaloguj się, aby zobaczyć kod
  • Poziom 9  
    Witam,
    Wstawiłem tak jak napisałeś i niestety pojawia mi się komunikat:
    can't execute code in break mode
    i nie idzie makro
  • Poziom 9  
    Dalej jest błąd. Jak kompiluje, pokazuje: ""Expected: End of statement""
    Jak próbowałem uruchomić: "SYTAX ERROR"