Spuštění makra na čas

Velmi běžný případ v praxi: potřebujete spouštět jedno nebo více svých maker v daném čase nebo s určitou frekvencí. Máte například velkou a obsáhlou zprávu, která se aktualizuje půl hodiny, a chtěli byste aktualizaci spustit půl hodiny před ranním příchodem do práce. Nebo máte makro, které by mělo automaticky rozesílat e-maily zaměstnancům ve stanovené frekvenci. Nebo při práci s kontingenční tabulkou chcete, aby se aktualizovala za běhu každých 10 sekund a tak dále.

Podívejme se na to, jakou má Excel a Windows schopnost toto implementovat.

Spuštění makra na dané frekvenci

Nejjednodušší způsob, jak toho dosáhnout, je použít vestavěnou metodu VBA Application.OnTimeAn, které spustí zadané makro v zadaný čas. Pojďme to pochopit na praktickém příkladu.

Otevřete editor jazyka Visual Basic pomocí stejnojmenného tlačítka na kartě vývojka (Vývojář) nebo klávesovou zkratkou Další+F11, vložte nový modul přes nabídku Vložit – Modul a zkopírujte tam následující kód:

Dim TimeToRun 'globální proměnná, kde je uložena doba příštího spuštění 'toto je hlavní makro Sub MyMacro() Application.Calculate 'přepočítat knihu Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill buňku A1 s náhodnou barvou :) Volejte NextRun 'spusťte makro NextRun pro nastavení času dalšího běhu End Sub 'toto makro nastavuje čas pro další spuštění hlavního makra Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'přidat 3 sekundy k aktuálnímu času Application.OnTime TimeToRun, "MyMacro" 'naplánovat další spuštění End Sub 'makro pro spuštění sekvence opakování Sub Start() Volání NextRun End Sub 'makro pro zastavení sekvence opakování Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Pojďme zjistit, co je tady.

Nejprve potřebujeme proměnnou, která bude uchovávat čas dalšího spuštění našeho makra – nazval jsem ji TimeToRun. Upozorňujeme, že obsah této proměnné musí být dostupný pro všechna naše následující makra, takže jej musíme vytvořit globální, tedy deklarovat na samém začátku modulu před prvním V.

Následuje naše hlavní makro Moje makro, která bude plnit hlavní úkol – přepočítat knihu metodou Aplikace.Vypočítejte. Aby to bylo jasnější, přidal jsem do listu v buňce A1 vzorec =TDATE(), který zobrazuje datum a čas – při přepočtu se nám jeho obsah aktualizuje přímo před očima (stačí zapnout zobrazení sekund v buňce formát). Pro extra zábavu jsem do makra přidal i příkaz vyplnit buňku A1 náhodně vybranou barvou (kód barvy je celé číslo v rozsahu 0..56, které generuje funkce RND a zaokrouhlí nahoru na celočíselnou funkci Int).

Makro NextRun přidá k předchozí hodnotě TimeToRun 3 sekundy a poté naplánuje další spuštění hlavního makra Moje makro pro tuto novou dobu. V praxi můžete samozřejmě použít jakékoli jiné časové intervaly, které potřebujete, nastavením argumentů funkce TimeValue ve formátu hh:mm:ss.

A nakonec, jen pro pohodlí, bylo přidáno více sekvenčních maker pro spouštění. Domů a jeho dokončení úprava. Poslední používá čtvrtý argument metody k přerušení sekvence. Včas stejný Falešný.

Celkem, pokud makro spustíte Domů, pak se celý tento kolotoč roztočí a na listu uvidíme následující obrázek:

Sekvenci můžete zastavit spuštěním, respektive makra úprava. Pro usnadnění můžete oběma makrům pomocí příkazu přiřadit klávesové zkratky Makra – Možnosti Karta vývojka (Vývojář — Makra — Možnosti).

Spuštění makra podle plánu

Vše výše popsané je samozřejmě možné pouze v případě, že máte spuštěný Microsoft Excel a je v něm otevřen náš soubor. Nyní se podívejme na složitější případ: potřebujete spustit Excel podle daného rozvrhu, například každý den v 5:00, otevřít v něm velkou a komplexní sestavu a aktualizovat v ní všechna spojení a dotazy tak, aby buďte připraveni, než dorazíme do práce 🙂

V takové situaci je lepší použít Plánovač systému Windows – program speciálně zabudovaný do jakékoli verze Windows, který může provádět určité akce podle plánu. Ve skutečnosti jej již používáte, aniž byste o tom věděli, protože váš počítač pravidelně kontroluje aktualizace, stahuje nové antivirové databáze, synchronizuje cloudové složky atd. To vše je práce Plánovače. Naším úkolem je tedy přidat ke stávajícím úkolům další, která spustí Excel a otevře v něm zadaný soubor. A na akci pověsíme naše makro Sešit_Otevřít tento soubor – a problém je vyřešen.

Hned bych vás chtěl upozornit, že práce s Plánovačem může vyžadovat pokročilá uživatelská práva, takže pokud nemůžete na svém pracovním počítači v kanceláři najít níže popsané příkazy a funkce, požádejte o pomoc své IT specialisty.

Spuštění Plánovače

Spustíme tedy Plánovač. Chcete-li to provést, můžete:

• Klikněte pravým tlačítkem na tlačítko Home A zvolte Správa počítače (Počítačový management)
• Vyberte v Ovládacích panelech: Administrace – Plánovač úloh (Ovládací panely — Nástroje pro správu — Plánovač úloh)
• Vyberte z hlavní nabídky Start – Příslušenství – Systémové nástroje – Plánovač úloh
• Stiskněte klávesovou zkratku Vyhrát+R, zadejte taskschd.msc a stiskněte vstoupit

Na obrazovce by se mělo objevit následující okno (mám anglickou verzi, ale můžete mít i verzi):

Vytvořte úkol

Chcete-li vytvořit nový úkol pomocí jednoduchého průvodce krok za krokem, klikněte na odkaz Vytvořte jednoduchý úkol (Vytvořit základní úkol) v pravém panelu.

V prvním kroku průvodce zadejte název a popis úlohy, kterou chcete vytvořit:

Klikněte na tlačítko další (Další) a v dalším kroku vybereme trigger – frekvenci spouštění nebo událost, která spustí náš úkol (například zapnutí počítače):

Pokud jste zvolili Denní (Denně), pak v dalším kroku budete muset vybrat konkrétní čas, datum začátku sekvence a krok (každý 2. den, 5. den atd.):

Dalším krokem je výběr akce – Spusťte program (Spustit program):

A nakonec nejzajímavější je, co přesně je třeba otevřít:

v Program nebo skript (Program/skript) je potřeba zadat cestu k programu Microsoft Excel jako program, tedy přímo ke spustitelnému souboru Excelu. Na různých počítačích s různými verzemi systému Windows a Office může být tento soubor v různých složkách, takže zde je několik způsobů, jak zjistit jeho umístění:

• Kliknutím pravým tlačítkem na ikonu (zástupce) spusťte Excel na ploše nebo na hlavním panelu a vyberte příkaz Materiály (Vlastnosti)a poté v okně, které se otevře, zkopírujte cestu z řádku Cíl:

                       

• Otevřete libovolný sešit aplikace Excel a poté otevřete Správce úloh (Správce úloh) Tlačí Ctrl+Další+od a kliknutím pravým tlačítkem na řádek Microsoft Excel, vyberte příkaz Materiály (Vlastnosti). V okně, které se otevře, můžete zkopírovat cestu, nezapomeňte k němu přidat zpětné lomítko a na konec EXCEL.EXE:

               

• Otevřete Excel, otevřete editor jazyka Visual Basic pomocí klávesové zkratky Další+F11, otevřete panel bezprostřední kombinace Ctrl+G, zadejte do něj příkaz:

  ? Aplikace. Cesta

  …a klikněte na vstoupit

  

  Zkopírujte výslednou cestu, nezapomeňte k němu přidat zpětné lomítko a na konec EXCEL.EXE.

v Přidat argumenty (volitelné) (Přidat argumenty (volitelné)) musíte vložit úplnou cestu ke knize s makrem, které chceme otevřít.

Když je vše zadáno, klikněte další a pak úprava (Dokončit). Úkol by měl být přidán do obecného seznamu:

Vytvořenou úlohu je vhodné spravovat pomocí tlačítek vpravo. Zde můžete úlohu otestovat okamžitým spuštěním (běh)bez čekání na určenou dobu. Úkol můžete dočasně deaktivovat (Zakázat)takže na určitou dobu přestane běžet, jako je vaše dovolená. No, vždy můžete změnit parametry (data, čas, název souboru) pomocí tlačítka Materiály (Vlastnosti).

Chcete-li otevřít soubor, přidejte makro

Nyní zbývá zavěsit do naší knihy spuštění makra, které potřebujeme na událost otevření souboru. Chcete-li to provést, otevřete knihu a pomocí klávesové zkratky přejděte do editoru jazyka Visual Basic Další+F11 nebo tlačítka Visual Basic Karta vývojka (Vývojář). V okně, které se otevře v levém horním rohu, musíte najít náš soubor ve stromu a dvojitým kliknutím otevřete modul Tato kniha (Tento sešit).

Pokud toto okno nevidíte v editoru jazyka Visual Basic, můžete jej otevřít pomocí nabídky Zobrazit — Project Explorer.

V okně modulu, které se otevře, přidejte obsluhu události otevření knihy tak, že ji vyberete z rozevíracích seznamů v horní části Cvičebnice и Otevřená, respektive:

Na obrazovce by se měla objevit šablona postupu. Sešit_Otevřít, kde mezi řádky Private Sub и End Sub a musíte vložit ty příkazy VBA, které by se měly automaticky spustit při otevření tohoto excelového sešitu, když ho plánovač otevře podle plánu. Zde je několik užitečných možností pro přetaktování:

• ThisWorkbook.RefreshAll – Obnoví všechny externí datové dotazy, dotazy Power Query a kontingenční tabulky. Nejuniverzálnější možnost. Jen nezapomeňte ve výchozím nastavení povolit připojení k externím datům a aktualizovat odkazy přes Soubor – Možnosti – Centrum důvěryhodnosti – Možnosti centra důvěryhodnosti – Externí obsah, v opačném případě se při otevření knihy objeví standardní varování a Excel, aniž by cokoliv aktualizoval, bude čekat na vaše požehnání v podobě kliknutí na tlačítko Povolit obsah (Povolit obsah):

  

• ActiveWorkbook.Connections(“Název_připojení”).Obnovit — aktualizace dat připojení Connection_Name.
• Listy („List5“).PivotTables(”PivotTable1«).PivotCache.Obnovit – aktualizace jedné kontingenční tabulky s názvem Kontingenční tabulka 1 na listu Sheet5.
• Aplikace.Vypočítejte – přepočet všech otevřených excelových sešitů.
• Application.CalculateFullRebuild – vynucené přepočítání všech vzorců a přebudování všech závislostí mezi buňkami ve všech otevřených sešitech (ekvivalentní opětovnému zadání všech vzorců).
• Pracovní listy („Zpráva“). Vytisknout – tiskový list Fotky.
• Zavolejte MyMacro – spusťte makro s názvem Moje makro.
• ThisWorkbook.Save – uložit aktuální knihu
  
• ThisWorkbooks.SaveAs „D:ArchiveReport“ & Replace(Now, „:“, „-“) & „.xlsx“ – uložit knihu do složky D: Archivovat pod jménem Fotky s datem a časem připojeným ke jménu.

Pokud chcete, aby se makro spustilo pouze při otevření souboru Plánovačem v 5:00 a ne pokaždé, když uživatel otevře sešit během pracovního dne, pak má smysl přidat kontrolu času, například:

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

To je vše. Nezapomeňte si uložit sešit ve formátu s podporou maker (xlsm nebo xlsb) a můžete bezpečně zavřít Excel a jít domů a nechat počítač zapnutý. Plánovač v danou chvíli (i když je PC zamčený) spustí Excel a otevře v něm zadaný soubor a naše makro provede naprogramované akce. A vy budete luxovat v posteli, zatímco se váš těžký report automaticky přepočítá – krása! 🙂

• Co jsou makra, jak je používat, kam vložit kód Visual Basic v Excelu
• Jak vytvořit vlastní makro doplněk pro Excel
• Jak používat Osobní sešit maker jako knihovnu maker v Excelu