Uruchamianie makra na czas

Bardzo częsty przypadek w praktyce: musisz uruchomić jedno lub więcej makr w określonym czasie lub z określoną częstotliwością. Na przykład masz duży i ciężki raport, który aktualizuje się na pół godziny i chcesz uruchomić aktualizację na pół godziny przed przyjściem do pracy rano. Lub masz makro, które powinno automatycznie wysyłać e-maile do pracowników z określoną częstotliwością. Lub podczas pracy z tabelą przestawną chcesz, aby aktualizowała się w locie co 10 sekund i tak dalej.

Przyjrzyjmy się, jakie programy Excel i Windows są w stanie to zaimplementować.

Uruchamianie makra z określoną częstotliwością

Najłatwiej to zrobić, korzystając z wbudowanej metody VBA Aplikacja.Na czasUruchamia określone makro w określonym czasie. Zrozummy to na praktycznym przykładzie.

Otwórz edytor Visual Basic za pomocą przycisku o tej samej nazwie na karcie wywoływacz (Deweloper) lub skrót klawiaturowy inny+F11, wstaw nowy moduł przez menu Wstaw – moduł i skopiuj tam następujący kod:

Dim TimeToRun 'zmienna globalna, w której jest przechowywany następny czas uruchomienia 'to jest główne makro Sub MyMacro() Application.Calculate 'przelicz książkę Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill komórka A1 z losowym kolorem :) Call NextRun 'uruchom makro NextRun, aby ustawić czas następnego uruchomienia End Sub 'to makro ustawia czas następnego uruchomienia makra głównego Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'dodaj 3 sekundy do aktualnego czasu Application.OnTime TimeToRun, "MyMacro" 'zaplanuj następne uruchomienie End Sub 'makro, aby rozpocząć powtarzaną sekwencję. Sub Start() Wywołaj NextRun End Sub 'makro, aby zatrzymać powtarzaną sekwencję Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Dowiedzmy się, co jest tutaj.

Najpierw potrzebujemy zmiennej, która będzie przechowywać czas kolejnego uruchomienia naszego makra – tak to nazwałem Czas biegać. Pamiętaj, że zawartość tej zmiennej musi być dostępna dla wszystkich naszych kolejnych makr, więc musimy to zrobić światowy, czyli zadeklaruj na samym początku modułu przed pierwszym Podłoże.

Dalej jest nasze główne makro MojeMakro, który wykona główne zadanie – przeliczyć książkę metodą Aplikacja.Oblicz. Żeby było jaśniej, dodałem do arkusza w komórce A1 formułę =TDATE(), która wyświetla datę i godzinę – po przeliczeniu jej zawartość zostanie zaktualizowana na naszych oczach (wystarczy włączyć wyświetlanie sekund w komórce format). Dla dodatkowej zabawy dodałem też do makra polecenie wypełnienia komórki A1 losowo wybranym kolorem (kod koloru to liczba całkowita z zakresu 0..56, która jest generowana przez funkcję Rnd i zaokrągla do funkcji liczby całkowitej Int).

Macro Następny bieg dodaje do poprzedniej wartości Czas biegać Jeszcze 3 sekundy, a następnie planuje kolejne uruchomienie makra głównego MojeMakro na ten nowy czas. Oczywiście w praktyce możesz użyć dowolnych innych przedziałów czasowych, ustawiając argumenty funkcji Wartość czasu w formacie gg:mm:ss.

I na koniec, tylko dla wygody, dodano więcej makr uruchamiania sekwencji. Strona główna i jego zakończenie koniec. Ostatni używa czwartego argumentu metody do przerwania sekwencji. O czasie równy Fałszywy.

Łącznie, jeśli uruchomisz makro Strona główna, wtedy cała ta karuzela się zakręci, a na arkuszu zobaczymy następujący obrazek:

Możesz zatrzymać sekwencję uruchamiając odpowiednio makro koniec. Dla wygody możesz przypisać skróty klawiaturowe do obu makr za pomocą polecenia Makra – Opcje Zakładka wywoływacz (Deweloper — Makra — Opcje).

Uruchamianie makra zgodnie z harmonogramem

Oczywiście wszystko opisane powyżej jest możliwe tylko wtedy, gdy masz uruchomiony Microsoft Excel i nasz plik jest w nim otwarty. A teraz spójrzmy na bardziej skomplikowany przypadek: trzeba uruchamiać Excela zgodnie z zadanym harmonogramem, np. codziennie o 5:00, otwierać w nim duży i złożony raport i aktualizować w nim wszystkie połączenia i zapytania tak, aby był bądź gotowy, zanim dotrzemy do pracy 🙂

W takiej sytuacji lepiej skorzystać Harmonogram Windows – program specjalnie wbudowany w dowolną wersję systemu Windows, który może wykonywać określone czynności zgodnie z harmonogramem. W rzeczywistości używasz go już nie wiedząc o tym, ponieważ Twój komputer regularnie sprawdza dostępność aktualizacji, pobiera nowe antywirusowe bazy danych, synchronizuje foldery w chmurze itp. To wszystko jest dziełem Harmonogramu. Naszym zadaniem jest więc dodanie do istniejących zadań kolejnego, które uruchomi Excela i otworzy w nim określony plik. A na imprezie zawiesimy nasze makro Skoroszyt_Otwarty ten plik – i problem został rozwiązany.

Chcę od razu ostrzec, że praca z Harmonogramem może wymagać zaawansowanych uprawnień użytkownika, więc jeśli nie możesz znaleźć opisanych poniżej poleceń i funkcji na komputerze służbowym w biurze, skontaktuj się ze specjalistami IT w celu uzyskania pomocy.

Uruchamianie harmonogramu

Więc zacznijmy Harmonogram. Aby to zrobić, możesz:

• Kliknij prawym przyciskiem myszy przycisk Start i wybierz Zarządzanie komputerem (Zarządzanie komputerem)
• Wybierz w Panelu sterowania: Administracja – Harmonogram zadań (Panel sterowania — Narzędzia administracyjne — Harmonogram zadań)
• Wybierz z menu głównego Start – Akcesoria – Narzędzia systemowe – Harmonogram zadań
• Naciśnij skrót klawiaturowy Win+R, wchodzić Taskschd.msc i naciśnij Wchodzę

Na ekranie powinno pojawić się następujące okno (posiadam wersję angielską, ale można mieć też wersję):

Utwórz zadanie

Aby utworzyć nowe zadanie za pomocą prostego kreatora krok po kroku, kliknij link Utwórz proste zadanie (Utwórz zadanie podstawowe) w prawym panelu.

W pierwszym kroku kreatora wprowadź nazwę i opis zadania, które ma zostać utworzone:

Kliknij na przycisk Następna (Kolejny) a w kolejnym kroku wybieramy wyzwalacz – częstotliwość uruchamiania lub zdarzenie, które uruchomi nasze zadanie (np. włączenie komputera):

Jeśli tak wybrałeś Daily (Codziennie), następnie w kolejnym kroku należy wybrać konkretną godzinę, datę rozpoczęcia sekwencji i kroku (co 2 dzień, 5 dzień itd.):

Następnym krokiem jest wybór akcji – Uruchom program (Rozpocznij program):

I na koniec najciekawsze jest to, co dokładnie należy otworzyć:

W Program lub skrypt (Program/skrypt) należy wprowadzić ścieżkę do programu Microsoft Excel jako program, czyli bezpośrednio do pliku wykonywalnego Excel. Na różnych komputerach z różnymi wersjami systemu Windows i pakietu Office ten plik może znajdować się w różnych folderach, więc oto kilka sposobów na sprawdzenie jego lokalizacji:

• Kliknij prawym przyciskiem myszy ikonę (skrót), aby uruchomić program Excel na pulpicie lub na pasku zadań i wybierz polecenie Materiały (Nieruchomości), a następnie w oknie, które się otworzy, skopiuj ścieżkę z wiersza cel:

                       

• Otwórz dowolny skoroszyt programu Excel, a następnie otwórz Task Manager (Menadżer zadań) popychanie Ctrl+inny+Cena Od i klikając prawym przyciskiem myszy linię Microsoft Excel, wybierz polecenie Materiały (Nieruchomości). W oknie, które się otworzy, możesz skopiować ścieżkę, nie zapominając o dodaniu do niego odwrotnego ukośnika i EXCEL.EXE na końcu:

               

• Otwórz Excel, otwórz edytor Visual Basic za pomocą skrótu klawiaturowego inny+F11, otwórz panel Natychmiastowy kombinacją Ctrl+G, wpisz w nim polecenie:

  ? Ścieżka aplikacji

  … i kliknij Wchodzę

  

  Skopiuj wynikową ścieżkę, nie zapominając o dodaniu do niego odwrotnego ukośnika i EXCEL.EXE na końcu.

W Dodaj argumenty (opcjonalnie) (Dodaj argumenty (opcjonalnie)) musisz wstawić pełną ścieżkę do książki z makrem, które chcemy otworzyć.

Gdy wszystko zostanie wprowadzone, kliknij Następna , a następnie koniec (Skończyć). Zadanie należy dodać do ogólnej listy:

Wygodnie jest zarządzać utworzonym zadaniem za pomocą przycisków po prawej stronie. Tutaj możesz przetestować zadanie, uruchamiając je natychmiast (biegać)bez czekania na określony czas. Możesz tymczasowo dezaktywować zadanie (Wyłączyć)aby przestał działać na jakiś czas, na przykład na wakacje. Cóż, zawsze możesz zmienić parametry (daty, czas, nazwę pliku) za pomocą przycisku Materiały (Nieruchomości).

Dodaj makro, aby otworzyć plik

Teraz pozostaje zawiesić w naszej książce uruchomienie makra, którego potrzebujemy w zdarzeniu otwarcia pliku. Aby to zrobić, otwórz książkę i przejdź do edytora Visual Basic za pomocą skrótu klawiaturowego inny+F11 lub przyciski Visual Basic Zakładka wywoływacz (Deweloper). W oknie, które otwiera się w lewym górnym rogu, musisz znaleźć nasz plik na drzewie i kliknąć dwukrotnie, aby otworzyć moduł Ta książka (Ten zeszyt).

Jeśli nie widzisz tego okna w edytorze Visual Basic, możesz je otworzyć za pomocą menu Widok — Eksplorator projektu.

W oknie modułu, które się otworzy, dodaj procedurę obsługi zdarzenia otwarcia książki, wybierając ją z rozwijanych list u góry skoroszyt и Otwarteodpowiednio:

Na ekranie powinien pojawić się szablon procedury. Skoroszyt_Otwarty, gdzie między wierszami Prywatna Sub и End Sub i musisz wstawić te polecenia VBA, które powinny być automatycznie wykonywane po otwarciu tego skoroszytu programu Excel, gdy Harmonogram otworzy go zgodnie z harmonogramem. Oto kilka przydatnych opcji podkręcania:

• Ten zeszyt. Odśwież wszystko – Odświeża wszystkie zapytania dotyczące danych zewnętrznych, zapytania Power Query i tabele przestawne. Najbardziej wszechstronna opcja. Tylko nie zapomnij domyślnie zezwalać na połączenia z danymi zewnętrznymi i aktualizować łącza przez Plik – Opcje – Centrum zaufania – Opcje centrum zaufania – Treść zewnętrzna, w przeciwnym razie po otwarciu księgi pojawi się standardowe ostrzeżenie i Excel bez aktualizacji czegokolwiek będzie czekał na Twoje błogosławieństwo w postaci kliknięcia przycisku Włącz zawartość (Włącz zawartość):

  

• ActiveWorkbook.Połączenia(„Nazwa_połączenia”).Odśwież — aktualizacja danych na połączeniu Nazwa_Połączenia.
• Arkusze („Arkusz5"). Tabele przestawne("Tabela przestawna1«).PivotCache.Odśwież – aktualizacja pojedynczej tabeli przestawnej o nazwie Tabela przestawna 1 na prześcieradle Sheet5.
• Aplikacja.Oblicz – przeliczenie wszystkich otwartych skoroszytów Excela.
• Aplikacja. Oblicz pełną odbudowę – wymuszone przeliczenie wszystkich formuł i przebudowanie wszystkich zależności między komórkami we wszystkich otwartych skoroszytach (odpowiednik ponownego wprowadzenia wszystkich formuł).
• Arkusze robocze („Raport”). Wydruk – arkusz do druku ZDJĘCIA.
• Zadzwoń do MojeMakro – uruchom makro o nazwie MojeMakro.
• ThisWorkbook.Zapisz – zapisz aktualną książkę
  
• ThisWorkbooks.SaveAs „D:ArchiveReport” i zamień (teraz „:”, „-”) i „.xlsx” – zapisz książkę do folderu D:Archiwum pod imieniem ZDJĘCIA z datą i godziną dołączoną do nazwy.

Jeśli chcesz, aby makro było wykonywane tylko wtedy, gdy plik jest otwierany przez Schedulera o godzinie 5:00, a nie za każdym razem, gdy użytkownik otwiera skoroszyt w ciągu dnia roboczego, to warto dodać kontrolę czasu, na przykład:

If Format(Teraz, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

To wszystko. Nie zapomnij zapisać skoroszytu w formacie z obsługą makr (xlsm lub xlsb) i możesz bezpiecznie zamknąć program Excel i wrócić do domu, pozostawiając komputer włączony. W danym momencie (nawet jeśli komputer jest zablokowany) Scheduler uruchomi Excela i otworzy w nim określony plik, a nasze makro wykona zaprogramowane działania. I będziesz rozkoszować się w łóżku, podczas gdy Twój ciężki raport zostanie automatycznie przeliczony – piękno!

• Czym są makra, jak ich używać, gdzie wstawić kod Visual Basic w Excelu
• Jak stworzyć własny dodatek do makr do Excela
• Jak używać skoroszytu makr osobistych jako biblioteki makr w programie Excel