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 - kopiowanie wzoru z wprowadzeniem zmian - tylko makro?

mario22 10 Jan 2012 10:46 2802 8
  • #1
    mario22
    Level 11  
    Witam,

    Excel 2003.

    Mam taki oto wzór:
    =JEŻELI(CZY.BŁĄD(JEŻELI([1.xls]Arkusz1!$L$12="B50, 3231";"3231";JEŻELI([1.xls]Arkusz1!$L$12="B5, 231";"231";"Błąd, sprawdź wybór """)));"błąd";JEŻELI([1.xls]Arkusz1!$L$12="B50, 3231";"3231";JEŻELI([1.xls]Arkusz1!$L$12="B5, 231";"231";"Błąd, sprawdź wybór""")))

    Jak widać są odwołania do plików zewnętrznych. Problem tkwi w tym że wzór trzeba skopiować do kolejnych komórek niżej ze zmianą nazwy pliku na kolejny nr czyli 2.xls, 3.xls itd. Niestety excel nie ma możliwości zmiany nazwy pliku jak ma to miejsce w przypadku odwołań względnych przy kopiowaniu komórek. Nie mam już pomysłu jak to rozwiązać.
    W kolejnych kolumnach są inne wzory również z odwołaniami do plików zewnętrzych (każdy wiersz odwołuje się do innego pliku).
    Niestety z makrami nie mam doświadczenia.
    Czy można to rozwiązać bez makra? A może jakieś proste makro?

    Pozdrawiam
  • #2
    marcinj12
    Level 40  
    Poniższe makro wstawi tą formułę w 20 komórek poniżej zaznaczonej komórki:
    Code: vb
    Log in, to see the code


    Formuła została uzyskana przez nagranie makra, gdzie wstawiłem Twoją funkcję do Excela, a potem zapisany na sztywno [1.xls] zastąpiłem przez sklejenie tekstu ze zmieniającą się zmienną i: [" & i & ".xls]. To tak na przyszłość, jakbyś chciał to kiedyś zmodyfikować.
  • #3
    adamas_nt
    Moderator of Programming
    Można i bez makra. Jeśli każde odwołanie
    Code:
    [1.xls]Arkusz1!$L$12
    zastąpisz
    Code:
    ADR.POŚR("["&WIERSZ()&".xls]Arkusz1!$L$12")
    Jeśli formuła znajduje się niżej, to należy odjąć liczę wierszy powyżej. Np dla formuły w A3 (3-ci wiersz) 1=3-2
    Code:
    ADR.POŚR("["&WIERSZ()-2&".xls]Arkusz1!$L$12")


    Kopiując w dół, wynik funkcji WIERSZ() będzie rósł, zmieniając argument a tym samym nazwę pliku...
  • #4
    mario22
    Level 11  
    Skorzystałem z pierwszej opcji (makro) i działa, zmodyfikowałem już sobie makro dla wypełnienia 10 kolumn jednocześnie (w każdej inny wzór)
    Drugą opcję sprawdzę w wolnej chwili.
    Mam jednak jeszcze jeden mały problem z arkuszem.
    Ponieważ nie wszystkie pliki do których jest odwołanie jeszcze istnieją, (będą powstawać i mają się same uzupełniać w zestawieniu) każda komórka dla której brak takiego pliku wyświetla okno z aby podać ścieżkę, trzeba wcisnąc anuluj aby przeszedł dalej. Przy kilku kolumnach dla np. 100 wierszy klikanie enterem stanie się uporczywe. Czy można to w jakiś sposób obejść?

    Dzięki
  • #5
    marcinj12
    Level 40  
    Można, w ten sposób:
    Code: vb
    Log in, to see the code
  • #6
    mario22
    Level 11  
    Dzięki, o to chodziło... niestety napotkałem kolejny duży problem.

    Nie wiem czy to wina Excela 2003,ale okazuje się że w tak przygotowanym arkuszu nie są aktualizowane łącza.
    W zasadzie miało to działać tak:
    Wiersze z danymi są zaciągane z zewnętrznych plików, numerowanych od 1 wzwyż, stąd konieczna była zmiana nazw. W przypadku braku pliku excel wyświetla błąd i jest to ukrywane (nie będzie widoczne dla wydruku). Gdy zostanie utwożony kolejny plik, dane z niego powinny się zaczytać automatycznie przy uruchomieniu pliku głównego. I tutaj jest problem bo aktualizacja nic nie daje. Excel nie widzi pliku, pomimo że plik istnieje.

    Można to obejść?
  • #7
    marcinj12
    Level 40  
    Może to dlatego, że w takiej formie jak to podałeś teraz, funkcja aktualizuje dane tylko z otwartego arkusza o takiej nazwie. Żeby aktualizować dane z arkusza umieszczonego w jakiejś konkretnej lokalizacji, powinieneś podać pełną ścieżkę do niego. Spróbuj przerobić kod w podobny sposób jak przykład poniżej (zwróć uwagę na zmianę nazwy arkusza na pełną ścieżkę oraz apostrofy wokół niej). Zmieniłem też aktywną komórkę, zamiast tego podając wprost numer wiersza i kolumny od której ma zacząć wstawianie:
    Code: vb
    Log in, to see the code


    Po otwarciu pliku powinno wyskoczyć okienko z pytaniem o aktualizację łączy, a dane zaktualizują się po wybraniu odpowiedniego przycisku. Chyba że masz to w zabezpieczeniach Excela wyłączone, wtedy ustaw poziom zabezpieczeń na "Niski" lub "Średni".
  • #8
    mario22
    Level 11  
    Pliki główny oraz pozostałe są w tym samym folderze, nie mogę podać ścieżki bo z innego miejsca (inny komputer) się nie odpali, a jest to konieczne.
    Okno z monitem do aktualizacji łącz wyskakuje, ale aktualizacja nie przynosi spodziewanego skutku.
  • #9
    marcinj12
    Level 40  
    Cóż, tak jak pisałem - zapis adresu jako: [1.xls]Arkusz1!$L$12 oznacza komórkę L12 z Arkusza1 otwartego pliku o nazwie 1.xls

    Spróbuj najpierw otworzyć plik źródłowy (jeden lub kilka), potem ten plik z makrem to się okaże, że zaktualizował łącza tylko w otwartych plikach.

    Chcąc się odnieść do nieotwartego pliku, moim zdaniem musisz zawsze podać pełną ścieżkę. Nie mam pomysłu jak to zrobić w formule (o ile w ogóle jest to możliwe), natomiast w VBA możesz użyć ThisWorkbook.Path do pobrania bieżącego katalogu z którego jest uruchomiony arkusz.