Kuthamanga macro pa nthawi

Chochitika chodziwika bwino: muyenera kuyendetsa macros anu amodzi kapena angapo panthawi inayake kapena pafupipafupi. Mwachitsanzo, muli ndi lipoti lalikulu ndi lolemetsa lomwe limasintha theka la ola ndipo mukufuna kuyendetsa zosinthika theka la ola musanakafike kuntchito m'mawa. Kapena muli ndi macro omwe amayenera kutumiza maimelo kwa antchito pafupipafupi. Kapena, pogwira ntchito ndi PivotTable, mukufuna kuti isinthe pa ntchentche masekondi 10 aliwonse, ndi zina zotero.

Tiyeni tiwone zomwe Excel ndi Windows zili ndi kuthekera kogwiritsa ntchito izi.

Kuthamanga kwa macro pafupipafupi

Njira yosavuta yochitira izi ndikugwiritsa ntchito njira ya VBA yomangidwa Ntchito.OnTimeAn yomwe imayendetsa ma macro odziwika panthawi yodziwika. Tiyeni timvetse izi ndi chitsanzo chothandiza.

Tsegulani Visual Basic mkonzi ndi batani la dzina lomwelo pa tabu Woyambitsa (Wolemba Mapulogalamu) kapena njira yachidule ya kiyibodi alt+F11, ikani gawo latsopano kudzera pa menyu Ikani - Module ndi kukopera khodi ili:

Dim TimeToRun 'kusintha kwapadziko lonse komwe nthawi yotsatira idzasungidwa 'iyi ndiye pulogalamu yayikulu kwambiri ya Sub MyMacro().Weretsani 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill cell A1 yokhala ndi mtundu wachisawawa :) Imbani NextRun 'thamangani NextRun macro kuti mukhazikitse nthawi yotsatira End Sub 'macro iyi imakhazikitsa nthawi yoyambira macro Sub NextRun() TimeToRun = Tsopano + TimeValue("00: 00:03") 'onjezani masekondi 3 ku nthawi yapano Application.OnTime TimeToRun, "MyMacro"' konzekerani ulendo wotsatira End Sub 'macro kuti muyambe kubwerezabwereza Sub Start() Imbani NextRun End Sub 'macro kuti muyimitse kubwereza kobwereza. Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Tiyeni tione chomwe chiri apa.

Choyamba, timafunikira chosinthika chomwe chidzasungira nthawi yotsatila ya macro athu - ndidayitcha TimeToRun. Chonde dziwani kuti zomwe zili mumitundu iyi ziyenera kupezeka kwa ma macros athu onse, chifukwa chake tiyenera kuzipanga. padziko lonse, mwachitsanzo, lengezani kumayambiriro kwa gawoli lisanayambike loyamba Sub.

Chotsatira chikubwera chachikulu chathu chachikulu MyMacro, yomwe idzachita ntchito yaikulu - kuwerengeranso bukuli pogwiritsa ntchito njira Kugwiritsa Ntchito.Kuwerengera. Kuti zimveke bwino, ndidawonjezera formula =TDATE() pa pepala mu cell A1, yomwe imawonetsa tsiku ndi nthawi - ikawerengedwanso, zomwe zili mkati mwake zidzasinthidwa pamaso pathu (ingoyatsa chiwonetsero cha masekondi mu cell. mtundu). Kuti ndisangalale kwambiri, ndinawonjezeranso ku macro lamulo lodzaza selo A1 ndi mtundu wosankhidwa mwachisawawa (mtundu wamtundu ndi chiwerengero cha 0..56, chomwe chimapangidwa ndi ntchitoyi. Lowani ndi kuzungulira mpaka ku ntchito yonse Int).

Macro NextRun imawonjezera pamtengo wam'mbuyomu TimeToRun 3 masekondi ena ndikukhazikitsanso kuthamanga kotsatira kwa main macro MyMacro kwa nthawi yatsopanoyi. Zachidziwikire, pochita, mutha kugwiritsa ntchito nthawi ina iliyonse yomwe mungafune pokhazikitsa mikangano yantchito TimeValue mumtundu hh:mm:ss.

Ndipo potsiriza, kuti zitheke, ma macro owonjezera otsatizana awonjezedwa. Kunyumba ndi kumaliza kwake chitsiriziro. Womaliza amagwiritsa ntchito njira yachinayi mkangano kuti aswe ndandanda. Panthawi yake ofanana chonyenga.

Total ngati muthamanga macro Kunyumba, ndiye carousel yonseyi idzazungulira, ndipo tiwona chithunzi chotsatira pa pepala:

Mutha kuyimitsa kutsatana ndikuthamanga, motsatana, macro chitsiriziro. Kuti zitheke, mutha kugawa njira zazifupi za kiyibodi ku ma macro onse pogwiritsa ntchito lamulo Macros - Zosankha tsamba Woyambitsa (Wopanga - Macros - Zosankha).

Kuthamanga macro pa ndandanda

Zachidziwikire, zonse zomwe zafotokozedwa pamwambapa ndizotheka pokhapokha ngati muli ndi Microsoft Excel ikuyenda ndipo fayilo yathu imatsegulidwa. Tsopano tiyeni tiwone vuto lovuta kwambiri: muyenera kuyendetsa Excel molingana ndi ndandanda yomwe mwapatsidwa, mwachitsanzo, tsiku lililonse pa 5:00, tsegulani lipoti lalikulu komanso lovuta momwemo ndikusintha maulumikizidwe onse ndi mafunso momwemo kuti zitheke. khalani okonzeka tikafika kuntchito 🙂

Zikatero, ndi bwino kugwiritsa ntchito Windows scheduler - pulogalamu yopangidwa mwapadera mu mtundu uliwonse wa Windows womwe ungathe kuchitapo kanthu pandandanda. M'malo mwake, mukugwiritsa ntchito kale osadziwa, chifukwa PC yanu nthawi zonse imayang'ana zosintha, kukopera zolemba zatsopano zotsutsana ndi kachilomboka, kugwirizanitsa zikwatu zamtambo, ndi zina zonse. Chifukwa chake ntchito yathu ndikuwonjezera ku ntchito zomwe zilipo ina yomwe idzatsegule Excel ndikutsegula fayilo yomwe yatchulidwamo. Ndipo tidzapachika macro athu pamwambowu Workbook_Open fayiloyi - ndipo vutoli lathetsedwa.

Ndikufuna kukuchenjezani nthawi yomweyo kuti kugwira ntchito ndi Scheduler kungafunike ufulu wapamwamba wogwiritsa ntchito, kotero ngati simungapeze malamulo ndi ntchito zomwe zafotokozedwa pansipa pakompyuta yanu yantchito muofesi, funsani akatswiri anu a IT kuti akuthandizeni.

Kukhazikitsa Scheduler

Ndiye tiyeni tiyambe Scheduler. Kuti muchite izi, mungathe:

• Dinani kumanja pa batani Start ndi kusankha Kusintha kwa Pakompyuta (Kasamalidwe ka makompyuta)
• Sankhani mu Control Panel: Administration - Task Scheduler (Gulu Lowongolera - Zida Zoyang'anira - Task Scheduler)
• Sankhani kuchokera ku menyu yayikulu Yambani - Chalk - Zida Zadongosolo - Task Scheduler
• Dinani njira yomasulira Win+R, lowani mayakhalin.msc Ndi kukanikiza Lowani

Zenera lotsatirali liyenera kuwonekera pazenera (ndili ndi mtundu wa Chingerezi, koma muthanso kukhala nawo):

Pangani ntchito

Kuti mupange ntchito yatsopano pogwiritsa ntchito wizard yosavuta ya sitepe ndi sitepe, dinani ulalo Pangani ntchito yosavuta (Pangani Basic Task) mu gulu lamanja.

Pa sitepe yoyamba ya wizard, lowetsani dzina ndi malongosoledwe a ntchito yomwe iyenera kupangidwa:

Dinani batani Ena (Ena) ndipo mu sitepe yotsatira timasankha choyambitsa - ma frequency oyambitsa kapena chochitika chomwe chidzayambitse ntchito yathu (mwachitsanzo, kuyatsa kompyuta):

Ngati mwasankha Daily (Tsiku lililonse), ndiye mu sitepe yotsatira muyenera kusankha nthawi yeniyeni, kuyambira tsiku lotsatizana ndi sitepe (tsiku lililonse la 2, tsiku la 5, ndi zina zotero):

Chotsatira ndikusankha zochita - Kuthamangitsani pulogalamuyi (Yambitsani pulogalamu):

Ndipo potsiriza, chinthu chochititsa chidwi kwambiri ndi chomwe chiyenera kutsegulidwa:

Mu Pulogalamu kapena script (Pulogalamu/script) muyenera kulowa njira yopita ku Microsoft Excel ngati pulogalamu, mwachitsanzo, ku Excel executable. Pamakompyuta osiyanasiyana okhala ndi mitundu yosiyanasiyana ya Windows ndi Office, fayiloyi ikhoza kukhala m'mafoda osiyanasiyana, ndiye nazi njira zingapo zomwe mungadziwire komwe ili:

• Dinani kumanja pa chithunzi (chidule) kuti mutsegule Excel pa desktop kapena pa taskbar ndikusankha lamulo zipangizo (Katundu), ndiyeno pazenera lomwe limatsegulidwa, tengerani njira kuchokera pamzerewu chandamale:

                       

• Tsegulani buku lililonse la Excel, kenako tsegulani Task Manager (Task Manager) kukankhira Ctrl+alt+kuchokera ndikudina kumanja pamzerewu Microsoft Excel, sankhani lamulo zipangizo (Katundu). Pawindo lomwe limatsegulidwa, mutha kukopera njira, osayiwala kuwonjezera kubwerera kumbuyo ndi EXCEL.EXE kumapeto:

               

• Tsegulani Excel, tsegulani Visual Basic mkonzi ndi njira yachidule ya kiyibodi alt+F11, gulu lotseguka mwamsanga kuphatikiza kwa Ctrl+G, lowetsani lamulo mmenemo:

  ? Ntchito.Njira

  …ndipo dinani Lowani

  

  Koperani njira yotsatila, osayiwala kuwonjezera kubwerera kumbuyo ndi EXCEL.EXE kumapeto.

Mu Onjezani mikangano (posankha) (Onjezani mikangano (posankha) muyenera kuyika njira yonse yopita ku bukhu ndi macro omwe tikufuna kutsegula.

Zonse zikalowa, dinani Ena Kenako chitsiriziro (Malizani). Ntchitoyi iyenera kuwonjezeredwa pamndandanda wamba:

Ndikosavuta kuyendetsa ntchito yomwe idapangidwa pogwiritsa ntchito mabatani omwe ali kumanja. Apa mutha kuyesa ntchitoyo poyiyendetsa nthawi yomweyo (thamanga)popanda kuyembekezera nthawi yodziwika. Mutha kuyimitsa ntchito kwakanthawi (Letsani)kotero kuti imasiya kuthamanga kwa nthawi, monga tchuthi chanu. Chabwino, mutha kusintha magawo (masiku, nthawi, dzina lafayilo) nthawi zonse kudzera pa batani zipangizo (Katundu).

Onjezani macro kuti mutsegule fayilo

Tsopano zatsala m'buku lathu kukhazikitsidwa kwa macro omwe tikufuna pamwambo wotsegulira mafayilo. Kuti muchite izi, tsegulani bukulo ndikupita ku Visual Basic mkonzi pogwiritsa ntchito njira yachidule ya kiyibodi alt+F11 kapena mabatani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu). Pazenera lomwe limatsegula pakona yakumanzere yakumanzere, muyenera kupeza fayilo yathu pamtengo ndikudina kawiri kuti mutsegule gawo. Bukhu ili (Buku la Ntchito).

Ngati simukuwona zenera ili mu Visual Basic mkonzi, ndiye kuti mutha kutsegula kudzera pa menyu Onani - Project Explorer.

Pazenera la gawo lomwe limatsegulidwa, onjezani buku lotsegulira zochitika posankha kuchokera pamndandanda wotsikira pamwamba. Buku la Ntchito и Openmotsatana:

template ya ndondomeko iyenera kuwonekera pazenera. Workbook_Open, pomwe pakati pa mizere Private Sub и mapeto Sub ndipo muyenera kuyika malamulo a VBA aja omwe ayenera kuchitidwa okha buku la Excel ili litatsegulidwa, Wokonza mapulani akatsegula molingana ndi ndandanda. Nazi njira zina zothandiza pa overclocking:

• Buku Lantchitoli.RefreshAll - Imatsitsimutsanso mafunso onse akunja, mafunso a Power Query, ndi PivotTables. Njira yosunthika kwambiri. Osayiwala kulola kulumikizana ndi data yakunja mwachisawawa ndikusintha maulalo kudzera Fayilo - Zosankha - Trust Center - Trust Center Zosankha - Zakunja Zakunja, apo ayi, mukatsegula bukhulo, chenjezo lokhazikika lidzawonekera ndipo Excel, popanda kukonzanso chirichonse, idzadikira madalitso anu mwa kuwonekera pa batani. Yambitsani zomwe zili (Yambitsani zomwe zili):

  

• ActiveWorkbook.Connections("Connection_Name").Tsitsani - Kusintha kwa data pa Connection_Name.
• Mapepala (“Mapepala5").PivotTables("PivotTable1«).PivotCache.Refresh - kukonzanso tebulo la pivot limodzi lotchedwa PivotTable1 pa pepala Sheet5.
• Kugwiritsa Ntchito.Kuwerengera - kuwerengeranso mabuku onse otseguka a Excel.
• Kugwiritsa.CalculateFullRebuild - kuwerengeranso mokakamiza ma formula onse ndikumanganso zodalira zonse pakati pa maselo m'mabuku onse otseguka (ofanana ndi kulowetsanso mafomu onse).
• Mapepala Ogwirira Ntchito("Lipoti").Sindikizani - pepala losindikizidwa Photos.
• Imbani MyMacro - yendetsani macro dzina lake MyMacro.
• Bukuli.Sungani - sungani buku lamakono
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Tsopano, “:”, “-“) & “.xlsx” - sungani bukulo mufoda D: Sungani pansi pa dzina Photos ndi tsiku ndi nthawi zowonjezeredwa ku dzina.

Ngati mukufuna kuti macro aphedwe pokhapokha fayilo ikatsegulidwa ndi Scheduler pa 5:00 am, ndipo osati nthawi zonse wogwiritsa ntchito akatsegula bukhu lantchito patsiku logwira ntchito, ndiye kuti ndizomveka kuwonjezera cheke, mwachitsanzo:

Ngati Format(Tsopano, "hh:mm") = "05:00" Ndiye ThisWorkbook.RefreshAll  

Ndizomwezo. Musaiwale kusunga buku lanu lantchito mumtundu wothandizidwa ndi macro (xlsm kapena xlsb) ndipo mutha kutseka Excel ndikupita kunyumba, kusiya kompyuta yanu. Panthawi ina (ngakhale PC itatsekedwa), Scheduler idzayambitsa Excel ndikutsegula fayilo yomwe yatchulidwamo, ndipo macro athu adzachita zomwe adazipanga. Ndipo mudzasangalala pabedi pomwe lipoti lanu lolemera limangowerengedwanso - kukongola! 🙂

• Kodi macros ndi chiyani, momwe mungawagwiritsire ntchito, momwe mungayikitsire Visual Basic code mu Excel
• Momwe mungapangire zowonjezera zanu zazikulu za Excel
• Momwe mungagwiritsire ntchito Personal Macro Workbook ngati laibulale ya macros anu mu Excel