Spustenie makra na čas

Veľmi častý prípad v praxi: potrebujete spustiť jedno alebo viacero svojich makier v danom čase alebo s určitou frekvenciou. Máte napríklad veľkú a náročnú správu, ktorá sa aktualizuje pol hodiny, a chceli by ste spustiť aktualizáciu pol hodiny pred ranným príchodom do práce. Alebo máte makro, ktoré by malo zamestnancom automaticky odosielať e-maily v určenej frekvencii. Alebo pri práci s kontingenčnou tabuľkou chcete, aby sa priebežne aktualizovala každých 10 sekúnd atď.

Pozrime sa na to, čo má Excel a Windows schopnosť implementovať to.

Spustenie makra na danej frekvencii

Najjednoduchší spôsob, ako to urobiť, je použiť vstavanú metódu VBA Application.OnTimeAn, ktoré spustí zadané makro v zadanom čase. Pochopme to na praktickom príklade.

Otvorte editor jazyka Visual Basic tlačidlom s rovnakým názvom na karte vývojka (vývojár) alebo klávesová skratka ostatné+F11, vložte nový modul cez ponuku Vložiť – Modul a skopírujte tam nasledujúci kód:

Dim TimeToRun 'globálna premenná, kde je uložený čas nasledujúceho spustenia 'toto je hlavné makro Sub MyMacro() Application.Calculate 'prepočítať knihu Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill bunka A1 s náhodnou farbou :) Zavolajte NextRun 'spustite makro NextRun na nastavenie času ďalšieho spustenia End Sub 'toto makro nastaví čas pre ďalšie spustenie hlavného makra Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'pridajte 3 sekundy k aktuálnemu času Application.OnTime TimeToRun, "MyMacro" 'naplánujte ďalšie spustenie End Sub 'makro na spustenie sekvencie opakovania Sub Start() Zavolajte makro NextRun End Sub 'makro na zastavenie opakovanej sekvencie Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Poďme zistiť, čo je tu.

Najprv potrebujeme premennú, ktorá bude uchovávať čas ďalšieho spustenia nášho makra – nazval som ju TimeToRun. Upozorňujeme, že obsah tejto premennej musí byť dostupný pre všetky naše nasledujúce makrá, takže ho musíme urobiť celkovo, teda deklarovať na samom začiatku modulu pred prvým nižšie.

Ďalej prichádza naše hlavné makro MyMacro, ktorý bude vykonávať hlavnú úlohu – prepočítať knihu metódou Aplikácia.Vypočítajte. Aby to bolo jasnejšie, do hárku v bunke A1 som pridal vzorec =TDATE(), ktorý zobrazuje dátum a čas – pri prepočte sa nám jeho obsah aktualizuje priamo pred očami (stačí si zapnúť zobrazenie sekúnd v bunke formát). Pre extra zábavu som do makra pridal aj príkaz na vyplnenie bunky A1 náhodne vybranou farbou (kód farby je celé číslo v rozsahu 0..56, ktoré generuje funkcia RND a zaokrúhli nahor na celé číslo Int).

Makro NextRun pridáva k predchádzajúcej hodnote TimeToRun 3 sekundy a potom naplánuje ďalšie spustenie hlavného makra MyMacro pre tento nový čas. Samozrejme, v praxi môžete nastavením argumentov funkcie použiť akékoľvek iné časové intervaly, ktoré potrebujete TimeValue vo formáte hh:mm:ss.

A nakoniec, len pre pohodlie, bolo pridaných viac makier na spúšťanie sekvencií. Domov a jeho dokončenie úprava. Posledný používa štvrtý argument metódy na prerušenie postupnosti. Včas rovný Falošný.

Celkom, ak spustíte makro Domov, potom sa celý tento kolotoč roztočí a na hárku uvidíme nasledujúci obrázok:

Sekvenciu môžete zastaviť spustením, resp. makra úprava. Pre pohodlie môžete obom makrám priradiť klávesové skratky pomocou príkazu Makrá – možnosti pútko vývojka (Vývojár — Makrá — Možnosti).

Spustenie makra podľa plánu

Samozrejme, všetko popísané vyššie je možné len vtedy, ak máte spustený program Microsoft Excel a je v ňom otvorený náš súbor. Teraz sa pozrime na zložitejší prípad: Excel potrebujete spustiť podľa daného harmonogramu, napríklad každý deň o 5:00, otvoriť v ňom veľký a komplexný report a aktualizovať v ňom všetky spojenia a dotazy tak, aby buďte pripravení, kým prídeme do práce 🙂

V takejto situácii je lepšie použiť Plánovač systému Windows – program špeciálne zabudovaný do ľubovoľnej verzie systému Windows, ktorý dokáže vykonávať určené akcie podľa plánu. V skutočnosti ho už používate bez toho, aby ste o tom vedeli, pretože váš počítač pravidelne kontroluje aktualizácie, sťahuje nové antivírusové databázy, synchronizuje cloudové priečinky atď. Všetko je to práca Plánovača. Našou úlohou je teda pridať k existujúcim úlohám ďalšiu, ktorá spustí Excel a otvorí v ňom zadaný súbor. A naše makro zavesíme na akciu Workbook_Open tento súbor – a problém je vyriešený.

Chcem vás hneď upozorniť, že práca s Plánovačom môže vyžadovať rozšírené používateľské práva, takže ak na svojom pracovnom počítači v kancelárii nemôžete nájsť nižšie popísané príkazy a funkcie, požiadajte o pomoc svojich IT špecialistov.

Spustenie Plánovača

Spustíme teda Plánovač. Ak to chcete urobiť, môžete:

• Kliknite pravým tlačidlom myši na tlačidlo štart A vyberte počítač Správa (Počítačový manažment)
• V ovládacom paneli vyberte: Administrácia – Plánovač úloh (Ovládací panel — Nástroje na správu — Plánovač úloh)
• Vyberte z hlavného menu Štart – Príslušenstvo – Systémové nástroje – Plánovač úloh
• Stlačte klávesovú skratku výhra+R, vstúpiť taskschd.msc a stlačte vstúpiť

Na obrazovke by sa malo objaviť nasledujúce okno (mám anglickú verziu, ale môžete mať aj verziu):

Vytvorte úlohu

Ak chcete vytvoriť novú úlohu pomocou jednoduchého sprievodcu krok za krokom, kliknite na odkaz Vytvorte jednoduchú úlohu (Vytvoriť základnú úlohu) v pravom paneli.

V prvom kroku sprievodcu zadajte názov a popis úlohy, ktorá sa má vytvoriť:

Kliknite na tlačidlo ďalšie (Ďalšie) a v ďalšom kroku vyberieme spúšťač – frekvenciu spúšťania alebo udalosť, ktorá spustí našu úlohu (napríklad zapnutie počítača):

Ak ste si vybrali denná (Denne), potom v ďalšom kroku budete musieť vybrať konkrétny čas, dátum začiatku sekvencie a krok (každý 2. deň, 5. deň atď.):

Ďalším krokom je výber akcie – Spustite program (Spustiť program):

A nakoniec, najzaujímavejšie je, čo presne treba otvoriť:

V Program alebo skript (Program/skript) je potrebné zadať cestu k programu Microsoft Excel ako program, teda priamo k spustiteľnému súboru Excelu. Na rôznych počítačoch s rôznymi verziami systému Windows a balíka Office sa tento súbor môže nachádzať v rôznych priečinkoch, takže tu je niekoľko spôsobov, ako zistiť jeho umiestnenie:

• Kliknutím pravým tlačidlom myši na ikonu (skratku) spustite Excel na pracovnej ploche alebo na paneli úloh a vyberte príkaz materiály (Vlastnosti)a potom v okne, ktoré sa otvorí, skopírujte cestu z čiary Cieľ:

                       

• Otvorte ľubovoľný excelový zošit a potom otvorte Správca úloh (Správca úloh) tlačenie ctrl+ostatné+od a kliknutím pravým tlačidlom myši na riadok Microsoft Excel, vyberte príkaz materiály (Vlastnosti). V okne, ktoré sa otvorí, môžete skopírovať cestu, nezabudnite k nemu pridať spätnú lomku a na koniec EXCEL.EXE:

               

• Otvorte Excel, otvorte editor Visual Basic pomocou klávesovej skratky ostatné+F11, otvorený panel Bezprostredná kombinácia ctrl+G, zadajte do neho príkaz:

  ? Aplikácia.Cesta

  …a kliknite na vstúpiť

  

  Skopírujte výslednú cestu, nezabudnite k nemu pridať spätnú lomku a na koniec EXCEL.EXE.

V Pridať argumenty (nepovinné) (Pridať argumenty (voliteľné)) je potrebné vložiť úplnú cestu ku knihe s makrom, ktoré chceme otvoriť.

Keď je všetko zadané, kliknite ďalšie a potom úprava (Skončiť). Úloha by sa mala pridať do všeobecného zoznamu:

Vytvorenú úlohu je vhodné spravovať pomocou tlačidiel vpravo. Tu môžete úlohu otestovať jej okamžitým spustením (beh)bez čakania na určený čas. Úlohu môžete dočasne deaktivovať (Zakázať)aby na určitý čas prestal bežať, napríklad na dovolenke. Parametre (dátum, čas, názov súboru) môžete kedykoľvek zmeniť pomocou tlačidla materiály (Vlastnosti).

Pridajte makro na otvorenie súboru

Teraz zostáva zavesiť do našej knihy spustenie makra, ktoré potrebujeme na udalosť otvorenia súboru. Ak to chcete urobiť, otvorte knihu a pomocou klávesovej skratky prejdite do editora jazyka Visual Basic ostatné+F11 alebo tlačidlá Visual Basic pútko vývojka (vývojár). V okne, ktoré sa otvorí v ľavom hornom rohu, musíte nájsť náš súbor v strome a dvojitým kliknutím otvorte modul Táto kniha (Tento pracovný zošit).

Ak toto okno nevidíte v editore jazyka Visual Basic, môžete ho otvoriť cez ponuku Zobraziť — Project Explorer.

V okne modulu, ktoré sa otvorí, pridajte obsluhu udalosti otvorenia knihy tak, že ju vyberiete z rozbaľovacích zoznamov v hornej časti Cvičebnica и Otvorený, respektíve:

Na obrazovke by sa mala objaviť šablóna postupu. Workbook_Open, kde medzi riadkami Súkromný Sub и End Sub a musíte vložiť tie príkazy VBA, ktoré by sa mali automaticky spustiť pri otvorení tohto excelového zošita, keď ho plánovač otvorí podľa plánu. Tu je niekoľko užitočných možností pretaktovania:

• ThisWorkbook.RefreshAll – Obnoví všetky externé dátové dotazy, dotazy Power Query a kontingenčné tabuľky. Najuniverzálnejšia možnosť. Len nezabudnite predvolene povoliť pripojenia k externým údajom a aktualizovať odkazy cez Súbor – Možnosti – Centrum dôveryhodnosti – Možnosti centra dôveryhodnosti – Externý obsah, v opačnom prípade sa pri otvorení knihy zobrazí štandardné upozornenie a Excel bez toho, aby čokoľvek aktualizoval, bude čakať na vaše požehnanie v podobe kliknutia na tlačidlo Povoliť obsah (Povoliť obsah):

  

• ActiveWorkbook.Connections(“Názov_pripojenia”).Obnoviť — aktualizácia údajov o spojení Connection_Name.
• Listy („Hárok 5").Kontingenčné tabuľky ("Kontingenčná tabuľka1«).PivotCache.Obnoviť – aktualizácia jednej kontingenčnej tabuľky s názvom Kontingenčná tabuľka1 na liste Sheet5.
• Aplikácia.Vypočítajte – prepočet všetkých otvorených excelových zošitov.
• Application.CalculateFullRebuild – nútený prepočet všetkých vzorcov a prebudovanie všetkých závislostí medzi bunkami vo všetkých otvorených zošitoch (ekvivalent opätovného zadávania všetkých vzorcov).
• Pracovné hárky („Správa“). Vytlačiť – tlačový hárok fotografie.
• Zavolajte MyMacro – spustiť makro s názvom MyMacro.
• ThisWorkbook.Save – uložiť aktuálnu knihu
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace(teraz, ":", "-") & ".xlsx" – uložiť knihu do priečinka D: Archív pod menom fotografie s dátumom a časom pripojeným k názvu.

Ak chcete, aby sa makro spustilo iba vtedy, keď plánovač otvorí súbor o 5:00 a nie vždy, keď používateľ otvorí zošit počas pracovného dňa, potom má zmysel pridať kontrolu času, napríklad:

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

To je všetko. Nezabudnite si uložiť zošit vo formáte s podporou makier (xlsm alebo xlsb) a môžete pokojne zavrieť Excel a ísť domov a nechať počítač zapnutý. Plánovač v danom momente (aj keď je PC zamknuté) spustí Excel a otvorí v ňom zadaný súbor a naše makro vykoná naprogramované akcie. A vy budete luxovať v posteli, kým sa váš ťažký report automaticky prepočíta – krása! 🙂

• Čo sú makrá, ako ich používať, kam vložiť kód Visual Basic v Exceli
• Ako vytvoriť svoj vlastný makro doplnok pre Excel
• Ako používať osobný zošit makier ako knižnicu pre vaše makrá v Exceli