Pokretanje makronaredbe na vrijeme

Vrlo čest slučaj u praksi: trebate pokrenuti jednu ili više svojih makronaredbi u određeno vrijeme ili određenom učestalošću. Na primjer, imate veliko i teško izvješće koje se ažurira pola sata i željeli biste pokrenuti ažuriranje pola sata prije nego što stignete na posao ujutro. Ili imate makronaredbu koja bi trebala automatski slati e-poštu zaposlenicima određenom učestalošću. Ili, kada radite sa zaokretnom tablicom, želite da se ažurira u hodu svakih 10 sekundi, i tako dalje.

Pogledajmo što Excel i Windows imaju mogućnost implementacije ovoga.

Pokretanje makronaredbe na zadanoj frekvenciji

Najlakši način da to učinite je korištenje ugrađene VBA metode Aplikacija.OnTimekoji pokreće navedenu makronaredbu u određeno vrijeme. Shvatimo ovo na praktičnom primjeru.

Otvorite uređivač Visual Basic pomoću istoimenog gumba na kartici razvijač (Programer) ili tipkovni prečac drugo+F11, umetnite novi modul kroz izbornik Umetak – Modul i tamo kopirajte sljedeći kod:

Dim TimeToRun 'globalna varijabla gdje se pohranjuje sljedeće vrijeme izvođenja 'ovo je glavni makro Sub MyMacro() Application.Calculate 'ponovno izračunajte knjigu Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill ćelija A1 sa slučajnom bojom :) Pozovi NextRun 'pokreni NextRun makronaredbu za postavljanje sljedećeg vremena izvođenja End Sub 'ova makronaredba postavlja vrijeme za sljedeće izvođenje glavnog makronaredbe 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 za početak ponavljanja niza Sub Start() Pozovite NextRun End Sub 'makro za zaustavljanje ponavljanja niza Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Idemo shvatiti što je što ovdje.

Prvo, trebamo varijablu koja će pohraniti vrijeme sljedećeg pokretanja našeg makronaredbe – nazvao sam je TimeToRun. Imajte na umu da sadržaj ove varijable mora biti dostupan svim našim sljedećim makronaredbama, tako da je moramo učiniti globalno, tj. deklarirati na samom početku modula prije prvog ispod.

Slijedi naš glavni makro MyMacro, koji će izvršiti glavni zadatak - ponovno izračunati knjigu pomoću metode Primjena.Izračunaj. Da bi bilo jasnije, dodao sam formulu =TDATE() na list u ćeliji A1, koja prikazuje datum i vrijeme - kada se ponovno izračuna, njen sadržaj će se ažurirati pred našim očima (samo uključite prikaz sekundi u ćeliji format). Za dodatnu zabavu, dodao sam makronaredbi za ispunjavanje ćelije A1 nasumično odabranom bojom (kod boje je cijeli broj u rasponu od 0..56, koji se generira 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 makronaredbe MyMacro za ovo novo vrijeme. Naravno, u praksi možete koristiti bilo koje druge vremenske intervale koji su vam potrebni postavljanjem argumenata funkcije VremenskaVrijednost u formatu hh:mm:ss.

I na kraju, samo radi praktičnosti, dodano je više makronaredbi za pokretanje niza. Naslovna i njegovo dovršenje završiti. Posljednji koristi četvrti argument metode za prekid niza. Na vrijeme jednak Lažan.

Ukupno ako pokrenete makronaredbu Naslovna, tada će se cijeli ovaj vrtuljak zavrtjeti, a na listu ćemo vidjeti sljedeću sliku:

Slijed možete zaustaviti pokretanjem makronaredbe završiti. Radi praktičnosti, pomoću naredbe možete dodijeliti tipkovničke prečace objema makronaredbama Makronaredbe – Opcije kartica razvijač (Razvojni programer — Makronaredbe — Opcije).

Pokretanje makronaredbe prema rasporedu

Naravno, sve gore opisano moguće je samo ako imate pokrenut Microsoft Excel i u njemu je otvorena naša datoteka. Sada pogledajmo kompliciraniji slučaj: morate pokrenuti Excel prema zadanom rasporedu, na primjer, svaki dan u 5:00, otvoriti veliko i složeno izvješće u njemu i ažurirati sve veze i upite u njemu kako bi budite spremni do dolaska na posao 🙂

U takvoj situaciji bolje je koristiti Windows Planer – program posebno ugrađen u bilo koju verziju sustava Windows koji može izvoditi određene radnje prema rasporedu. Zapravo, već ga koristite, a da toga niste svjesni, jer vaše računalo redovito provjerava ima li ažuriranja, preuzima nove antivirusne baze podataka, sinkronizira mape u oblaku itd. Sve je to rad planera. Dakle, naš zadatak je postojećim zadacima dodati još jedan koji će pokrenuti Excel i u njemu otvoriti navedenu datoteku. I objesit ćemo naš makro na događaj Radna knjiga_Otvori ovu datoteku – i problem je riješen.

Želim vas odmah upozoriti da rad s Planerom može zahtijevati napredna korisnička prava, pa ako ne možete pronaći dolje opisane naredbe i funkcije na svom radnom računalu u uredu, obratite se svojim informatičkim stručnjacima za pomoć.

Pokretanje planera

Pa pokrenimo Planer. Da biste to učinili, možete:

• Desni klik na gumb početni plan I odaberite računalo za upravljanje (Upravljanje računalom)
• Odaberite na upravljačkoj ploči: Administracija – Planer zadataka (Upravljačka ploča — Administrativni alati — Planer zadataka)
• Odaberite iz glavnog izbornika Start – Pribor – Alati sustava – Planer zadataka
• Pritisnite tipkovni prečac Pobijediti+R, Unesi taskschd.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):

Stvorite zadatak

Za izradu novog zadatka pomoću jednostavnog čarobnjaka korak po korak kliknite na poveznicu Napravite jednostavan zadatak (Stvori osnovni zadatak) na desnoj ploči.

U prvom koraku čarobnjaka unesite naziv i opis zadatka koji želite izraditi:

Kliknite na gumb Sljedeći (Sljedeći) a u sljedećem koraku biramo okidač – učestalost pokretanja ili događaj koji će pokrenuti naš zadatak (npr. paljenje računala):

Ako ste odabrali Svakodnevno (Dnevno), tada ćete u sljedećem koraku morati odabrati određeno vrijeme, datum početka niza i korak (svaki 2. dan, 5. dan itd.):

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

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

u Program ili skripta (Program/skripta) potrebno je unijeti put do Microsoft Excela kao programa tj. direktno do izvršne datoteke Excela. Na različitim računalima s različitim verzijama sustava Windows i Office, ova datoteka može biti u različitim mapama, pa evo nekoliko načina na koje možete saznati njezinu lokaciju:

• Kliknite desnom tipkom miša na ikonu (prečac) kako biste pokrenuli Excel na radnoj površini ili programskoj traci i odaberite naredbu Materijali (Svojstva), a zatim u prozoru koji se otvori kopirajte putanju iz linije Meta:

                       

• Otvorite bilo koju Excel radnu knjigu, a zatim otvorite Task Manager (Upravitelj zadataka) guranje Ctrl+drugo+Od a desnim klikom na liniju Microsoft Excel, odaberite naredbu Materijali (Svojstva). U prozoru koji se otvori možete kopirati putanju, ne zaboravite mu dodati obrnutu kosu crtu i EXCEL.EXE na kraju:

               

• Otvorite Excel, otvorite uređivač Visual Basic tipkovničkim prečacem drugo+F11, otvorena ploča Neposredan kombinacija Ctrl+G, u nju unesite naredbu:

  ? Application.Path

  … i kliknite na ući

  

  Kopirajte dobiveni put, ne zaboravite mu dodati obrnutu kosu crtu i EXCEL.EXE na kraju.

u Dodavanje argumenata (izborno) (Dodajte argumente (nije obavezno)) morate umetnuti punu putanju do knjige s makronaredbom koju želimo otvoriti.

Kad se sve unese, onda kliknite Sljedeći a zatim završiti (Završi). Zadatak treba dodati na opći popis:

Izrađenim zadatkom prikladno je upravljati pomoću gumba s desne strane. Ovdje možete testirati zadatak tako da ga odmah pokrenete (trčanje)bez čekanja navedenog vremena. Možete privremeno deaktivirati zadatak (Onemogući)tako da prestane raditi na određeno vrijeme, kao što je vaš godišnji odmor. Pa, uvijek možete promijeniti parametre (datume, vrijeme, naziv datoteke) putem gumba Materijali (Svojstva).

Dodajte makronaredbu za otvaranje datoteke

Sada ostaje da objesimo u našu knjigu pokretanje makronaredbe koja nam je potrebna na događaj otvaranja datoteke. Da biste to učinili, otvorite knjigu i idite na uređivač Visual Basic pomoću prečaca na tipkovnici drugo+F11 ili gumbe Visual Basic kartica razvijač (Programer). U prozoru koji se otvori u gornjem lijevom kutu potrebno je pronaći našu datoteku na stablu i duplim klikom otvoriti modul Ova knjiga (Ova radna knjiga).

Ako ne vidite ovaj prozor u uređivaču Visual Basica, možete ga otvoriti kroz izbornik Pogled — Istraživač projekta.

U prozoru modula koji se otvori dodajte rukovatelj događajem otvaranja knjige odabirom s padajućih popisa na vrhu Radna bilježnica и Otvoren, odnosno:

Na ekranu bi se trebao pojaviti obrazac postupka. Radna knjiga_Otvori, gdje između redaka Privatni sub и End Sub i trebaš ubaciti one VBA naredbe koje bi se trebale automatski izvršavati kada se otvori ova Excel radna knjiga, kada ju Scheduler otvori po rasporedu. Evo nekoliko korisnih opcija za overclocking:

• ThisWorkbook.RefreshAll – Osvježava sve upite vanjskih podataka, Power Query upite i zaokretne tablice. Najsvestranija opcija. Samo ne zaboravite dopustiti veze s vanjskim podacima prema zadanim postavkama i ažurirati veze putem Datoteka – Opcije – Centar za pouzdanost – Opcije centra za pouzdanost – Vanjski sadržaj, u suprotnom, kada otvorite knjigu, pojavit će se standardno upozorenje i Excel će, ne ažurirajući ništa, čekati vaš blagoslov u obliku klika na gumb Omogući sadržaj (Omogući sadržaj):

  

• ActiveWorkbook.Connections(“Connection_Name”).Osvježi — ažuriranje podataka o vezi Connection_Name.
• Listovi (“List5“).Zaokretne tablice(“Zaokretna tablica1«).PivotCache.Osvježi – ažuriranje jedne zaokretne tablice pod nazivom Zaokretna tablica1 na plahti Sheet5.
• Primjena.Izračunaj – preračunavanje svih otvorenih Excel radnih knjiga.
• Application.CalculateFullRebuild – prisilno ponovno izračunavanje svih formula i ponovna izgradnja svih ovisnosti između ćelija u svim otvorenim radnim knjigama (ekvivalentno ponovnom unosu svih formula).
• Radni listovi (“Izvješće”). Ispis – ispisni list Fotografije.
• Pozovite MyMacro – pokrenite makro pod nazivom MyMacro.
• ThisWorkbook.Save – spremanje trenutne knjige
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – spremite knjigu u mapu D:Arhiva pod imenom Fotografije s datumom i vremenom dodanim uz ime.

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

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

To je sve. Ne zaboravite spremiti svoju radnu knjigu u formatu s omogućenim makronaredbama (xlsm ili xlsb) i možete sigurno zatvoriti Excel i otići kući, ostavljajući računalo uključenim. U određenom trenutku (čak i ako je računalo zaključano), Planer će pokrenuti Excel i otvoriti navedenu datoteku u njemu, a naš makro će izvršiti programirane radnje. A vi ćete uživati ​​u krevetu dok se vaš težak izvještaj automatski preračunava – ljepota! 🙂

• Što su makronaredbe, kako ih koristiti, gdje umetnuti Visual Basic kod u Excelu
• Kako stvoriti vlastiti makro dodatak za Excel
• Kako koristiti osobnu radnu knjigu makronaredbi kao biblioteku za svoje makronaredbe u Excelu