Op tijd een macro uitvoeren

Een veel voorkomend geval in de praktijk: je moet een of meer van je macro's op een bepaald moment of met een bepaalde frequentie draaien. Je hebt bijvoorbeeld een groot en zwaar rapport dat een half uur wordt bijgewerkt en je wilt de update graag een half uur voordat je 's ochtends op je werk aankomt, draaien. Of je hebt een macro die automatisch met een bepaalde frequentie e-mails naar werknemers moet sturen. Of, wanneer u met een draaitabel werkt, wilt u dat deze elke 10 seconden on-the-fly wordt bijgewerkt, enzovoort.

Laten we eens kijken wat Excel en Windows hebben om dit te implementeren.

Een macro uitvoeren op een bepaalde frequentie

De eenvoudigste manier om dit te doen, is door de ingebouwde VBA-methode te gebruiken Toepassing.OnTimeEen die de opgegeven macro op het opgegeven tijdstip uitvoert. Laten we dit begrijpen met een praktisch voorbeeld.

Open de Visual Basic-editor met de knop met dezelfde naam op het tabblad ontwikkelaar (Ontwikkelaar) of sneltoets anders+F11, voeg een nieuwe module in via het menu Invoegen – Module en kopieer daar de volgende code:

Dim TimeToRun 'globale variabele waar de volgende runtime wordt opgeslagen' dit is de hoofdmacro Sub MyMacro() Application.Calculate 'herbereken het boek Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cel A1 met een willekeurige kleur :) Roep NextRun aan 'voer de NextRun-macro uit om de volgende runtime in te stellen End Sub 'deze macro stelt de tijd in voor de volgende run van de hoofdmacro Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'voeg 3 seconden toe aan de huidige tijd Application.OnTime TimeToRun, "MyMacro" 'schema de volgende run End Sub 'macro om de herhalingsreeks te starten Sub Start() Roep NextRun End Sub 'macro om de herhalingsreeks te stoppen Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Laten we uitzoeken wat hier wat is.

Ten eerste hebben we een variabele nodig die de tijd van de volgende uitvoering van onze macro opslaat - ik noemde het Tijd om te rennen. Houd er rekening mee dat de inhoud van deze variabele beschikbaar moet zijn voor al onze volgende macro's, dus we moeten het maken globaal, dwz declareren helemaal aan het begin van de module voor de eerste Sub.

Vervolgens komt onze belangrijkste macro Mijn Macro, die de hoofdtaak zal uitvoeren - om het boek opnieuw te berekenen met behulp van de methode Toepassing.Berekenen. Om het duidelijker te maken, heb ik de formule =TDATE() toegevoegd aan het blad in cel A1, die de datum en tijd weergeeft - wanneer herberekend, wordt de inhoud ervan vlak voor onze ogen bijgewerkt (schakel gewoon de weergave van seconden in de cel in formaat). Voor extra plezier heb ik aan de macro ook de opdracht toegevoegd om cel A1 te vullen met een willekeurig geselecteerde kleur (de kleurcode is een geheel getal in het bereik 0..56, dat wordt gegenereerd door de functie rnd en rondt naar boven af ​​op een geheel getal Int).

Macro VolgendeRun voegt toe aan de vorige waarde Tijd om te rennen Nog 3 seconden en plant dan de volgende run van de hoofdmacro Mijn Macro voor deze nieuwe tijd. In de praktijk kunt u natuurlijk alle andere tijdsintervallen gebruiken die u nodig hebt door de functieargumenten in te stellen Tijdswaarde in het formaat uu:mm:ss.

En tot slot zijn er voor het gemak meer macro's voor het starten van sequenties toegevoegd. Home en de voltooiing ervan Finish. De laatste gebruikt het vierde methodeargument om de reeks te doorbreken. Op tijd gelijk Niet waar.

Totaal als u de macro uitvoert Home, dan draait deze hele carrousel en zien we de volgende afbeelding op het blad:

U kunt de reeks stoppen door respectievelijk de macro uit te voeren Finish. Voor het gemak kunt u sneltoetsen toewijzen aan beide macro's met behulp van de opdracht Macro's – Opties tab ontwikkelaar (Ontwikkelaar — Macro's — Opties).

Een macro volgens een schema uitvoeren

Alles hierboven beschreven is natuurlijk alleen mogelijk als u Microsoft Excel draait en ons bestand daarin geopend is. Laten we nu naar een ingewikkelder geval kijken: u moet Excel volgens een bepaald schema uitvoeren, bijvoorbeeld elke dag om 5:00 uur, er een groot en complex rapport in openen en alle verbindingen en query's erin bijwerken zodat het klaar zijn tegen de tijd dat we op het werk aankomen

In een dergelijke situatie is het beter om Windows-planner – een speciaal in elke versie van Windows ingebouwd programma dat bepaalde acties volgens een schema kan uitvoeren. In feite gebruikt u het al zonder het te weten, omdat uw pc regelmatig controleert op updates, nieuwe antivirusdatabases downloadt, cloudmappen synchroniseert, enz. Het is allemaal het werk van de Scheduler. Het is dus onze taak om aan de bestaande taken nog een taak toe te voegen die Excel start en het opgegeven bestand daarin opent. En we zullen onze macro op het evenement hangen Werkmap_Open dit bestand - en het probleem is opgelost.

Ik wil je meteen waarschuwen dat voor het werken met de Scheduler geavanceerde gebruikersrechten nodig kunnen zijn, dus als je de onderstaande commando's en functies niet kunt vinden op je werkcomputer op kantoor, neem dan contact op met je IT-specialisten voor hulp.

De planner starten

Dus laten we de Scheduler starten. Om dit te doen, kunt u ofwel:

• Klik met de rechtermuisknop op de knop Start En kies Computerbeheer (Computer management)
• Selecteer in het Configuratiescherm: Beheer – Taakplanner (Configuratiescherm — Systeembeheer — Taakplanner)
• Kies uit het hoofdmenu Start – Accessoires – Systeemwerkset – Taakplanner
• Druk op de sneltoets overwinning+R, ga naar binnen taskschd.msc en druk op Enter

Het volgende venster zou op het scherm moeten verschijnen (ik heb een Engelse versie, maar je kunt ook een versie hebben):

Maak een taak aan

Om een ​​nieuwe taak aan te maken met behulp van een eenvoudige stapsgewijze wizard, klikt u op de link Maak een eenvoudige taak (Maak basistaak aan) in het rechter paneel.

Voer bij de eerste stap van de wizard de naam en beschrijving in van de aan te maken taak:

Klik op de knop Volgende (Next) en in de volgende stap selecteren we een trigger - de startfrequentie of een gebeurtenis die onze taak start (bijvoorbeeld de computer aanzetten):

Als je ervoor kiest Dagelijkse (Daily), dan moet u in de volgende stap een specifieke tijd, startdatum van de reeks en stap selecteren (elke 2e dag, 5e dag, enz.):

De volgende stap is het kiezen van een actie – Start het programma (Start een programma):

En tot slot, het meest interessante is wat er precies moet worden geopend:

In het Programma of script (programma/script) u moet het pad naar Microsoft Excel als een programma invoeren, dwz rechtstreeks naar het uitvoerbare Excel-bestand. Op verschillende computers met verschillende versies van Windows en Office kan dit bestand zich in verschillende mappen bevinden, dus hier zijn een paar manieren om de locatie te achterhalen:

• Klik met de rechtermuisknop op het pictogram (snelkoppeling) om Excel op het bureaublad of in de taakbalk te starten en selecteer de opdracht Materialen (Eigendommen)en kopieer in het geopende venster het pad van de regel doelwit:

                       

• Open een Excel-werkmap en open vervolgens Task Manager (Taakbeheer) duwen Ctrl+anders+Van en door met de rechtermuisknop op de regel te klikken Microsoft Excel, kies een commando Materialen (Eigendommen). In het geopende venster kunt u het pad kopiëren, niet vergeten om er een backslash aan toe te voegen en EXCEL.EXE aan het einde:

               

• Open Excel, open Visual Basic-editor met sneltoets anders+F11, open paneel Onmiddellijk een combinatie van Ctrl+G, voer de opdracht erin in:

  ? Applicatiepad

  … en klik op Enter

  

  Kopieer het resulterende pad, niet vergeten om er een backslash aan toe te voegen en EXCEL.EXE aan het einde.

In het Argumenten toevoegen (optioneel) (Argumenten toevoegen (optioneel)) je moet het volledige pad naar het boek invoegen met de macro die we willen openen.

Als alles is ingevoerd, klik dan op Volgende en Finish (Af hebben). De taak moet worden toegevoegd aan de algemene lijst:

Het is handig om de gemaakte taak te beheren met de knoppen aan de rechterkant. Hier kunt u de taak testen door deze onmiddellijk uit te voeren (rennen)zonder te wachten op de aangegeven tijd. Je kunt een taak tijdelijk deactiveren (Uitschakelen)zodat het gedurende een bepaalde tijd niet meer werkt, zoals uw vakantie. Welnu, u kunt de parameters (datums, tijd, bestandsnaam) altijd wijzigen via de knop Materialen (Eigendommen).

Een macro toevoegen om een ​​bestand te openen

Nu blijft het om in ons boek de lancering van de macro te hangen die we nodig hebben op de file open-gebeurtenis. Open hiervoor het boek en ga naar de Visual Basic-editor met behulp van de sneltoets anders+F11 of knoppen Visual Basic tab ontwikkelaar (Ontwikkelaar). In het venster dat wordt geopend in de linkerbovenhoek, moet u ons bestand in de boom vinden en dubbelklikken om de module te openen Dit boek (Dit Werkboek).

Als u dit venster niet ziet in de Visual Basic-editor, kunt u het openen via het menu Bekijken — Projectverkenner.

Voeg in het geopende modulevenster een gebeurtenishandler voor het openen van een boek toe door deze te selecteren in de vervolgkeuzelijsten bovenaan werkboek и Openenrespectievelijk:

Er zou een proceduresjabloon op het scherm moeten verschijnen. Werkmap_Open, waar tussen de regels Privé Sub и End Sub en u moet die VBA-opdrachten invoegen die automatisch moeten worden uitgevoerd wanneer deze Excel-werkmap wordt geopend, wanneer de planner deze volgens het schema opent. Hier zijn enkele handige opties voor overklokken:

• Dit werkboek.Alles vernieuwen – Vernieuwt alle externe gegevensquery's, Power Query-query's en draaitabellen. De meest veelzijdige optie. Vergeet niet om standaard verbindingen met externe gegevens toe te staan ​​en koppelingen bij te werken via Bestand – Opties – Vertrouwenscentrum – Opties vertrouwenscentrum – Externe inhoud, anders verschijnt er bij het openen van het boek een standaardwaarschuwing en wacht Excel, zonder iets bij te werken, op uw zegen in de vorm van klikken op de knop Inhoud aanzetten (Inhoud aanzetten):

  

• ActiveWorkbook.Connections ("Verbindingsnaam"). Vernieuwen — gegevens bijwerken op de Connection_Name-verbinding.
• Lakens ("Blad5"“). Draaitabellen(“Spiltabel1«).PivotCache.Vernieuwen - een enkele draaitabel bijwerken met de naam Draaitabel1 op het blad Sheet5.
• Toepassing.Berekenen – herberekening van alle geopende Excel-werkmappen.
• Toepassing.BerekenenVolledig Opnieuw opbouwen – gedwongen herberekening van alle formules en het opnieuw opbouwen van alle afhankelijkheden tussen cellen in alle geopende werkmappen (gelijk aan het opnieuw invoeren van alle formules).
• Werkbladen ("Rapport").Afdrukken – printblad Foto's.
• Bel Mijn Macro – voer een macro uit met de naam Mijn Macro.
• Dit Werkboek.Opslaan – sla het huidige boek op
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-”) & “.xlsx” - sla het boek op in een map D:Archief onder de naam Foto's met datum en tijd toegevoegd aan de naam.

Als u wilt dat de macro alleen wordt uitgevoerd wanneer het bestand om 5:00 uur wordt geopend door de planner, en niet elke keer dat de gebruiker de werkmap tijdens de werkdag opent, is het zinvol om een ​​tijdcontrole toe te voegen, bijvoorbeeld:

If Format(Now, "uu:mm") = "05:00" Dan ThisWorkbook.RefreshAll  

Dat is alles. Vergeet niet uw werkmap op te slaan in een macro-enabled formaat (xlsm of xlsb) en u kunt Excel veilig afsluiten en naar huis gaan, terwijl u uw computer aan laat. Op een bepaald moment (zelfs als de pc is vergrendeld), start de Scheduler Excel en opent het het opgegeven bestand erin, en onze macro voert de geprogrammeerde acties uit. En u zult heerlijk in bed liggen terwijl uw zware rapport automatisch opnieuw wordt berekend - schoonheid!

• Wat zijn macro's, hoe ze te gebruiken, waar Visual Basic-code in Excel in te voegen?
• Hoe u uw eigen macro-invoegtoepassing voor Excel maakt
• De persoonlijke macrowerkmap gebruiken als bibliotheek voor uw macro's in Excel