Makro ajaa ajoissa

Hyvin yleinen tapaus käytännössä: sinun on suoritettava yksi tai useampi makrosi tiettyyn aikaan tai tietyllä taajuudella. Sinulla on esimerkiksi suuri ja raskas raportti, joka päivittyy puoli tuntia ja haluat suorittaa päivityksen puoli tuntia ennen töihin saapumistasi aamulla. Tai sinulla on makro, jonka pitäisi automaattisesti lähettää sähköposteja työntekijöille tietyin väliajoin. Tai kun työskentelet Pivot-taulukon kanssa, haluat sen päivittävän lennossa 10 sekunnin välein ja niin edelleen.

Katsotaanpa, mitä Excelillä ja Windowsilla on kyky toteuttaa tämä.

Makron suorittaminen tietyllä taajuudella

Helpoin tapa tehdä tämä on käyttää sisäänrakennettua VBA-menetelmää Application.OnTimeSe, joka suorittaa määritetyn makron määritettynä aikana. Ymmärretään tämä käytännön esimerkillä.

Avaa Visual Basic -editori välilehden samannimisellä painikkeella kehittäjä (Kehittäjä) tai pikanäppäimellä muut+F11, lisää uusi moduuli valikon kautta Insert – moduuli ja kopioi sinne seuraava koodi:

Dim TimeToRun 'maailmanlaajuinen muuttuja, johon seuraava ajoaika on tallennettu 'tämä on päämakro Sub MyMacro() Application.Calculate 'laske kirjan uudelleen Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'täytä solu A1 satunnaisella värillä :) Kutsu NextRun 'ajaa NextRun-makro asettaaksesi seuraavan suoritusajan End Sub 'tämä makro asettaa ajan päämakron seuraavalle ajolle Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'lisää 3 sekuntia nykyiseen aikaan Application.OnTime TimeToRun, "MyMacro" 'ajoita seuraavan ajon End Sub 'makro aloittaaksesi toistosekvenssin Sub Start() Soita NextRun End Sub 'makro lopettaaksesi toistosekvenssin Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Selvitetään mitä tässä on.

Ensinnäkin tarvitsemme muuttujan, joka tallentaa makromme seuraavan ajon ajan – minä kutsuin sitä TimeToRun. Huomaa, että tämän muuttujan sisällön on oltava kaikkien myöhempien makrojemme käytettävissä, joten meidän on tehtävä se maailmanlaajuinen, eli ilmoittaa aivan moduulin alussa ennen ensimmäistä alla.

Seuraavaksi tulee päämakromme OmaMakro, joka suorittaa päätehtävän - laskea kirjan uudelleen menetelmällä Sovellus. Laske. Selvyyden vuoksi lisäsin solun A1 taulukkoon kaavan =TDATE(), joka näyttää päivämäärän ja kellonajan – uudelleen laskettaessa sen sisältö päivittyy silmiemme edessä (laita vain sekuntien näyttö päälle solussa muoto). Lisähuviksi lisäsin makroon myös komennon täyttää solu A1 satunnaisesti valitulla värillä (värikoodi on funktion generoima kokonaisluku alueella 0..56 rnd ja pyöristää ylöspäin kokonaislukufunktioon Int).

Makro NextRun lisää edelliseen arvoon TimeToRun 3 sekuntia lisää ja ajoittaa sitten päämakron seuraavan ajon OmaMakro tälle uudelle ajalle. Käytännössä voit tietysti käyttää mitä tahansa muita tarvitsemiasi aikavälejä asettamalla funktion argumentit TimeValue muodossa tt:mm:ss.

Ja lopuksi, vain mukavuuden vuoksi, lisää sarjan käynnistysmakroja on lisätty. Koti ja sen valmistuminen Suorittaa loppuun. Viimeinen käyttää neljättä metodiargumenttia sekvenssin katkaisemiseen. On aika yhtäläinen Väärä.

Yhteensä, jos suoritat makron Koti, silloin koko karuselli pyörii ja arkilla näkyy seuraava kuva:

Voit pysäyttää sarjan suorittamalla vastaavasti makron Suorittaa loppuun. Mukavuuden vuoksi voit määrittää pikanäppäimet molemmille makroille komennolla Makrot – Asetukset kieleke kehittäjä (Kehittäjä — Makrot — Asetukset).

Makron suorittaminen aikataulun mukaan

Tietenkin kaikki yllä kuvattu on mahdollista vain, jos sinulla on Microsoft Excel käynnissä ja tiedostomme on avoinna siinä. Tarkastellaan nyt monimutkaisempaa tapausta: sinun on suoritettava Excel tietyn aikataulun mukaan, esimerkiksi joka päivä klo 5:00, avattava siinä suuri ja monimutkainen raportti ja päivitettävä kaikki siinä olevat yhteydet ja kyselyt, jotta se olkaa valmiit kun saavumme töihin 🙂

Tällaisessa tilanteessa on parempi käyttää Windowsin ajastin – mihin tahansa Windows-versioon erityisesti sisäänrakennettu ohjelma, joka voi suorittaa tiettyjä toimintoja aikataulussa. Itse asiassa käytät sitä jo tietämättäsi, koska tietokoneesi tarkistaa säännöllisesti päivityksiä, lataa uusia virustorjuntatietokantoja, synkronoi pilvikansioita jne. Kaikki on Schedulerin työtä. Joten tehtävämme on lisätä olemassa oleviin tehtäviin toinen, joka käynnistää Excelin ja avaa määritetyn tiedoston siinä. Ja ripustamme makromme tapahtumaan Työkirja_Avaa tämä tiedosto – ja ongelma on ratkaistu.

Haluan heti varoittaa, että Schedulerin kanssa työskentely saattaa vaatia lisäkäyttöoikeuksia, joten jos et löydä alla kuvattuja komentoja ja toimintoja työtietokoneeltasi toimistossa, ota yhteyttä IT-asiantuntijoihisi saadaksesi apua.

Aikataulun käynnistäminen

Joten aloitetaan Scheduler. Voit tehdä tämän jommallakummalla seuraavista tavoista:

• Napsauta painiketta hiiren oikealla painikkeella Aloita Ja valitse tietokoneen hallinta (Tietokonehallinta)
• Valitse Ohjauspaneelista: Hallinta – Tehtävien ajoitus (Ohjauspaneeli — Hallintatyökalut — Tehtävien ajoitus)
• Valitse päävalikosta Käynnistä – Apuohjelmat – Järjestelmätyökalut – Tehtävien ajoitus
• Paina näppäimistön pikakuvaketta voittaa+R, tulla sisään taskschd.mscja ja paina enter

Seuraavan ikkunan pitäisi ilmestyä näytölle (minulla on englanninkielinen versio, mutta sinulla voi olla myös versio):

Luo tehtävä

Luo uusi tehtävä yksinkertaisen vaiheittaisen ohjatun toiminnon avulla napsauttamalla linkkiä Luo yksinkertainen tehtävä (Luo perustehtävä) oikeassa paneelissa.

Kirjoita ohjatun toiminnon ensimmäisessä vaiheessa luotavan tehtävän nimi ja kuvaus:

Napsauta painiketta seuraava (Seuraava) ja seuraavassa vaiheessa valitsemme triggerin – käynnistystaajuuden tai tapahtuman, joka käynnistää tehtävämme (esimerkiksi tietokoneen käynnistäminen):

Jos valitsit Päivä (Päivittäin), niin seuraavassa vaiheessa sinun on valittava tietty aika, sekvenssin aloituspäivämäärä ja vaihe (joka 2. päivä, 5. päivä jne.):

Seuraava askel on valita toiminto – Suorita ohjelma (Aloita ohjelma):

Ja lopuksi mielenkiintoisin asia on se, mitä tarkalleen on avattava:

In Ohjelma tai käsikirjoitus (Ohjelma/käsikirjoitus) sinun on syötettävä polku Microsoft Exceliin ohjelmana, eli suoraan Excelin suoritettavaan tiedostoon. Eri tietokoneissa, joissa on eri Windows- ja Office-versiot, tämä tiedosto voi olla eri kansioissa, joten tässä on muutamia tapoja selvittää sen sijainti:

• Napsauta hiiren kakkospainikkeella kuvaketta (pikakuvaketta) käynnistääksesi Excel työpöydällä tai tehtäväpalkissa ja valitse komento Tarvikkeet (Ominaisuudet)ja kopioi polku riviltä avautuvassa ikkunassa Kohde:

                       

• Avaa mikä tahansa Excel-työkirja ja avaa sitten Task Manager (Tehtävänhallinta) työntää Ctrl+muut+alkaen ja napsauttamalla riviä hiiren oikealla painikkeella Microsoft Excel, valitse komento Tarvikkeet (Ominaisuudet). Avautuvassa ikkunassa voit kopioida polun, unohtamatta lisätä kenoviivaa siihen ja EXCEL.EXE-tiedoston loppuun:

               

• Avaa Excel, avaa Visual Basic -editori pikanäppäimellä muut+F11, avoin paneeli Välitön yhdistelmä Ctrl+G, kirjoita siihen komento:

  ? Application.Path

  … ja napsauta enter

  

  Kopioi tuloksena oleva polku, unohtamatta lisätä kenoviivaa siihen ja EXCEL.EXE-tiedoston loppuun.

In Lisää argumentteja (valinnainen) (Lisää argumentteja (valinnainen)) sinun on lisättävä koko polku kirjaan makrolla, jonka haluamme avata.

Kun kaikki on syötetty, napsauta seuraava ja sitten Suorittaa loppuun (Viedä loppuun). Tehtävä tulee lisätä yleiseen listaan:

Luotua tehtävää on kätevä hallita oikealla olevilla painikkeilla. Täällä voit testata tehtävää suorittamalla sen välittömästi (juosta)odottamatta määritettyä aikaa. Voit poistaa tehtävän väliaikaisesti käytöstä (Poista käytöstä)niin, että se lakkaa toimimasta joksikin aikaa, kuten lomallesi. No, voit aina muuttaa parametreja (päivämäärät, kellonaika, tiedoston nimi) painikkeella Tarvikkeet (Ominaisuudet).

Lisää makro avataksesi tiedoston

Nyt on vain ripustaa kirjaamme tarvitsemamme makron käynnistäminen tiedoston avaamistapahtumassa. Voit tehdä tämän avaamalla kirjan ja siirtymällä Visual Basic -editoriin pikanäppäimellä muut+F11 tai painikkeita Visual Basic kieleke kehittäjä (Kehittäjä). Vasemmassa yläkulmassa avautuvassa ikkunassa sinun on löydettävä tiedostomme puusta ja avattava moduuli kaksoisnapsauttamalla Tämä kirja (Tämä työkirja).

Jos et näe tätä ikkunaa Visual Basic -editorissa, voit avata sen valikon kautta Näytä — Project Explorer.

Lisää avautuvaan moduuliikkunaan kirjan avaustapahtuman käsittelijä valitsemalla se yläreunan avattavista luetteloista Työkirja и avoinvastaavasti:

Toimenpidemallin pitäisi ilmestyä näytölle. Työkirja_Avaa, missä rivien välissä Private Sub и End Sub ja sinun on lisättävä ne VBA-komennot, jotka pitäisi suorittaa automaattisesti, kun tämä Excel-työkirja avataan, kun ajoitusohjelma avaa sen aikataulun mukaisesti. Tässä on joitain hyödyllisiä vaihtoehtoja ylikellotukseen:

• ThisWorkbook.RefreshAll – Päivittää kaikki ulkoiset tietokyselyt, Power Query -kyselyt ja pivot-taulukot. Monipuolisin vaihtoehto. Älä vain unohda sallia oletusarvoisesti yhteydet ulkoisiin tietoihin ja päivittää linkkejä kautta Tiedosto – Asetukset – Luottamuskeskus – Luottamuskeskuksen asetukset – Ulkoinen sisältö, muuten, kun avaat kirjan, näkyviin tulee vakiovaroitus ja Excel, päivittämättä mitään, odottaa siunaustasi napsautuksen muodossa Ota sisältö käyttöön (Ota sisältö käyttöön):

  

• ActiveWorkbook.Connections("Yhteyden_nimi"). Päivitä — Connection_Name-yhteyden tietojen päivittäminen.
• Sheets("Sheet5").PivotTables("PivotTable1«).PivotCache.Päivitä – yhden nimetyn pivot-taulukon päivittäminen Pivot-taulukko1 arkilla Sheet5.
• Sovellus. Laske – kaikkien avoimien Excel-työkirjojen uudelleenlaskenta.
• Application.CalculateFullRebuild – kaikkien kaavojen pakotettu uudelleenlaskenta ja kaikkien solujen välisten riippuvuuksien uudelleenrakentaminen kaikissa avoimissa työkirjoissa (vastaa kaikkien kaavojen syöttämistä uudelleen).
• Työarkit ("Raportti"). Tulosta – tulostusarkki Valokuvat.
• Soita MyMacrolle – Suorita makro nimeltä OmaMakro.
• This Workbook.Save – tallenna nykyinen kirja
  
• ThisWorkbooks.SaveAs "D:ArchiveReport" & Replace(Now, ":", "-") & ".xlsx" – tallenna kirja kansioon D: Arkisto nimellä Valokuvat päivämäärä ja aika on liitetty nimeen.

Jos haluat, että makro suoritetaan vain, kun Ajoitus avaa tiedoston klo 5, eikä aina, kun käyttäjä avaa työkirjan työpäivän aikana, on järkevää lisätä aikatarkistus esimerkiksi:

Jos Muoto(nyt, "tt:mm") = "05:00" sitten ThisWorkbook.RefreshAll  

Siinä kaikki. Älä unohda tallentaa työkirjaasi makroja tukevassa muodossa (xlsm tai xlsb), niin voit turvallisesti sulkea Excelin ja palata kotiin jättäen tietokoneen päälle. Tietyllä hetkellä (vaikka tietokone olisi lukittu) Scheduler käynnistää Excelin ja avaa siinä määritetyn tiedoston, ja makromme suorittaa ohjelmoidut toiminnot. Ja nautit sängyssä, kun raskas raporttisi lasketaan automaattisesti uudelleen – kauneutta! 🙂

• Mitä makrot ovat, miten niitä käytetään, mihin Visual Basic -koodi lisätään Excelissä
• Kuinka luoda oma makro-apuohjelma Excelille
• Oman makron työkirjan käyttäminen makrojen kirjastona Excelissä