Pokretanje makroa na vrijeme

Vrlo čest slučaj u praksi: morate pokrenuti jedan ili više svojih makroa u datom trenutku ili na određenoj frekvenciji. Na primjer, imate veliki i težak izvještaj koji se ažurira pola sata i želite da pokrenete ažuriranje pola sata prije nego što stignete na posao ujutro. Ili imate makro koji bi trebao automatski slati e-poštu zaposlenima na određenoj frekvenciji. Ili, kada radite sa zaokretnom tabelom, želite da se ona ažurira u hodu svakih 10 sekundi, i tako dalje.

Pogledajmo šta Excel i Windows imaju mogućnosti da to implementiraju.

Pokretanje makroa na datoj frekvenciji

Najlakši način da to učinite je korištenje ugrađene VBA metode Application.OnTimeAn koji pokreće navedeni makro u određeno vrijeme. Hajde da to shvatimo na praktičnom primjeru.

Otvorite Visual Basic editor sa istoimenim dugmetom na kartici Developer (programer) ili prečica na tastaturi alt+F11, ubacite novi modul kroz meni Umetak – Modul i tamo kopirajte sljedeći kod:

Dim TimeToRun 'globalna varijabla gdje je pohranjeno vrijeme sljedećeg pokretanja 'ovo je glavni makro Sub MyMacro() Application.Calculate 'ponovo izračunajte opseg knjige("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill ćelija A1 sa nasumičnom bojom :) Pozovite NextRun 'pokrenite makro NextRun da postavite vrijeme sljedećeg pokretanja End Sub' ovaj makro postavlja vrijeme za sljedeće pokretanje glavnog makroa Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'dodajte 3 sekunde trenutnom vremenu Application.OnTime TimeToRun, "MyMacro" 'zakažite sljedeće pokretanje End Sub 'makro da započnete sekvencu ponavljanja Sub Start() Pozovite NextRun End Sub 'makro da zaustavite sekvencu ponavljanja Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Hajde da shvatimo šta je šta ovde.

Prvo, potrebna nam je varijabla koja će pohraniti vrijeme sljedećeg pokretanja našeg makroa – nazvao sam je TimeToRun. Imajte na umu da sadržaj ove varijable mora biti dostupan svim našim narednim makroima, tako da ga moramo napraviti globalni, tj. deklarirati na samom početku modula prije prvog Izmjene.

Zatim dolazi naš glavni makro MyMacro, koji će izvršiti glavni zadatak – preračunati knjigu koristeći metodu Aplikacija.Izračunaj. Da bi bilo jasnije, dodao sam formulu =TDATE() na list u ćeliji A1, koji prikazuje datum i vrijeme – kada se ponovo izračuna, njegov sadržaj će se ažurirati pred našim očima (samo uključite prikaz sekundi u ćeliji formatu). Za dodatnu zabavu, makrou sam dodao i naredbu za popunjavanje ćelije A1 nasumično odabranom bojom (kod boje je cijeli broj u rasponu 0..56, koji je generiran funkcijom RND i zaokružuje na cjelobrojnu funkciju Int).

makro NextRun dodaje prethodnoj vrijednosti TimeToRun Još 3 sekunde, a zatim zakazuje sljedeće pokretanje glavnog makroa MyMacro za ovo novo vrijeme. Naravno, u praksi možete koristiti bilo koje druge vremenske intervale koji su vam potrebni postavljanjem argumenata funkcije TimeValue u formatu hh:mm:ss.

I na kraju, samo radi praktičnosti, dodano je više makroa za pokretanje sekvence. POČETNA i njegov završetak završiti. Posljednji koristi četvrti argument metode za prekid niza. Na vrijeme jednak lažan.

Ukupno ako pokrenete makro POČETNA, onda će se cijeli ovaj vrtuljak vrtjeti, a na listu ćemo vidjeti sljedeću sliku:

Niz možete zaustaviti pokretanjem, odnosno, makroa završiti. Radi praktičnosti, možete dodijeliti prečice na tipkovnici oba makroa pomoću naredbe Makroi – Opcije tabulator Developer (Programer — Makroi — Opcije).

Pokretanje makroa po rasporedu

Naravno, sve gore opisano moguće je samo ako imate pokrenut Microsoft Excel i u njemu je otvoren naš fajl. Pogledajmo sada složeniji slučaj: potrebno je da pokrenete Excel prema datom rasporedu, na primjer, svaki dan u 5:00, u njemu otvorite veliki i složeni izvještaj i ažurirate sve veze i upite u njemu tako da budite spremni do dolaska na posao 🙂

U takvoj situaciji bolje je koristiti Windows Planer – program posebno ugrađen u bilo koju verziju Windows-a koji može izvršiti određene radnje prema rasporedu. U stvari, već ga koristite a da to niste svesni, jer vaš računar redovno proverava da li postoje ažuriranja, preuzima nove antivirusne baze podataka, sinhronizuje fascikle u oblaku, itd. Sve je to delo Planera. Dakle, naš zadatak je da postojećim zadacima dodamo još jedan koji će pokrenuti Excel i otvoriti navedeni fajl u njemu. A mi ćemo okačiti naš makro na događaj Workbook_Open ovaj fajl – i problem je rešen.

Želim da vas odmah upozorim da rad sa Planerom može zahtevati napredna korisnička prava, pa ako ne možete da pronađete dole opisane komande i funkcije na svom radnom računaru u kancelariji, obratite se svojim IT stručnjacima za pomoć.

Pokretanje Planera

Pokrenimo planer. Da biste to učinili, možete:

  • Desni klik na dugme start i odaberite Upravljanje računarima (upravljanje računarom)
  • Odaberite na kontrolnoj tabli: Administracija – Planer zadataka (Kontrolna tabla — Administrativni alati — Planer zadataka)
  • Odaberite iz glavnog menija Start – Pribor – Sistemski alati – Planer zadataka
  • Pritisnite tastersku prečicu pobijediti+R, unesite taskchd.msc i pritisnite ući

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

Pokretanje makroa na vrijeme

Stvorite zadatak

Da biste kreirali novi zadatak pomoću jednostavnog čarobnjaka korak po korak, kliknite na vezu Kreirajte jednostavan zadatak (Kreiraj osnovni zadatak) u desnom panelu.

U prvom koraku čarobnjaka unesite naziv i opis zadatka koji se kreira:

Pokretanje makroa na vrijeme

Kliknite na dugme sljedeći (Sljedeći) a u sljedećem koraku biramo okidač – frekvenciju pokretanja ili događaj koji će pokrenuti naš zadatak (na primjer, uključivanje računala):

Pokretanje makroa na vrijeme

Ako ste izabrali svakodnevno (Dnevno), zatim u sljedećem koraku morat ćete odabrati određeno vrijeme, datum početka niza i korak (svaki 2. dan, 5. dan, itd.):

Pokretanje makroa na vrijeme

Sljedeći korak je odabir akcije – Pokrenite program (Pokreni program):

Pokretanje makroa na vrijeme

I na kraju, najzanimljivije je šta tačno treba otvoriti:

Pokretanje makroa na vrijeme

U Program ili skripta (Program/skripta) potrebno je da unesete putanju do Microsoft Excela kao programa, odnosno direktno do Excel izvršne datoteke. Na različitim računarima sa različitim verzijama Windowsa i Officea, ova datoteka može biti u različitim fasciklama, pa evo nekoliko načina da saznate njenu lokaciju:

  • Kliknite desnim tasterom miša na ikonu (prečicu) da pokrenete Excel na radnoj površini ili na traci zadataka i izaberite komandu Materijali (Svojstva), a zatim u prozoru koji se otvori kopirajte putanju iz linije meta:

    Pokretanje makroa na vrijeme                      Pokretanje makroa na vrijeme

  • Otvorite bilo koju Excel radnu svesku, a zatim otvorite Task Manager (Upravitelj zadataka) guranje Ctrl+alt+od i desnim klikom na liniju Microsoft Excel, izaberite komandu Materijali (Svojstva). U prozoru koji se otvori, možete kopirati putanju, ne zaboravljajući da joj dodate obrnutu kosu crtu i EXCEL.EXE na kraju:

    Pokretanje makroa na vrijeme              Pokretanje makroa na vrijeme

  • Otvorite Excel, otvorite Visual Basic editor sa prečicom na tastaturi alt+F11, otvoreni panel odmah kombinacija Ctrl+G, u njega unesite naredbu:

    ? Application.Path

    … i kliknite na ući

    Pokretanje makroa na vrijeme

    Kopirajte rezultirajuću putanju, ne zaboravljajući da joj dodate obrnutu kosu crtu i EXCEL.EXE na kraju.

U Dodajte argumente (opciono) (Dodajte argumente (opcionalno)) potrebno je da umetnete punu putanju do knjige sa makroom koji želimo da otvorimo.

Kada sve unesete, kliknite sljedeći i onda završiti (završetak). Zadatak treba dodati na opštu listu:

Pokretanje makroa na vrijeme

Pogodno je upravljati kreiranim zadatkom pomoću dugmadi s desne strane. Ovdje možete testirati zadatak tako što ćete ga odmah pokrenuti (trčati)bez čekanja određenog vremena. Možete privremeno deaktivirati zadatak (Onemogući)tako da prestane da radi na određeno vreme, kao što je vaš odmor. Pa, uvijek možete promijeniti parametre (datume, vrijeme, naziv datoteke) putem dugmeta Materijali (Svojstva).

Dodajte makro za otvaranje datoteke

Sada ostaje da objesimo u našu knjigu pokretanje makroa koji nam je potreban na događaj otvaranja datoteke. Da biste to učinili, otvorite knjigu i idite na Visual Basic editor koristeći prečicu na tastaturi alt+F11 ili dugmad Visual Basic tabulator Developer (programer). U prozoru koji se otvara u gornjem lijevom kutu potrebno je pronaći našu datoteku na stablu i dvaput kliknuti da otvorite modul Ova knjiga (ova radna sveska).

Ako ne vidite ovaj prozor u uređivaču Visual Basic-a, možete ga otvoriti kroz meni Pogled — Project Explorer.

U prozoru modula koji se otvori dodajte rukovalac događaja otvaranja knjige tako što ćete ga odabrati sa padajućih lista na vrhu Radna sveska и otvoreno, odnosno:

Pokretanje makroa na vrijeme

Predložak procedure bi se trebao pojaviti na ekranu. Workbook_Open, gdje između redova Private Sub и End Sub i treba da ubacite one VBA komande koje treba da se automatski izvrše kada se ova Excel radna sveska otvori, kada je Planer otvori prema rasporedu. Evo nekoliko korisnih opcija za overclocking:

  • ThisWorkbook.RefreshAll – Osvježava sve eksterne upite podataka, Power Query upite i zaokretne tabele. Najsvestranija opcija. Samo ne zaboravite da omogućite veze sa vanjskim podacima prema zadanim postavkama i ažurirate veze putem Datoteka – Opcije – Centar za pouzdanost – Opcije centra za pouzdanost – Vanjski sadržaj, inače, kada otvorite knjigu, pojavit će se standardno upozorenje i Excel će, bez ažuriranja ništa, čekati vaš blagoslov u vidu klika na dugme Omogući sadržaj (Omogući sadržaj):

    Pokretanje makroa na vrijeme

  • ActiveWorkbook.Connections(“Connection_Name”).Osvježi — ažuriranje podataka na vezi Connection_Name.
  • Sheets(“Sheet5“).Zaokretne tabele(“Zaokretna tabela1«).PivotCache.Osvježi – ažuriranje jedne pivot tablice pod nazivom Zaokretna tabela1 na listu List5.
  • Aplikacija.Izračunaj – preračunavanje svih otvorenih Excel radnih knjiga.
  • Application.CalculateFullRebuild – prisilno ponovno izračunavanje svih formula i ponovna izgradnja svih zavisnosti između ćelija u svim otvorenim radnim knjigama (ekvivalentno ponovnom unosu svih formula).
  • Radni listovi(“Izvještaj”).PrintOut – štampani list Fotografije.
  • Pozovite MyMacro – pokrenite makro pod nazivom MyMacro.
  • ThisWorkbook.Save – sačuvati trenutnu knjigu
  • OveWorkbooks.Save As “D:ArchiveReport” & Zamijeni (Sada, “:”, “-“) & “.xlsx” – sačuvajte knjigu u fasciklu D: Arhiva pod imenom Fotografije sa datumom i vremenom pridodatim imenu.

Ako želite da se makro izvršava samo kada planer otvori datoteku u 5:00 ujutro, a ne svaki put kada korisnik otvori radnu knjigu tokom radnog dana, onda ima smisla dodati provjeru vremena, na primjer:

Ako je Format(Sada, "hh:mm") = "05:00" Onda je ova radna sveska.RefreshAll  

To je sve. Ne zaboravite da sačuvate svoju radnu svesku u formatu sa omogućenim makroima (xlsm ili xlsb) i možete bezbedno zatvoriti Excel i otići kući, ostavljajući računar uključen. U datom trenutku (čak i ako je računar zaključan), Planer će pokrenuti Excel i otvoriti navedenu datoteku u njemu, a naš makro će izvršiti programirane radnje. I uživat ćete u krevetu dok se vaš težak izvještaj automatski preračunava – ljepota! 🙂

  • Šta su makroi, kako ih koristiti, gdje umetnuti Visual Basic kod u Excel
  • Kako da kreirate sopstveni makro dodatak za Excel
  • Kako koristiti ličnu radnu svesku makroa kao biblioteku za svoje makroe u Excel-u

Ostavite odgovor