Pravočasno izvajanje makra

Zelo pogost primer v praksi: enega ali več svojih makrov morate zagnati ob določenem času ali ob določeni frekvenci. Na primer, imate veliko in težko poročilo, ki se posodablja pol ure, in bi radi zagnali posodobitev pol ure, preden pridete zjutraj v službo. Ali pa imate makro, ki bi moral samodejno pošiljati e-pošto zaposlenim ob določeni pogostosti. Ali pa, ko delate z vrtilno tabelo, želite, da se sproti posodablja vsakih 10 sekund in tako naprej.

Poglejmo, kaj imata Excel in Windows možnost za implementacijo tega.

Izvajanje makra pri določeni frekvenci

Najlažji način za to je uporaba vgrajene metode VBA Aplikacija.OnTimeAn, ki zažene navedeni makro ob določenem času. Razumejmo to s praktičnim primerom.

Odprite urejevalnik Visual Basic z istoimenskim gumbom na zavihku razvijalec (Razvijalec) ali bližnjico na tipkovnici druga+F11, vstavite nov modul skozi meni Vstavi – Modul in tja kopirajte naslednjo kodo:

Dim TimeToRun 'globalna spremenljivka, kjer je shranjen naslednji čas izvajanja 'to je glavni makro Sub MyMacro() Application.Calculate 'ponovni izračun knjige Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill celica A1 z naključno barvo :) Pokličite NextRun 'zaženite makro NextRun, da nastavite čas naslednjega izvajanja End Sub 'ta makro nastavi čas za naslednji zagon glavnega makra Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'dodaj 3 sekunde trenutnemu času Application.OnTime TimeToRun, "MyMacro" 'načrtuj naslednji zagon End Sub 'makro za začetek ponavljajočega se zaporedja Sub Start() Pokliči NextRun End Sub 'makro za ustavitev ponavljajočega se zaporedja Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Ugotovimo, kaj je kaj tukaj.

Najprej potrebujemo spremenljivko, ki bo shranila čas naslednjega zagona našega makra – poklical sem jo TimeToRun. Upoštevajte, da mora biti vsebina te spremenljivke na voljo vsem našim naslednjim makrom, zato jo moramo narediti globalna, tj. deklariraj na samem začetku modula pred prvim spodaj.

Sledi naš glavni makro MojMakro, ki bo opravil glavno nalogo – preračunati knjigo z metodo Aplikacija. Izračunaj. Da bi bilo bolj jasno, sem na list v celici A1 dodal formulo =TDATE(), ki prikazuje datum in uro – ob ponovnem izračunu se bo njena vsebina posodobila tik pred našimi očmi (samo vklopite prikaz sekund v celici format). Za dodatno zabavo sem makru dodal tudi ukaz za zapolnitev celice A1 z naključno izbrano barvo (barvna koda je celo število v območju 0..56, ki jo generira funkcija Rnd in zaokroži navzgor na celoštevilsko funkcijo Int).

Makro NextRun doda prejšnji vrednosti TimeToRun še 3 sekunde in nato načrtuje naslednji zagon glavnega makra MojMakro za ta novi čas. Seveda lahko v praksi uporabite katere koli druge časovne intervale, ki jih potrebujete, tako da nastavite argumente funkcije TimeValue v obliki hh:mm:ss.

In končno, samo zaradi udobja je bilo dodanih več makrov za zagon zaporedja. Domov in njegovo dokončanje Konec. Zadnji uporablja četrti argument metode za prekinitev zaporedja. Pravočasno enako False.

Skupaj, če zaženete makro Domov, potem se bo ves ta vrtiljak zavrtel in na listu bomo videli naslednjo sliko:

Zaporedje lahko ustavite tako, da zaženete makro Konec. Za udobje lahko z ukazom dodelite bližnjice na tipkovnici obema makroma Makri – možnosti tab razvijalec (Razvijalec — Makri — Možnosti).

Izvajanje makra po urniku

Vse zgoraj opisano je seveda možno le, če imate zagnan Microsoft Excel in je v njem odprta naša datoteka. Zdaj pa poglejmo bolj zapleten primer: Excel morate zagnati po danem urniku, na primer vsak dan ob 5. uri, v njem odpreti veliko in zapleteno poročilo ter posodobiti vse povezave in poizvedbe v njem, tako da bo bodite pripravljeni, ko pridemo v službo 🙂

V takšni situaciji je bolje uporabiti Windows Scheduler – program, posebej vgrajen v katero koli različico sistema Windows, ki lahko izvede določena dejanja po urniku. Pravzaprav ga že uporabljate, ne da bi vedeli, saj vaš računalnik redno preverja, ali so na voljo posodobitve, prenaša nove protivirusne zbirke podatkov, sinhronizira mape v oblaku itd. Vse to je delo razporejevalnika. Naša naloga je torej, da obstoječim nalogam dodamo še eno, ki bo zagnala Excel in v njem odprla navedeno datoteko. In na dogodek bomo obesili naš makro Delovni zvezek_Odpri to datoteko – in težava je rešena.

Takoj vas želim opozoriti, da lahko za delo z razporejevalnikom potrebujete napredne uporabniške pravice, zato, če na svojem službenem računalniku v pisarni ne najdete spodaj opisanih ukazov in funkcij, se za pomoč obrnite na svojega IT strokovnjaka.

Zagon razporejevalnika

Zaženimo torej razporejevalnik. To lahko storite tako:

• Desni klik na gumb Začetek In izberite računalniško upravljanje (Upravljanje z računalnikom)
• Na nadzorni plošči izberite: Administracija – Načrtovalnik opravil (Nadzorna plošča — Administrativna orodja — Razporejevalnik opravil)
• Izberite v glavnem meniju Start – Pripomočki – Sistemska orodja – Načrtovalnik opravil
• Pritisnite bližnjico na tipkovnici Win+R, vnesite taskschd.msc in pritisnite Vnesite

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

Ustvari nalogo

Če želite ustvariti novo opravilo s preprostim čarovnikom po korakih, kliknite povezavo Ustvari preprosto nalogo (Ustvari osnovno nalogo) na desni plošči.

V prvem koraku čarovnika vnesite ime in opis naloge, ki jo želite ustvariti:

Kliknite na gumb Naslednji (Naslednji) in v naslednjem koraku izberemo sprožilec – frekvenco zagona ali dogodek, ki bo zagnal našo nalogo (na primer vklop računalnika):

Če ste se odločili dnevni (Dnevno), potem boste morali v naslednjem koraku izbrati določen čas, začetni datum zaporedja in korak (vsak 2. dan, 5. dan itd.):

Naslednji korak je izbira dejanja – Zaženite program (Zaženite program):

In končno, najbolj zanimivo je, kaj točno je treba odpreti:

v Program ali skripta (Program/skripta) vnesti morate pot do Microsoft Excela kot programa, torej neposredno do izvršljive datoteke Excel. V različnih računalnikih z različnimi različicami sistema Windows in Office je lahko ta datoteka v različnih mapah, zato je tukaj nekaj načinov, kako ugotoviti njeno lokacijo:

• Z desnim klikom na ikono (bližnjico) zaženemo Excel na namizju ali v opravilni vrstici in izberemo ukaz materiali (Lastnosti), nato pa v oknu, ki se odpre, kopirajte pot iz vrstice ciljna:

                       

• Odprite kateri koli Excelov delovni zvezek in nato odprite Task Manager (Upravitelj opravil) potiska Ctrl+druga+od in z desnim klikom na vrstico Microsoft Excel, izberite ukaz materiali (Lastnosti). V oknu, ki se odpre, lahko kopirate pot, ne pozabite mu dodati poševnice nazaj in na koncu EXCEL.EXE:

               

• Odprite Excel, odprite urejevalnik Visual Basic z bližnjico na tipkovnici druga+F11, odprta plošča Takojšen kombinacijo Ctrl+Gvanj vnesite ukaz:

  ? Application.Path

  … in kliknite Vnesite

  

  Kopirajte nastalo pot, ne pozabite mu dodati poševnice nazaj in na koncu EXCEL.EXE.

v Dodaj argumente (neobvezno) (Dodajte argumente (neobvezno)) vstaviti morate celotno pot do knjige z makrom, ki ga želimo odpreti.

Ko je vse vneseno, kliknite Naslednji in nato Konec (konč). Nalogo je treba dodati na splošni seznam:

Ustvarjeno nalogo je priročno upravljati z gumbi na desni. Tukaj lahko preizkusite nalogo tako, da jo takoj zaženete (teči)brez čakanja na določen čas. Opravilo lahko začasno deaktivirate (Onemogoči)tako da preneha delovati za določeno obdobje, kot je vaš dopust. No, parametre (datume, uro, ime datoteke) lahko vedno spremenite prek gumba materiali (Lastnosti).

Dodajte makro, da odprete datoteko

Zdaj moramo v naši knjigi obesiti zagon makra, ki ga potrebujemo, ob dogodku odprtja datoteke. Če želite to narediti, odprite knjigo in pojdite v urejevalnik Visual Basic z bližnjico na tipkovnici druga+F11 ali gumbi Visual Basic tab razvijalec (Razvijalec). V oknu, ki se odpre v zgornjem levem kotu, morate najti našo datoteko na drevesu in dvoklikniti, da odprete modul Ta knjiga (Ta delovni zvezek).

Če tega okna ne vidite v urejevalniku Visual Basic, ga lahko odprete v meniju Pogled — Raziskovalec projektov.

V oknu modula, ki se odpre, dodajte obravnavo dogodka odprte knjige tako, da ga izberete s spustnih seznamov na vrhu Delovni zvezek и Odprtooziroma:

Na zaslonu se mora prikazati predloga postopka. Delovni zvezek_Odpri, kjer med vrsticami Zasebni Sub и End Sub in vstaviti morate tiste ukaze VBA, ki naj se samodejno izvedejo, ko se ta Excelov delovni zvezek odpre, ko ga razporejevalnik odpre po urniku. Tukaj je nekaj uporabnih možnosti za overclocking:

• Ta delovni zvezek.RefreshAll – Osveži vse zunanje podatkovne poizvedbe, poizvedbe Power Query in vrtilne tabele. Najbolj vsestranska možnost. Samo ne pozabite privzeto dovoliti povezav z zunanjimi podatki in posodobiti povezav prek Datoteka – Možnosti – Središče zaupanja – Možnosti središča zaupanja – Zunanja vsebina, v nasprotnem primeru, ko odprete knjigo, se prikaže standardno opozorilo in Excel, ne da bi karkoli posodobil, bo čakal na vaš blagoslov v obliki klika na gumb Omogoči vsebino (Omogoči vsebino):

  

• ActiveWorkbook.Connections(“Ime_povezave”).Osveži — posodabljanje podatkov o povezavi Connection_Name.
• Listi (»List5“).Vrtilne tabele(“Vrtilna tabela1«).PivotCache.Osveži – posodabljanje ene vrtilne tabele z imenom Vrtilna tabela1 na listu Sheet5.
• Aplikacija. Izračunaj – preračun vseh odprtih Excelovih delovnih zvezkov.
• Application.CalculateFullRebuild – prisilno ponovno izračunavanje vseh formul in ponovna izgradnja vseh odvisnosti med celicami v vseh odprtih delovnih zvezkih (enakovredno ponovnemu vnosu vseh formul).
• Delovni listi (“Poročilo”). Izpis – tiskalni list slike.
• Pokličite MyMacro – zaženite makro z imenom MojMakro.
• Ta delovni zvezek.Shrani – shrani trenutno knjigo
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – shranite knjigo v mapo D:Arhiv pod imenom slike z datumom in uro, ki sta dodana imenu.

Če želite, da se makro izvede le, ko datoteko odpre razporejevalnik ob 5:00 zjutraj in ne vsakič, ko uporabnik odpre delovni zvezek med delovnikom, je smiselno dodati časovno preverjanje, npr.

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

To je vse. Ne pozabite shraniti svojega delovnega zvezka v formatu, ki podpira makro (xlsm ali xlsb), in lahko varno zaprete Excel ter odidete domov in pustite računalnik prižgan. V določenem trenutku (tudi če je računalnik zaklenjen) bo planer zagnal Excel in v njem odprl navedeno datoteko, naš makro pa bo izvedel programirana dejanja. In uživali boste v postelji, medtem ko se bo vaše težko poročilo samodejno preračunavalo – lepota! 🙂

• Kaj so makri, kako jih uporabljati, kam vstaviti Visual Basic kodo v Excelu
• Kako ustvariti lasten makro dodatek za Excel
• Kako uporabljati osebni delovni zvezek za makre kot knjižnico za svoje makre v Excelu