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 Makro - wyszukaj pionowo z tablicy w innym pliku

18 Maj 2020 22:31 402 9
  • Poziom 3  
    Witam,

    Potrzebuję makro, które po wpisaniu wartości w kolumnie A, wstawi wartości w tym samym wierszu w kolumnach B, C, D, E w oparciu o bazę w innym pliku, nazwijmy go "głównym". Ma to działać analogicznie do wyszukaj pionowo, ale odwoływać się do tablicy w innym pliku (makro otwiera plik główny, zaciąga dane i zamyka plik główny). Nie ma działać w pętli - chciałbym, żeby uzupełniało wartości tylko dla wiersza, w którym wpisałem dane w kolumnie 1.

    Do tej pory stworzyłem coś takiego - działa ale wolno

    Kod: vbnet
    Zaloguj się, aby zobaczyć kod

    Nie mam też pomysłu jak wymóc okno dialogowe, jeśli wyszukiwanie w bazie nie znajdzie szukanego rekordu.

    Z góry dziękuję za każdą pomoc.

    Pozdrawiam,
    Mariusz
  • Poziom 34  
    Kerondab napisał:
    działa ale wolno

    Musi działać wolno bo za dużo 'aktywacji' jakiś 'activeceli' otwierasz plik "baza" zamykasz jakiś "Lista_srodkow"
  • Poziom 3  
    clubs napisał:
    Kerondab napisał:
    działa ale wolno

    Musi działać wolno bo za dużo 'aktywacji' jakiś 'activeceli' otwierasz plik "baza" zamykasz jakiś "Lista_srodkow"


    Sorki - mój błąd. zamknięcie dotyczy "baza" - modyfikowałem kod na stronę, żeby usunąć długie nazwy i tę pominąłem. Już poprawiłem.

    Pozdrawiam
  • Poziom 3  
    Witam,
    trochę zmieniłem podejście i stworzyłem coś takiego co w dużej mierze odpowiada moim oczekiwaniom ale mam dodatkowe pytanie:
    "Plik Baza" jest dużym plikiem i długo się otwiera. Chciałbym, żeby napisany kod mógł sprawdzić czy plik jest już otwarty i jeżeli tak, żeby pominął krok otwierania pliku - nie udało mi się znaleźć rozwiązania, które mógłbym dopasować do swojego makro.
    Jeśli to by się udało przed zamknięciem pliku "baza" dodam okno z zapytaniem czy zamknąć plik "baza". Jeśli będą planowane dodatkowe zmiany w pliku, w którym pracuję, plik "baza" nie będzie zamykany, co znacznie przyspieszy pracę makra.
    Z góry dziękuję za każdą pomoc.

    Biezacy_plik = plik, na którym pracuję (w którym makro odwołuje się do "baza.xlsx")

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Baza As Variant

    Baza = "directory\baza.xlsx"

    If Target.Column = 2 Then

    Application.ScreenUpdating = False

    Workbooks.Open (Baza)
    Workbooks("Biezacy_plik").Activate


    Cells(ActiveCell.Row - 1, 3).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 11, 0)
    Cells(ActiveCell.Row - 1, 4).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 3, 0)
    Cells(ActiveCell.Row - 1, 5).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 20, 0)
    Cells(ActiveCell.Row - 1, 6).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 7, 0)
    Cells(ActiveCell.Row - 1, 7).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 9, 0)

    Workbooks("baza.xlsx").Close False

    End If

    Application.ScreenUpdating = False

    End Sub
  • Pomocny post
    Poziom 34  
    Kerondab napisał:
    trochę zmieniłem podejście

    Musisz założyć z góry co chcesz osiągnąć a nie co chwile wklejać jakieś makra.
    "Widzę", że chcesz pobrać tylko 5 wartości z kolumn(F,J,L,N,W) można to zrobisz tak j.w czy przez zwykłe kopiowanie.
    Kerondab napisał:
    Chciałbym, żeby napisany kod mógł sprawdzić czy plik jest już otwarty i jeżeli tak, żeby pominął krok otwierania pliku - nie udało mi się znaleźć rozwiązania

    Było już na forum(coś ala)
    Kod: vbscript
    Zaloguj się, aby zobaczyć kod
  • Poziom 3  
    Dzięki!
    Działa w takim zestawieniu:


    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Baza As Variant
    Baza = "directory\baza.xlsx"
    If Target.Column = 2 Then
    Application.ScreenUpdating = False

    otwarty = False
    For Each Workbook In Workbooks
    If Workbook.Name = "baza.xlsx" Then
    otwarty = True
    Exit For
    End If
    Next Workbook

    If Not otwarty Then

    Workbooks.Open (Baza)
    Workbooks("Biezacy_plik").Activate

    Cells(ActiveCell.Row - 1, 3).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 11, 0)
    Cells(ActiveCell.Row - 1, 4).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 3, 0)
    Cells(ActiveCell.Row - 1, 5).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 20, 0)
    Cells(ActiveCell.Row - 1, 6).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 7, 0)
    Cells(ActiveCell.Row - 1, 7).Value = Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row - 1, 2), Workbooks("baza.xlsx").Sheets("2").Range("D:X"), 9, 0)

    response = MsgBox("Pozostawienie otwartego pliku przyspieszy działanie Harmonogramu przy wpisie nowych rekordów", vbYesNo + vbQuestion, "Czy zamknąć plik: baza.xlsx ?")
    If response = vbYes Then
    Workbooks("baza.xlsx").Close False

    End If
    End If
    Application.ScreenUpdating = False
    End Sub


    A mam takie jeszcze jedno pytanie - czy możliwe jest w VBA zrobić coś takiego, żeby w oknie dialogowym mieć przycisk w stylu "nie pytaj ponownie", po którego aktywacji kolejne załączenie makra, do zamknięcia arkusza, by pomijało część kodu, dokładnie tę część:
    response = MsgBox("Pozostawienie otwartego pliku przyspieszy działanie Harmonogramu przy wpisie nowych rekordów", vbYesNo + vbQuestion, "Czy zamknąć plik: baza.xlsx ?")
    If response = vbYes Then
    Workbooks("baza.xlsx").Close False

    Przepraszam za ciągłe zmiany ale jestem kompletnie zielony w VBA i kolejne wizje przychodzą do głowy dopiero jak zobaczę jak działa to co już mam.

    Pozdrawiam i dziękuję.
  • Poziom 34  
    Kerondab napisał:
    Działa w takim zestawieniu

    Masz źle zrobioną instrukcje 'If Not otwarty Then' a co jeżeli plik masz otwarty?
    Kerondab napisał:
    "nie pytaj ponownie", po którego aktywacji kolejne załączenie makra, do zamknięcia arkusza

    masz makro do zamknięcia arkusza?
  • Poziom 3  
    clubs napisał:
    Kerondab napisał:
    Działa w takim zestawieniu

    Masz źle zrobioną instrukcje 'If Not otwarty Then' a co jeżeli plik masz otwarty?
    Kerondab napisał:
    "nie pytaj ponownie", po którego aktywacji kolejne załączenie makra, do zamknięcia arkusza

    masz makro do zamknięcia arkusza?

    odnośnie pierwszego pytania:

    Jeśli plik jest otwarty to makro pomija wykonanie otwarcia arkusza "baza". Jeśli nie jest otwarty to go otwiera i aktywuje plik na którym pracuję:

    If Not otwarty Then
    Workbooks.Open (Baza)
    Workbooks("Biezacy_plik").Activate

    Możliwe, że czegoś nie przewidziałem, ale testowałem i z otwartym plikiem baza i z zamkniętym i wykonuje to co założyłem,czyli jak plik baza nie jest otwarty to go otwiera a jak jest otwarty to wykonuje dalsze instrukcje: kopiowanie potrzebnych wierszy.
    Przyznam jednak, że nie do końca rozumiem sekcję:
    otwarty = False
    For Each Workbook In Workbooks
    If Workbook.Name = "baza.xlsx" Then
    otwarty = True
    Znalazłem ten moduł gdzieś w internecie i próbowałem różnych konfiguracji ale tylko to mi działa. Nie rozumiem, czemu przed for each muszę dać otwarty=false a potem odnosić się do otwarty jako true...
    Z jednej strony chciałbym sobie stworzyć ułatwienie w pracy ale z drugiej chcę się nauczyć podstaw makr i trochę je zrozumieć, więc będę bardzo wdzięczny za wyjaśnienie lub alternatywne rozwiązanie.

    odnośnie drugiego pytania:
    Część kodu zamykająca arkusz powiązana jest z oknem dialogowym:

    response = MsgBox("Pozostawienie otwartego pliku przyspieszy działanie Harmonogramu przy wpisie nowych rekordów", vbYesNo + vbQuestion, "Czy zamknąć plik: baza.xlsx ?")
    If response = vbYes Then
    Workbooks("baza.xlsx").Close False

    Chodzi mi tylko mechanizm pomijania części kodu, tu może być powiązany nawet z przyciskiem tak, wtedy pytanie by brzmiało - czy pozostawić otwartą bazę do dalszych modyfikacji.
    Chodzi mi tutaj o to, żeby po aktywacji tego kodu cała sekcja od aktywacji okna dialogowego po zamknięcie arkusza była pomijana przy następnym uruchomieniu makra aż do czasu zamknięcia pliku i by ponownie nie otwierało się okno dialogowe.

    Pozdrawiam
  • Pomocny post
    Poziom 34  
    Kerondab napisał:
    Jeśli plik jest otwarty to makro pomija wykonanie otwarcia arkusza "baza".

    Kerondab napisał:
    a jak jest otwarty to wykonuje dalsze instrukcje: kopiowanie potrzebnych wierszy.

    Pomija cała instrukcje czyli nic nie robi.
    Kerondab napisał:
    czemu przed for each muszę dać otwarty=false a potem odnosić się do otwarty jako true

    Generalnie nie musisz dać na początku 'otwarty=false' bo zmienna otwarty typu boolean zawsze z automatu jest 'fałsz' potem pętla 'leci' po skoroszytach i jeżeli napotka nazwę = "baza.xlsx", otwarty przyjmuje wartość jako prawda.
    Kerondab napisał:
    Chodzi mi tylko mechanizm pomijania części kodu, tu może być powiązany nawet z przyciskiem tak, wtedy pytanie by brzmiało - czy pozostawić otwartą bazę do dalszych modyfikacji.

    przenieś to pytanie do sekcji 'beforeclose'
  • Poziom 3  
    Kerondab napisał:
    Chodzi mi tylko mechanizm pomijania części kodu, tu może być powiązany nawet z przyciskiem tak, wtedy pytanie by brzmiało - czy pozostawić otwartą bazę do dalszych modyfikacji.

    przenieś to pytanie do sekcji 'beforeclose'[/quote]

    Dzięki. Niezupełnie o to mi chodziło ale ogólnie spełni to swoją rolę. W praktyce wygląda to tak, że plik "baza" musi być otwarty w czasie pracy w pliku bieżącym bo znacznie to przyspiesza pracę, więc spokojnie to pytanie może być przeniesione na beforeclose.

    Dzięki za pomoc