Kjøre en makro i tide

Et veldig vanlig tilfelle i praksis: du må kjøre en eller flere av makroene dine på et gitt tidspunkt eller med en viss frekvens. Du har for eksempel en stor og tung rapport som oppdateres en halvtime og du vil gjerne kjøre oppdateringen en halvtime før du kommer på jobb om morgenen. Eller du har en makro som automatisk skal sende ut e-post til ansatte med en spesifisert frekvens. Eller, når du arbeider med en pivottabell, vil du at den skal oppdateres med en gang hvert 10. sekund, og så videre.

La oss se på hva Excel og Windows har evnen til å implementere dette.

Kjøre en makro ved en gitt frekvens

Den enkleste måten å gjøre dette på er å bruke den innebygde VBA-metoden Application.OnTimeEn som kjører den angitte makroen på det angitte tidspunktet. La oss forstå dette med et praktisk eksempel.

Åpne Visual Basic-editoren med knappen med samme navn på fanen utvikler (Utvikler) eller hurtigtast andre+F11, sett inn en ny modul gjennom menyen Sett inn – modul og kopier følgende kode dit:

Dim TimeToRun 'global variabel hvor neste kjøretid er lagret' dette er hovedmakroen Sub MyMacro() Application.Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill celle A1 med en tilfeldig farge :) Call NextRun 'kjør NextRun-makroen for å angi neste kjøretid End Sub 'denne makroen setter tiden for neste kjøring av hovedmakroen Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'legg til 3 sekunder til gjeldende tid Application.OnTime TimeToRun, "MyMacro" 'planlegg neste kjøring End Sub 'makro for å starte repetisjonssekvensen Sub Start() Ring NextRun End Sub 'makro for å stoppe repetisjonssekvensen Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

La oss finne ut hva som er hva her.

Først trenger vi en variabel som lagrer tidspunktet for neste kjøring av makroen vår – jeg kalte den TimeToRun. Vær oppmerksom på at innholdet i denne variabelen må være tilgjengelig for alle våre påfølgende makroer, så vi må gjøre det global, dvs. deklarer helt i begynnelsen av modulen før den første Sub.

Deretter kommer vår hovedmakro Min Makro, som vil utføre hovedoppgaven - å beregne boken på nytt ved hjelp av metoden Søknad.Beregn. For å gjøre det klarere, la jeg til formelen =TDATE() til arket i celle A1, som viser dato og klokkeslett – når den beregnes på nytt, vil innholdet oppdateres rett foran øynene våre (bare slå på visningen av sekunder i cellen format). For ekstra moro la jeg også til makroen kommandoen for å fylle celle A1 med en tilfeldig valgt farge (fargekoden er et heltall i området 0..56, som genereres av funksjonen Rnd og runder opp til en heltallsfunksjon int).

Makro NextRun legger til den forrige verdien TimeToRun 3 sekunder til og planlegger deretter neste kjøring av hovedmakroen Min Makro for denne nye tiden. Selvfølgelig kan du i praksis bruke alle andre tidsintervaller du trenger ved å sette funksjonsargumentene Tidsverdi i formatet tt:mm:ss.

Og til slutt, bare for enkelhets skyld, har flere sekvenslanseringsmakroer blitt lagt til. Hjemprodukt og dens ferdigstillelse Finish. Den siste bruker det fjerde metodeargumentet for å bryte sekvensen. På tide lik Falsk.

Totalt hvis du kjører makroen Hjemprodukt, så vil hele denne karusellen snurre, og vi vil se følgende bilde på arket:

Du kan stoppe sekvensen ved å kjøre henholdsvis makroen Finish. For enkelhets skyld kan du tilordne tastatursnarveier til begge makroene ved å bruke kommandoen Makroer – Alternativer tab utvikler (Utvikler – Makroer – Alternativer).

Kjøre en makro etter en tidsplan

Selvfølgelig er alt beskrevet ovenfor bare mulig hvis du kjører Microsoft Excel og filen vår er åpen i den. La oss nå se på en mer komplisert sak: du må kjøre Excel i henhold til en gitt tidsplan, for eksempel hver dag klokken 5:00, åpne en stor og kompleks rapport i den og oppdatere alle tilkoblinger og spørringer i den slik at den vil Vær klar når vi kommer på jobb 🙂

I en slik situasjon er det bedre å bruke Windows Scheduler – et program spesielt innebygd i enhver versjon av Windows som kan utføre spesifiserte handlinger etter en tidsplan. Faktisk bruker du det allerede uten å vite det, fordi PC-en din jevnlig sjekker etter oppdateringer, laster ned nye antivirusdatabaser, synkroniserer skymapper, osv. Det hele er Planleggerens arbeid. Så vår oppgave er å legge til en annen til de eksisterende oppgavene som vil starte Excel og åpne den angitte filen i den. Og vi skal henge makroen vår på arrangementet Arbeidsbok_Åpen denne filen – og problemet er løst.

Jeg vil med en gang advare deg om at arbeid med Planleggeren kan kreve avanserte brukerrettigheter, så hvis du ikke finner kommandoene og funksjonene beskrevet nedenfor på arbeidsdatamaskinen på kontoret, kontakt IT-spesialistene dine for å få hjelp.

Starter planleggeren

Så la oss starte planleggeren. For å gjøre dette kan du enten:

• Høyreklikk på knappen Start Og velg Datamaskinbehandling (databehandling)
• Velg i kontrollpanelet: Administrasjon – Oppgaveplanlegger (Kontrollpanel — Administrative verktøy — Oppgaveplanlegger)
• Velg fra hovedmenyen Start – Tilbehør – Systemverktøy – Oppgaveplanlegger
• Trykk tastaturgenvei Vinne+R, Tast inn taskschd.msc og trykk Enter

The following window should appear on the screen (I have an English version, but you can also have a version):

Lag en oppgave

For å opprette en ny oppgave ved hjelp av en enkel trinn-for-trinn-veiviser, klikk på lenken Lag en enkel oppgave (Opprett grunnleggende oppgave) i høyre panel.

I det første trinnet i veiviseren skriver du inn navnet og beskrivelsen av oppgaven som skal opprettes:

Klikk på knappen neste (Neste) og i neste trinn velger vi en trigger – lanseringsfrekvensen eller en hendelse som vil starte oppgaven vår (for eksempel å slå på datamaskinen):

Hvis du valgte Daglig (Daglig), så i neste trinn må du velge et spesifikt tidspunkt, startdato for sekvensen og trinn (hver 2. dag, 5. dag osv.):

Det neste trinnet er å velge en handling – Kjør programmet (Start et program):

Og til slutt, det mest interessante er hva som må åpnes:

på Program eller manus (Program/manus) du må legge inn stien til Microsoft Excel som et program, altså direkte til den kjørbare Excel. På forskjellige datamaskiner med forskjellige versjoner av Windows og Office kan denne filen være i forskjellige mapper, så her er noen måter du kan finne ut hvor:

• Høyreklikk på ikonet (snarveien) for å starte Excel på skrivebordet eller på oppgavelinjen og velg kommandoen materialer (Eiendommer), og kopier deretter banen fra linjen i vinduet som åpnes Target:

                       

• Åpne en hvilken som helst Excel-arbeidsbok, og åpne deretter Task manager (Oppgavebehandling) skyve Ctrl+andre+Fra og ved å høyreklikke på linjen Microsoft Excel, velg en kommando materialer (Eiendommer). I vinduet som åpnes kan du kopiere banen, ikke glem å legge til en omvendt skråstrek og EXCEL.EXE på slutten:

               

• Åpne Excel, åpne Visual Basic-redigering med hurtigtast andre+F11, åpent panel umiddelbar en kombinasjon av Ctrl+G, skriv inn kommandoen i den:

  ? Application.Path

  … og klikk videre Enter

  

  Kopier den resulterende banen, ikke glem å legge til en omvendt skråstrek og EXCEL.EXE på slutten.

på Legg til argumenter (valgfritt) (Legg til argumenter (valgfritt)) du må sette inn hele banen til boken med makroen vi vil åpne.

Når alt er lagt inn, klikk neste og deretter Finish (Bli ferdig). Oppgaven bør legges til den generelle listen:

Det er praktisk å administrere den opprettede oppgaven ved å bruke knappene til høyre. Her kan du teste oppgaven ved å kjøre den umiddelbart (løpe)uten å vente på den angitte tiden. Du kan deaktivere en oppgave midlertidig (Deaktiver)slik at den slutter å gå i en periode, for eksempel ferien din. Vel, du kan alltid endre parametrene (datoer, klokkeslett, filnavn) via knappen materialer (Eiendommer).

Legg til en makro for å åpne en fil

Nå gjenstår det å henge i boken vår lanseringen av makroen vi trenger på filåpningshendelsen. For å gjøre dette, åpne boken og gå til Visual Basic-editoren ved å bruke hurtigtasten andre+F11 eller knapper Visual Basic tab utvikler (Utvikler). I vinduet som åpnes i øvre venstre hjørne, må du finne filen vår på treet og dobbeltklikke for å åpne modulen Denne boken (Denne arbeidsboken).

Hvis du ikke ser dette vinduet i Visual Basic-editoren, kan du åpne det via menyen Vis — Prosjektutforsker.

I modulvinduet som åpnes, legg til en bok åpen hendelsesbehandler ved å velge den fra rullegardinlistene øverst Arbeidsbok и Åpen, henholdsvis:

En prosedyremal skal vises på skjermen. Arbeidsbok_Åpen, hvor mellom linjene Privat del и End Sub og du må sette inn de VBA-kommandoene som skal kjøres automatisk når denne Excel-arbeidsboken åpnes, når planleggeren åpner den i henhold til tidsplanen. Her er noen nyttige alternativer for overklokking:

• This Workbook.RefreshAll – Oppdaterer alle eksterne dataspørringer, Power Query-spørringer og pivottabeller. Det mest allsidige alternativet. Bare ikke glem å tillate tilkoblinger til eksterne data som standard og oppdatere lenker via Fil – Alternativer – Trust Center – Trust Center Options – Eksternt innhold, ellers, når du åpner boken, vil en standard advarsel vises og Excel, uten å oppdatere noe, vil vente på din velsignelse i form av å klikke på knappen Aktiver innhold (Aktiver innhold):

  

• ActiveWorkbook.Connections(“Connection_Name”). Oppdater — oppdatering av data på Connection_Name-tilkoblingen.
• Ark(“Sheet5").Pivottabeller("Pivottabell1«).PivotCache.Oppdater – oppdatering av en enkelt pivottabell med navn Pivottabell1 på arket Sheet5.
• Søknad.Beregn – omberegning av alle åpne Excel-arbeidsbøker.
• Application.CalculateFullRebuild – tvungen omberegning av alle formler og gjenoppbygging av alle avhengigheter mellom celler i alle åpne arbeidsbøker (tilsvarer å legge inn alle formler på nytt).
• Regneark ("Rapport"). Skriv ut – skrive ut ark Bilder.
• Ring MyMacro – kjør en makro kalt Min Makro.
• Denne arbeidsboken. Lagre – lagre gjeldende bok
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – lagre boken i en mappe D: Arkiv under navnet Bilder med dato og klokkeslett vedlagt navnet.

Hvis du vil at makroen bare skal kjøres når filen åpnes av planleggeren klokken 5:00, og ikke hver gang brukeren åpner arbeidsboken i løpet av arbeidsdagen, er det fornuftig å legge til en tidssjekk, for eksempel:

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

Det er alt. Ikke glem å lagre arbeidsboken din i et makroaktivert format (xlsm eller xlsb), så kan du trygt lukke Excel og gå hjem og la datamaskinen stå på. På et gitt tidspunkt (selv om PC-en er låst), vil Scheduler starte Excel og åpne den spesifiserte filen i den, og makroen vår vil utføre de programmerte handlingene. Og du vil kose deg i sengen mens den tunge rapporten din automatisk beregnes på nytt – skjønnhet! 🙂

• Hva er makroer, hvordan bruker du dem, hvor skal du sette inn Visual Basic-kode i Excel
• Hvordan lage ditt eget makrotillegg for Excel
• Slik bruker du den personlige makroarbeidsboken som et bibliotek for makroene dine i Excel