Kører en makro til tiden

Et meget almindeligt tilfælde i praksis: du skal køre en eller flere af dine makroer på et givet tidspunkt eller med en bestemt frekvens. Du har for eksempel en stor og tung rapport, der opdaterer en halv time, og du vil gerne køre opdateringen en halv time, før du kommer på arbejde om morgenen. Eller du har en makro, der automatisk skal sende e-mails til medarbejderne med en bestemt frekvens. Eller, når du arbejder med en pivottabel, vil du have, at den skal opdateres med det samme hvert 10. sekund, og så videre.

Lad os se på, hvad Excel og Windows har evnen til at implementere dette.

Kørsel af en makro ved en given frekvens

Den nemmeste måde at gøre dette på er at bruge den indbyggede VBA-metode Application.OnTimeEn der kører den angivne makro på det angivne tidspunkt. Lad os forstå dette med et praktisk eksempel.

Åbn Visual Basic-editoren med knappen af ​​samme navn på fanen developer (Udvikler) eller tastaturgenvej andre+F11, indsæt et nyt modul gennem menuen Indsæt – Modul og kopier følgende kode dertil:

Dim TimeToRun 'global variabel, hvor den næste køretid er gemt 'dette er hovedmakroen Sub MyMacro() Application.Calculate 'genberegn bogen Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill celle A1 med en tilfældig farve :) Kald NextRun 'kør NextRun-makroen for at indstille næste kørselstid End Sub 'denne makro indstiller tiden for næste kørsel af hovedmakroen Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'tilføj 3 sekunder til den aktuelle tid Application.OnTime TimeToRun, "MyMacro" 'planlæg næste kørsel End Sub 'makro for at starte gentagelsessekvensen Sub Start() Kald NextRun End Sub 'makro for at stoppe gentagelsessekvensen Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Lad os finde ud af, hvad der er hvad her.

For det første har vi brug for en variabel, der gemmer tidspunktet for næste kørsel af vores makro – jeg kaldte det TimeToRun. Bemærk venligst, at indholdet af denne variabel skal være tilgængeligt for alle vores efterfølgende makroer, så vi skal gøre det global, dvs. erklære helt i begyndelsen af ​​modulet før det første Under.

Dernæst kommer vores hovedmakro MyMacro, som vil udføre hovedopgaven - at genberegne bogen ved hjælp af metoden Anvendelse.Beregn. For at gøre det klarere tilføjede jeg formlen =TDATE() til arket i celle A1, som viser datoen og klokkeslættet – når det genberegnes, vil indholdet blive opdateret lige foran vores øjne (bare slå visningen af ​​sekunder til i cellen format). For ekstra sjov tilføjede jeg også til makroen kommandoen om at udfylde celle A1 med en tilfældigt valgt farve (farvekoden er et heltal i området 0..56, som genereres af funktionen Rnd og runder op til en heltalsfunktion Int).

Makro NextRun tilføjer til den tidligere værdi TimeToRun 3 sekunder mere og planlægger derefter den næste kørsel af hovedmakroen MyMacro til denne nye tid. Selvfølgelig kan du i praksis bruge alle andre tidsintervaller, du har brug for, ved at indstille funktionsargumenterne Tidsværdi i formatet tt:mm:ss.

Og til sidst, bare for nemheds skyld, er der tilføjet flere sekvensstartmakroer. Home og dens færdiggørelse Finish. Den sidste bruger det fjerde metodeargument til at bryde sekvensen. Til tiden lige False.

I alt, hvis du kører makroen Home, så vil hele denne karrusel dreje, og vi vil se følgende billede på arket:

Du kan stoppe sekvensen ved at køre henholdsvis makroen Finish. For nemheds skyld kan du tildele tastaturgenveje til begge makroer ved hjælp af kommandoen Makroer – Indstillinger fanen developer (Udvikler — Makroer — Indstillinger).

Kørsel af en makro efter en tidsplan

Selvfølgelig er alt beskrevet ovenfor kun muligt, hvis du har Microsoft Excel kørende, og vores fil er åben i den. Lad os nu se på en mere kompliceret sag: du skal køre Excel i henhold til en given tidsplan, for eksempel hver dag kl. 5:00, åbne en stor og kompleks rapport i den og opdatere alle forbindelser og forespørgsler i den, så den vil være klar, når vi kommer på arbejde 🙂

I en sådan situation er det bedre at bruge Windows Scheduler – et program, der er specielt indbygget i enhver version af Windows, der kan udføre specificerede handlinger efter en tidsplan. Faktisk bruger du det allerede uden at vide det, fordi din pc jævnligt tjekker for opdateringer, downloader nye antivirusdatabaser, synkroniserer cloud-mapper osv. Det hele er Schedulerens arbejde. Så vores opgave er at tilføje en anden til de eksisterende opgaver, der vil starte Excel og åbne den angivne fil i den. Og vi vil hænge vores makro på begivenheden Arbejdsbog_Åben denne fil – og problemet er løst.

Jeg vil med det samme advare dig om, at arbejdet med Scheduler kan kræve avancerede brugerrettigheder, så hvis du ikke kan finde de kommandoer og funktioner, der er beskrevet nedenfor på din arbejdscomputer på kontoret, skal du kontakte dine it-specialister for at få hjælp.

Start af Scheduler

Så lad os starte skemalæggeren. For at gøre dette kan du enten:

• Højreklik på knappen Starten Og vælg computer Management (Computerstyring)
• Vælg i Kontrolpanel: Administration – Opgaveplanlægger (Kontrolpanel — Administrative værktøjer — Opgaveplanlægning)
• Vælg fra hovedmenuen Start – Tilbehør – Systemværktøjer – Opgaveplanlægning
• Tryk på tastaturgenvej Vinde+R, gå ind taskschd.msc og tryk Indtast

Følgende vindue skulle vises på skærmen (jeg har en engelsk version, men du kan også have en version):

Opret en opgave

For at oprette en ny opgave ved hjælp af en simpel trin-for-trin-guide skal du klikke på linket Lav en simpel opgave (Opret grundlæggende opgave) i højre panel.

I det første trin i guiden skal du indtaste navnet og beskrivelsen af ​​den opgave, der skal oprettes:

Klik på knappen Næste (Næste) og i det næste trin vælger vi en trigger – lanceringsfrekvensen eller en hændelse, der vil starte vores opgave (for eksempel at tænde for computeren):

Hvis du vælger Daglige (Daglige), så skal du i det næste trin vælge et bestemt tidspunkt, startdato for sekvensen og trin (hver 2. dag, 5. dag osv.):

Det næste trin er at vælge en handling – Kør programmet (Start et program):

Og endelig er det mest interessante, hvad der præcist skal åbnes:

I Program eller script (Program/manuskript) du skal indtaste stien til Microsoft Excel som et program, altså direkte til den eksekverbare Excel. På forskellige computere med forskellige versioner af Windows og Office kan denne fil være i forskellige mapper, så her er et par måder, hvorpå du kan finde ud af dens placering:

• Højreklik på ikonet (genvejen) for at starte Excel på skrivebordet eller på proceslinjen, og vælg kommandoen Materialer (Ejendomme), og kopier derefter stien fra linjen i det vindue, der åbnes mål:

                       

• Åbn en hvilken som helst Excel-projektmappe, og åbn derefter Task Manager (Jobliste) skubbe Ctrl+andre+Fra og ved at højreklikke på linjen Microsoft Excel, vælg en kommando Materialer (Ejendomme). I det vindue, der åbnes, kan du kopiere stien, ikke at glemme at tilføje en omvendt skråstreg til det og EXCEL.EXE til sidst:

               

• Åbn Excel, åbn Visual Basic-editor med tastaturgenvej andre+F11, åbent panel umiddelbar en kombination af Ctrl+G, indtast kommandoen i den:

  ? Application.Path

  … og klik videre Indtast

  

  Kopier den resulterende sti, ikke at glemme at tilføje en omvendt skråstreg til det og EXCEL.EXE til sidst.

I Tilføj argumenter (valgfrit) (Tilføj argumenter (valgfrit)) du skal indsætte den fulde sti til bogen med den makro, som vi vil åbne.

Når alt er indtastet, så klik Næste og så Finish (Afslut). Opgaven skal føjes til den generelle liste:

Det er praktisk at styre den oprettede opgave ved hjælp af knapperne til højre. Her kan du teste opgaven ved at køre den med det samme (løb)uden at vente på det angivne tidspunkt. Du kan midlertidigt deaktivere en opgave (Deaktiver)så den holder op med at køre i en periode, f.eks. din ferie. Nå, du kan altid ændre parametrene (datoer, tid, filnavn) via knappen Materialer (Ejendomme).

Tilføj en makro for at åbne en fil

Nu er det tilbage at hænge i vores bog lanceringen af ​​den makro, vi har brug for på file open-begivenheden. For at gøre dette skal du åbne bogen og gå til Visual Basic-editoren ved hjælp af tastaturgenvejen andre+F11 eller knapper Visual Basic fanen developer (Udvikler). I vinduet, der åbner i øverste venstre hjørne, skal du finde vores fil på træet og dobbeltklikke for at åbne modulet Denne bog (Denne arbejdsbog).

Hvis du ikke kan se dette vindue i Visual Basic-editoren, kan du åbne det via menuen Vis — Project Explorer.

I modulvinduet, der åbnes, tilføjer du en bog åben begivenhedshåndtering ved at vælge den fra rullelisterne øverst Workbook и Åbne, henholdsvis:

En procedureskabelon skal vises på skærmen. Arbejdsbog_Åben, hvor mellem linjerne Privat Sub и End Sub og du skal indsætte de VBA-kommandoer, der skal udføres automatisk, når denne Excel-projektmappe åbnes, når Scheduler åbner den i henhold til tidsplanen. Her er nogle nyttige muligheder for overclocking:

• ThisWorkbook.RefreshAll – Opdaterer alle eksterne dataforespørgsler, Power Query-forespørgsler og pivottabeller. Den mest alsidige mulighed. Bare glem ikke at tillade forbindelser til eksterne data som standard og opdatere links via Fil – Indstillinger – Tillidscenter – Tillidscenter-indstillinger – Eksternt indhold, ellers, når du åbner bogen, vises en standardadvarsel og Excel, uden at opdatere noget, vil vente på din velsignelse i form af at klikke på knappen Aktivér indhold (Aktiver indhold):

  

• ActiveWorkbook.Connections(“Connection_Name”). Opdater — opdatering af data på forbindelsen Connection_Name.
• Sheets(“Sheet5").Pivottabeller("Pivottabel1«).PivotCache.Opdater – opdatering af en enkelt pivottabel med navn Pivottabel1 på arket Sheet5.
• Anvendelse.Beregn – genberegning af alle åbne Excel-projektmapper.
• Application.CalculateFullRebuild – tvungen genberegning af alle formler og genopbygning af alle afhængigheder mellem celler i alle åbne projektmapper (svarende til genindtastning af alle formler).
• Arbejdsark ("Rapport"). Udskriv – udskriv ark pics.
• Ring til MyMacro – kør en makro med navnet MyMacro.
• Denne arbejdsbog. Gem – gem den aktuelle bog
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – gem bogen i en mappe D: Arkiv under navnet pics med dato og klokkeslæt tilføjet navnet.

Hvis du ønsker, at makroen kun skal udføres, når filen åbnes af planlæggeren kl. 5:00, og ikke hver gang brugeren åbner projektmappen i løbet af arbejdsdagen, er det fornuftigt at tilføje en tidskontrol, for eksempel:

Hvis Format(Nu, "tt:mm") = "05:00" Så ThisWorkbook.RefreshAll  

Det er alt. Glem ikke at gemme din projektmappe i et makroaktiveret format (xlsm eller xlsb), og du kan trygt lukke Excel og gå hjem og lade din computer være tændt. På et givet tidspunkt (selvom pc'en er låst), vil Scheduler starte Excel og åbne den specificerede fil i den, og vores makro udfører de programmerede handlinger. Og du vil nyde i sengen, mens din tunge rapport automatisk genberegnes – skønhed! 🙂

• Hvad er makroer, hvordan man bruger dem, hvor skal man indsætte Visual Basic-kode i Excel
• Sådan opretter du dit eget makro-tilføjelsesprogram til Excel
• Sådan bruger du Personal Macro Workbook som et bibliotek til dine makroer i Excel