Kör ett makro i tid

Ett mycket vanligt fall i praktiken: du behöver köra ett eller flera av dina makron vid en given tidpunkt eller vid en viss frekvens. Du har till exempel en stor och tung rapport som uppdateras en halvtimme och du vill gärna köra uppdateringen en halvtimme innan du kommer till jobbet på morgonen. Eller så har du ett makro som automatiskt ska skicka ut e-postmeddelanden till anställda med en viss frekvens. Eller, när du arbetar med en pivottabell, vill du att den ska uppdateras i farten var tionde sekund, och så vidare.

Låt oss titta på vad Excel och Windows har förmågan att implementera detta.

Köra ett makro vid en given frekvens

Det enklaste sättet att göra detta är att använda den inbyggda VBA-metoden Application.OnTimeEn som kör det angivna makrot vid den angivna tiden. Låt oss förstå detta med ett praktiskt exempel.

Öppna Visual Basic-redigeraren med knappen med samma namn på fliken utvecklare (Utvecklaren) eller kortkommandon andra+F11, infoga en ny modul genom menyn Infoga – Modul och kopiera följande kod dit:

Dim TimeToRun 'global variabel där nästa körtid lagras' det här är huvudmakrot Sub MyMacro() Application.Calculate 'beräkna om boken Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cell A1 med en slumpmässig färg :) Call NextRun 'kör NextRun-makrot för att ställa in nästa körtid End Sub ' detta makro ställer in tiden för nästa körning av huvudmakrot Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'lägg till 3 sekunder till den aktuella tiden Application.OnTime TimeToRun, "MyMacro" 'schemalägg nästa körning End Sub 'makro för att starta upprepningssekvensen Sub Start() Ring NextRun End Sub 'makro för att stoppa upprepningssekvensen Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Låt oss ta reda på vad som är vad här.

Först behöver vi en variabel som lagrar tiden för nästa körning av vårt makro – jag kallade det TimeToRun. Observera att innehållet i denna variabel måste vara tillgängligt för alla våra efterföljande makron, så vi måste göra det global, dvs deklarera i början av modulen före den första Sub.

Därefter kommer vårt huvudmakro MyMacro, som kommer att utföra huvuduppgiften – att räkna om boken med metoden Applikation. Beräkna. För att göra det tydligare lade jag till formeln =TDATE() till arket i cell A1, som visar datum och tid – när det räknas om kommer innehållet att uppdateras mitt framför våra ögon (slå bara på visningen av sekunder i cellen formatera). För extra kul lade jag även till makrot kommandot att fylla cell A1 med en slumpmässigt vald färg (färgkoden är ett heltal i intervallet 0..56, som genereras av funktionen Rnd och avrundar uppåt till en heltalsfunktion Int).

Makro NextRun läggs till det tidigare värdet TimeToRun 3 sekunder till och schemalägger sedan nästa körning av huvudmakrot MyMacro för denna nya tid. Naturligtvis kan du i praktiken använda alla andra tidsintervall du behöver genom att ställa in funktionsargumenten Tidsvärde i formatet hh:mm:ss.

Och slutligen, bara för bekvämlighets skull, har fler sekvensstartsmakron lagts till. Hem och dess slutförande Finish. Den sista använder det fjärde metodargumentet för att bryta sekvensen. På gång lika Falsk.

Totalt om du kör makrot Hem, då snurrar hela karusellen, och vi kommer att se följande bild på arket:

Du kan stoppa sekvensen genom att köra makrot Finish. För enkelhetens skull kan du tilldela kortkommandon till båda makron med kommandot Makron – Alternativ fliken utvecklare (Utvecklare — Makron — Alternativ).

Köra ett makro enligt ett schema

Naturligtvis är allt som beskrivs ovan endast möjligt om du har Microsoft Excel igång och vår fil är öppen i den. Låt oss nu titta på ett mer komplicerat fall: du måste köra Excel enligt ett givet schema, till exempel varje dag klockan 5:00, öppna en stor och komplex rapport i den och uppdatera alla anslutningar och frågor i den så att den kommer att vara redo när vi kommer till jobbet 🙂

I en sådan situation är det bättre att använda Windows Schemaläggare – ett program som är speciellt inbyggt i alla versioner av Windows som kan utföra specificerade åtgärder enligt ett schema. Faktum är att du redan använder det utan att veta om det, eftersom din dator regelbundet söker efter uppdateringar, laddar ner nya antivirusdatabaser, synkroniserar molnmappar, etc. Allt är schemaläggarens arbete. Så vår uppgift är att lägga till ytterligare en till de befintliga uppgifterna som kommer att starta Excel och öppna den angivna filen i den. Och vi kommer att hänga upp vårt makro på eventet Arbetsbok_Öppna denna fil – och problemet är löst.

Jag vill genast varna dig för att arbete med Schemaläggaren kan kräva avancerade användarrättigheter, så om du inte kan hitta de kommandon och funktioner som beskrivs nedan på din arbetsdator på kontoret, kontakta dina IT-specialister för hjälp.

Startar Schemaläggaren

Så låt oss starta Schemaläggaren. För att göra detta kan du antingen:

• Högerklicka på knappen Start Och välj Datorhantering (Datorhantering)
• Välj i kontrollpanelen: Administration – Task Scheduler (Kontrollpanelen — Administrativa verktyg — Schemaläggare)
• Välj från huvudmenyn Start – Tillbehör – Systemverktyg – Schemaläggare
• Tryck på snabbtangenten Vinna+R, stiga på taskschd.msc och tryck ange

Följande fönster bör visas på skärmen (jag har en engelsk version, men du kan också ha en version):

Skapa en uppgift

För att skapa en ny uppgift med en enkel steg-för-steg-guide, klicka på länken Skapa en enkel uppgift (Skapa grundläggande uppgift) i den högra panelen.

I det första steget i guiden anger du namnet och beskrivningen av uppgiften som ska skapas:

Klicka på knappen Nästa (Nästa) och i nästa steg väljer vi en trigger – lanseringsfrekvensen eller en händelse som kommer att starta vår uppgift (till exempel att slå på datorn):

Om du väljer Dagligen (Dagligen), sedan i nästa steg måste du välja en specifik tidpunkt, startdatum för sekvensen och steg (varannan dag, 2:e dag, etc.):

Nästa steg är att välja en åtgärd – Kör programmet (Starta ett program):

Och slutligen, det mest intressanta är vad exakt som behöver öppnas:

I Program eller manus (Program/manus) du behöver ange sökvägen till Microsoft Excel som ett program, dvs direkt till den exekverbara filen. På olika datorer med olika versioner av Windows och Office kan den här filen finnas i olika mappar, så här är några sätt för dig att ta reda på var den är:

• Högerklicka på ikonen (genväg) för att starta Excel på skrivbordet eller i aktivitetsfältet och välj kommandot material (Egenskaper), och kopiera sedan sökvägen från raden i fönstret som öppnas Målet:

                       

• Öppna valfri Excel-arbetsbok och öppna sedan Task manager (Aktivitetshanteraren) trycka ctrl+andra+Från och genom att högerklicka på raden Microsoft Excel, välj ett kommando material (Egenskaper). I fönstret som öppnas kan du kopiera sökvägen, glöm inte att lägga till ett omvänt snedstreck och EXCEL.EXE i slutet:

               

• Öppna Excel, öppna Visual Basic-redigeraren med kortkommandon andra+F11, öppen panel omedelbar en kombination av ctrl+G, skriv in kommandot i det:

  ? Application.Path

  … och klicka på ange

  

  Kopiera den resulterande sökvägen, glöm inte att lägga till ett omvänt snedstreck och EXCEL.EXE i slutet.

I Lägg till argument (valfritt) (Lägg till argument (valfritt)) du måste infoga hela sökvägen till boken med makrot som vi vill öppna.

När allt har skrivits in klickar du Nästa och då Finish (Avsluta). Uppgiften bör läggas till den allmänna listan:

Det är bekvämt att hantera den skapade uppgiften med knapparna till höger. Här kan du testa uppgiften genom att köra den direkt (springa)utan att vänta på angiven tid. Du kan tillfälligt inaktivera en uppgift (Inaktivera)så att den slutar fungera under en period, till exempel din semester. Tja, du kan alltid ändra parametrarna (datum, tid, filnamn) genom knappen material (Egenskaper).

Lägg till ett makro för att öppna en fil

Nu återstår att hänga i vår bok lanseringen av makrot vi behöver på filöppningshändelsen. För att göra detta, öppna boken och gå till Visual Basic-redigeraren med kortkommandot andra+F11 eller knappar Visual Basic fliken utvecklare (Utvecklaren). I fönstret som öppnas i det övre vänstra hörnet måste du hitta vår fil i trädet och dubbelklicka för att öppna modulen Denna bok (Denna arbetsbok).

Om du inte ser det här fönstret i Visual Basic-redigeraren kan du öppna det via menyn Visa — Project Explorer.

I modulfönstret som öppnas lägger du till en boköppningshändelsehanterare genom att välja den från rullgardinslistorna högst upp Arbetsbok и Öppenrespektive:

En procedurmall bör visas på skärmen. Arbetsbok_Öppna, var mellan raderna Privat del и End Sub och du måste infoga de VBA-kommandon som ska köras automatiskt när denna Excel-arbetsbok öppnas, när Schemaläggaren öppnar den enligt schemat. Här är några användbara alternativ för överklockning:

• This Workbook.RefreshAll – Uppdaterar alla externa datafrågor, Power Query-frågor och pivottabeller. Det mest mångsidiga alternativet. Glöm bara inte att tillåta anslutningar till extern data som standard och uppdatera länkar via Fil – Alternativ – Trust Center – Trust Center-alternativ – Externt innehåll, annars, när du öppnar boken, kommer en standardvarning att visas och Excel, utan att uppdatera något, väntar på din välsignelse i form av att klicka på knappen Aktivera innehåll (Aktivera innehåll):

  

• ActiveWorkbook.Connections(“Connection_Name”). Uppdatera — uppdatering av data på Connection_Name-anslutningen.
• Sheets(“Sheet5").PivotTables("Pivottabell1«).PivotCache.Uppdatera – uppdatera en enda pivottabell med namnet Pivottabell1 på arket Sheet5.
• Applikation. Beräkna – omräkning av alla öppna Excel-arbetsböcker.
• Application.CalculateFullRebuild – framtvingad omräkning av alla formler och återuppbyggnad av alla beroenden mellan celler i alla öppna arbetsböcker (motsvarar att återinmata alla formler).
• Arbetsblad ("Rapport"). Skriv ut – skriva ut ark Bilder.
• Ring MyMacro – kör ett makro med namnet MyMacro.
• This Workbook.Save – spara den aktuella boken
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Nu, “:”, “-“) & “.xlsx” – spara boken i en mapp D: Arkiv under namnet Bilder med datum och tid bifogat till namnet.

Om du vill att makrot endast ska köras när filen öppnas av schemaläggaren klockan 5:00, och inte varje gång användaren öppnar arbetsboken under arbetsdagen, är det vettigt att lägga till en tidskontroll, till exempel:

Om Format(Nu, "hh:mm") = "05:00" Då ThisWorkbook.RefreshAll  

Det är allt. Glöm inte att spara din arbetsbok i ett makroaktiverat format (xlsm eller xlsb) så kan du säkert stänga Excel och gå hem och lämna datorn påslagen. Vid ett givet tillfälle (även om datorn är låst) kommer Scheduler att starta Excel och öppna den angivna filen i den, och vårt makro kommer att utföra de programmerade åtgärderna. Och du kommer att njuta i sängen medan din tunga rapport automatiskt räknas om – skönhet! 🙂

• Vad är makron, hur man använder dem, var man infogar Visual Basic-kod i Excel
• Hur du skapar ditt eget makrotillägg för Excel
• Så här använder du den personliga makroarbetsboken som ett bibliotek för dina makron i Excel