Rularea la timp a unei macrocomenzi

Un caz foarte frecvent în practică: trebuie să rulați una sau mai multe macrocomenzi la un moment dat sau la o anumită frecvență. De exemplu, aveți un raport mare și greu care se actualizează cu o jumătate de oră și ați dori să rulați actualizarea cu o jumătate de oră înainte de a ajunge dimineața la serviciu. Sau aveți o macrocomandă care ar trebui să trimită automat e-mailuri către angajați la o frecvență specificată. Sau, când lucrați cu un PivotTable, doriți ca acesta să se actualizeze din mers la fiecare 10 secunde și așa mai departe.

Să ne uităm la ce Excel și Windows au capacitatea de a implementa acest lucru.

Rularea unei macrocomenzi la o frecvență dată

Cel mai simplu mod de a face acest lucru este să utilizați metoda VBA încorporată Aplicație.OnTimeUn care rulează macrocomanda specificată la ora specificată. Să înțelegem asta cu un exemplu practic.

Deschideți editorul Visual Basic cu butonul cu același nume din filă dezvoltator (Dezvoltator) sau scurtătură de la tastatură Alt+F11, introduceți un modul nou prin meniu Inserare – Modul și copiați următorul cod acolo:

Dim TimeToRun 'variabila globală unde este stocată următorul timp de rulare 'aceasta este macro-ul principal Sub MyMacro() Application.Calculate 'recalculați cartea Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill celula A1 cu o culoare aleatorie :) Apelați NextRun „rulați macrocomanda NextRun pentru a seta următoarea durată de rulare End Sub „această macrocomandă setează timpul pentru următoarea rulare a macrocomenzii principale Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") „adăugați 3 secunde la ora curentă Application.OnTime TimeToRun, „MyMacro” „programați următoarea rulare End Sub „macro pentru a începe secvența de repetare Sub Start() Apelați NextRun End Sub „macro pentru a opri secvența repetată Sub Finish() Application.OnTime TimeToRun, „MyMacro”, , False End Sub  

Să ne dăm seama ce este aici.

În primul rând, avem nevoie de o variabilă care să stocheze timpul următoarei rulări a macrocomenzii noastre - am numit-o TimeToRun. Vă rugăm să rețineți că conținutul acestei variabile trebuie să fie disponibil pentru toate macrocomenzile noastre ulterioare, așa că trebuie să o facem global, adică declara chiar la începutul modulului înainte de primul Sub.

Urmează macro-ul nostru principal MyMacro, care va îndeplini sarcina principală – să recalculeze cartea folosind metoda Aplicație.Calculează. Pentru a fi mai clar, am adăugat formula =TDATE() în foaia din celula A1, care afișează data și ora – când este recalculată, conținutul său va fi actualizat chiar în fața ochilor noștri (doar să porniți afișarea secundelor în celulă). format). Pentru un plus de distracție, am adăugat la macrocomandă și comanda de a umple celula A1 cu o culoare aleasă aleatoriu (codul de culoare este un număr întreg în intervalul 0..56, care este generat de funcție RND și rotunjește la o funcție întreagă Int).

Macro NextRun se adaugă la valoarea anterioară TimeToRun încă 3 secunde și apoi programează următoarea rulare a macrocomenzii principale MyMacro pentru acest nou timp. Desigur, în practică, puteți utiliza orice alte intervale de timp de care aveți nevoie prin setarea argumentelor funcției TimeValue în formatul hh:mm:ss.

Și, în cele din urmă, doar pentru comoditate, au fost adăugate mai multe macrocomenzi de lansare a secvenței. Acasă și finalizarea acestuia finalizarea. Ultimul folosește al patrulea argument al metodei pentru a rupe secvența. La timp egal Fals.

Total dacă rulați macro-ul Acasă, atunci întregul carusel se va învârti și vom vedea următoarea imagine pe foaie:

Puteți opri secvența rulând, respectiv, macro-ul finalizarea. Pentru comoditate, puteți atribui comenzi rapide de la tastatură ambelor macrocomenzi folosind comanda Macro-uri – Opțiuni fila dezvoltator (Dezvoltator — Macrocomenzi — Opțiuni).

Rularea unei macrocomenzi conform unui program

Desigur, tot ceea ce este descris mai sus este posibil numai dacă rulați Microsoft Excel și fișierul nostru este deschis în el. Acum să ne uităm la un caz mai complicat: trebuie să rulați Excel conform unui program dat, de exemplu, în fiecare zi la 5:00, să deschideți un raport mare și complex în el și să actualizați toate conexiunile și interogările din el, astfel încât să fie fiți gata până ajungem la serviciu 🙂

Într-o astfel de situație, este mai bine să utilizați Windows Scheduler – un program special construit în orice versiune de Windows care poate efectua acțiuni specificate într-un program. De fapt, îl folosești deja fără să știi, deoarece PC-ul tău verifică în mod regulat actualizările, descarcă noi baze de date antivirus, sincronizează folderele cloud etc. Toată treaba Scheduler-ului. Deci sarcina noastră este să adăugăm la sarcinile existente încă una care va lansa Excel și va deschide fișierul specificat în el. Și ne vom agăța macro-ul la eveniment Caiet de lucru_Deschis acest fișier – iar problema este rezolvată.

Vreau să vă avertizez imediat că lucrul cu Scheduler poate necesita drepturi avansate de utilizator, așa că dacă nu puteți găsi comenzile și funcțiile descrise mai jos pe computerul de lucru din birou, contactați specialiștii IT pentru ajutor.

Lansarea Schedulerului

Deci, să începem Scheduler. Pentru a face acest lucru, puteți:

• Faceți clic dreapta pe buton acasă Și alegeți Gestionarea computerelor (gestionarea computerelor)
• Selectați în Panoul de control: Administrare – Task Scheduler (Panou de control — Instrumente administrative — Programator de activități)
• Selectați din meniul principal Start – Accesorii – Instrumente de sistem – Task Scheduler
• Apăsați tasta rapidă de la tastatură A castiga+R, introduce taskschd.msc și apăsați Intrați

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

Creați o sarcină

Pentru a crea o sarcină nouă folosind un asistent simplu pas cu pas, faceți clic pe link Creați o sarcină simplă (Creați sarcina de bază) în panoul din dreapta.

La primul pas al expertului, introduceți numele și descrierea sarcinii de creat:

Faceți clic pe buton Pagina Următoare → (Următor →) iar în pasul următor selectăm un declanșator – frecvența de lansare sau un eveniment care ne va lansa sarcina (de exemplu, pornirea computerului):

Dacă ai ales Zilnic (Zilnic), apoi la pasul următor va trebui să selectați o anumită oră, data de începere a secvenței și pas (la fiecare a 2-a zi, a 5-a zi etc.):

Următorul pas este să alegeți o acțiune - Rulați programul (Porniți un program):

Și, în sfârșit, cel mai interesant lucru este exact ce trebuie deschis:

În Program sau scenariu (Program/script) trebuie să introduceți calea către Microsoft Excel ca program, adică direct către executabilul Excel. Pe diferite computere cu diferite versiuni de Windows și Office, acest fișier poate fi în foldere diferite, așa că iată câteva modalități de a afla locația lui:

• Faceți clic dreapta pe pictogramă (comandă rapidă) pentru a lansa Excel pe desktop sau în bara de activități și selectați comanda materiale (Proprietăți), iar apoi în fereastra care se deschide, copiați calea din linie Ţintă:

                       

• Deschideți orice registru de lucru Excel, apoi deschideți Task Manager (Gestionar de sarcini) împingând Ctrl+Alt+De la și făcând clic dreapta pe linie Microsoft Excel, alegeți o comandă materiale (Proprietăți). În fereastra care se deschide, puteți copia calea, fără a uita să adăugați o bară oblică inversă și EXCEL.EXE la sfârșit:

               

• Deschideți Excel, deschideți editorul Visual Basic cu comanda rapidă de la tastatură Alt+F11, panoul deschis imediat o combinație de Ctrl+G, introduceți comanda în ea:

  ? Aplicație.Cale

  … și faceți clic pe Intrați

  

  Copiați calea rezultată, fără a uita să adăugați o bară oblică inversă și EXCEL.EXE la sfârșit.

În Adăugați argumente (opțional) (Adăugați argumente (opțional)) trebuie să inserați calea completă către carte cu macrocomanda pe care vrem să o deschidem.

Când totul este introdus, apoi faceți clic Pagina Următoare → și apoi finalizarea (Finalizarea). Sarcina ar trebui adăugată la lista generală:

Este convenabil să gestionați sarcina creată folosind butoanele din dreapta. Aici puteți testa sarcina rulând-o imediat (alerga)fără a aștepta timpul specificat. Puteți dezactiva temporar o sarcină (Dezactivare)astfel încât să se oprească pentru o perioadă de timp, cum ar fi vacanța dvs. Ei bine, puteți schimba oricând parametrii (date, ora, numele fișierului) prin intermediul butonului materiale (Proprietăți).

Adăugați o macrocomandă pentru a deschide un fișier

Acum rămâne să agățăm în cartea noastră lansarea macrocomenzii de care avem nevoie la evenimentul de deschidere a fișierului. Pentru a face acest lucru, deschideți cartea și accesați editorul Visual Basic folosind comanda rapidă de la tastatură Alt+F11 sau butoane Visual Basic fila dezvoltator (Dezvoltator). În fereastra care se deschide în colțul din stânga sus, trebuie să găsiți fișierul nostru în arbore și să faceți dublu clic pentru a deschide modulul Această carte (Acest registru de lucru).

Dacă nu vedeți această fereastră în editorul Visual Basic, atunci o puteți deschide prin meniu Vizualizare — Explorator de proiecte.

În fereastra de modul care se deschide, adăugați un handler de evenimente de deschidere a cărții selectându-l din listele derulante din partea de sus Workbook и Operatii Deschise, respectiv:

Pe ecran ar trebui să apară un șablon de procedură. Caiet de lucru_Deschis, unde între rânduri Sub privat и End Sub și trebuie să inserați acele comenzi VBA care ar trebui să fie executate automat atunci când acest registru de lucru Excel este deschis, când Scheduler îl deschide conform programului. Iată câteva opțiuni utile pentru overclocking:

• ThisWorkbook.RefreshAll – Actualizează toate interogările de date externe, interogările Power Query și PivotTables. Cea mai versatilă opțiune. Doar nu uitați să permiteți conexiunile la date externe în mod implicit și să actualizați linkurile prin Fișier – Opțiuni – Centru de încredere – Opțiuni centru de încredere – Conținut extern, în caz contrar, când deschideți cartea, va apărea un avertisment standard și Excel, fără a actualiza nimic, va aștepta binecuvântarea dvs. sub formă de clic pe butonul Activați conținutul (Activați conținutul):

  

• ActiveWorkbook.Connections(„Connection_Name”).Reîmprospătare — actualizarea datelor privind conexiunea Connection_Name.
• Foi ("Foaie 5„).PivotTables(„PivotTable1«).PivotCache.Reîmprospătare – actualizarea unui singur tabel pivot numit Tabel Pivot1 pe foaie Sheet5.
• Aplicație.Calculează – recalcularea tuturor registrelor de lucru Excel deschise.
• Aplicație.CalculateFullRebuild – recalcularea forțată a tuturor formulelor și reconstruirea tuturor dependențelor dintre celule din toate registrele de lucru deschise (echivalent cu reintroducerea tuturor formulelor).
• Fișe de lucru ("Raport").PrintOut – fișă de imprimare Fotografii.
• Apelați MyMacro – rulați o macrocomandă numită MyMacro.
• Acest registru de lucru.Salvați – salvați cartea curentă
  
• ThisWorkbooks.SaveAs „D:ArchiveReport” & Înlocuire(Acum, „:”, „-“) și „.xlsx” – salvați cartea într-un folder D: Arhivă sub nume Fotografii cu data și ora adăugate la nume.

Dacă doriți ca macrocomanda să fie executată numai atunci când fișierul este deschis de către Scheduler la ora 5:00 și nu de fiecare dată când utilizatorul deschide registrul de lucru în timpul zilei de lucru, atunci este logic să adăugați o verificare a timpului, de exemplu:

Dacă Format(Acum, "hh:mm") = "05:00", atunci ThisWorkbook.RefreshAll  

Asta e tot. Nu uitați să salvați registrul de lucru într-un format activat pentru macrocomandă (xlsm sau xlsb) și puteți închide Excel în siguranță și mergeți acasă, lăsând computerul pornit. La un moment dat (chiar dacă PC-ul este blocat), Scheduler-ul va lansa Excel și va deschide fișierul specificat în el, iar macro-ul nostru va efectua acțiunile programate. Și te vei bucura în pat în timp ce raportul tău greu este recalculat automat - frumusețe! 🙂

• Ce sunt macrocomenzile, cum să le folosești, unde să inserezi codul Visual Basic în Excel
• Cum să vă creați propriul program de completare macro pentru Excel
• Cum să utilizați registrul de lucru cu macrocomenzi personale ca bibliotecă pentru macrocomenzile dvs. în Excel