Kumhanyisa macro nenguva

Mhosva yakajairika mukuita: iwe unofanirwa kumhanya imwe kana anopfuura macros ako panguva yakapihwa kana pane imwe frequency. Semuenzaniso, une mushumo wakakura uye unorema unovandudza hafu yeawa uye unoda kumhanyisa hafu yeawa usati wasvika kubasa mangwanani. Kana iwe une macro iyo inofanirwa kutumira otomatiki maemail kune vashandi pane yakatarwa frequency. Kana, kana uchishanda nePivotTable, iwe unoda kuti ivandudze pane nhunzi masekonzi gumi ega ega, zvichingodaro.

Ngatitarisei kune izvo Excel neWindows vane kugona kuita izvi.

Kumhanyisa macro pane yakapihwa frequency

Nzira iri nyore yekuita izvi ndeye kushandisa yakavakirwa-mukati VBA nzira Application.OnTimeIyo inomhanyisa iyo yakatsanangurwa macro panguva yakatarwa. Ngatinzwisise izvi nemuenzaniso unoshanda.

Vhura iyo Visual Basic edhita nebhatani rezita rimwe chete pane tab yokuvaka (Mugadziri) kana nzira yekudimbudzira keyboard alt+F11, isa module nyowani kuburikidza nemenyu Pinza – Module uye kopira kodhi inotevera ipapo:

Dim TimeToRun 'global variable panochengetwa nguva inotevera 'iyi ndiyo main macro Sub MyMacro() Application.Calculate' recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56)' fill sero A1 ine ruvara rusina kurongeka :) Fonera NextRun 'mhanya iyo NextRun macro kuseta inotevera yekumhanya nguva End Sub 'iyi macro inoisa nguva yekumhanya kunotevera kweiyo main macro Sub NextRun() TimeToRun = Zvino + NguvaValue("00: 00:03") 'wedzera masekonzi matatu kune yazvino nguva Application.OnTime TimeToRun, "MyMacro" 'schedule the next run End Sub 'macro kutanga kudzokorora kutevedzana Sub Start() Call NextRun End Sub 'macro kumisa kutevedzana kwekudzokorora. Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Ngatione kuti chii apa.

Chekutanga, isu tinoda shanduko inozochengeta nguva yeinotevera kumhanya kwema macro yedu - ndakaidaidza TimeToRun. Ndokumbira utarise kuti zviri mukati mekusiyana uku zvinofanirwa kuwanikwa kune ese anotevera macros, saka isu tinofanirwa kuzviita. zvepasi rose, kureva zivisa pakutanga chaiko kwemodule isati yatanga Sub.

Inotevera inouya yedu huru macro MyMacro, iyo ichaita basa guru - kuverenga zvakare bhuku uchishandisa nzira Kushandisa.Karukureta. Kuti zvijeke, ndakawedzera fomula = TDATE () kune pepa muchitokisi A1, iro rinoratidza zuva nenguva - kana ikaverengerwa, zvirimo zvichagadziridzwa pamberi pemeso edu (ingo vhura kuratidzwa kwemasekondi muchitokisi. format). Kuti uwedzere kunakidzwa, ini ndakawedzera kune macro murairo wekuzadza sero A1 nemuvara wakasarudzika wakasarudzika (iyo kodhi yeruvara ndeye integer muhuwandu 0..56, iyo inogadzirwa nebasa Rnd uye inotenderedza kusvika kune yakazara basa Int).

Macro NextRun inowedzera kune yakapfuura kukosha TimeToRun 3 mamwe masekondi uye wozoronga inotevera kumhanya kweiyo main macro MyMacro kwenguva itsva iyi. Ehe, mukuita, unogona kushandisa chero imwe nguva yenguva yaunoda nekuisa iyo basa nharo TimeValue muchimiro hh:mm:ss.

Uye pakupedzisira, kungoitira nyore, kutevedzana kwekuvhura macros kwakawedzerwa. Home uye kupera kwayo pedza. Yekupedzisira inoshandisa nzira yechina nharo kuputsa kutevedzana. Nenguva akaenzana venhema.

Total kana uchimhanyisa macro Home, ipapo iyi carousel yese ichatenderera, uye isu tichaona unotevera mufananidzo pane pepa:

Iwe unogona kumisa kutevedzana nekumhanya, zvichiteerana, iyo macro pedza. Kuti zvive nyore, unogona kugovera mapfupi ekhibhodi kune ese macros uchishandisa rairo Macros - Sarudzo Tab yokuvaka (Mugadziri - Macros - Sarudzo).

Kumhanyisa macro pane chirongwa

Ehe, zvese zvakatsanangurwa pamusoro zvinogoneka chete kana uine Microsoft Excel inomhanya uye faira redu rakavhurika mariri. Iye zvino ngatitarisei imwe nyaya yakaoma: unofanirwa kumhanya Excel maererano nehurongwa hwakapihwa, semuenzaniso, zuva rega rega na5:00, vhura mushumo wakakura uye wakaoma mukati mayo uye uvandudze zvese zvinongedzo uye mibvunzo mairi kuitira kuti iite. gadzirira panguva yatinosvika kubasa 🙂

Mumamiriro ezvinhu akadaro, zviri nani kushandisa Windows scheduler - chirongwa chakanyatso kuvakwa mune chero vhezheni yeWindows iyo inogona kuita zvakatemwa zviito pahurongwa. Kutaura zvazviri, uri kutoishandisa usingazvizivi, nokuti PC yako inogara ichitarisa kuti inovandudzwa, inodhawunirodha itsva anti-virus databases, synchronizes cloud folders, etc. Zvose ibasa reMurongwa. Saka basa redu nderekuwedzera kumabasa aripo rimwe rinozovhura Excel uye kuvhura iyo yakatsanangurwa faira mairi. Uye isu ticharembedza macro yedu pane chiitiko Bhuku rekushanda_Vhura iyi faira - uye dambudziko rinogadziriswa.

Ndinoda kukuyambira pakarepo kuti kushanda neMugadziri kungada kodzero dzevashandisi vepamusoro, saka kana usingakwanise kuwana mirairo nemabasa anotsanangurwa pazasi pakombuta yako yebasa muhofisi, taura nenyanzvi dzako dzeIT kuti ubatsirwe.

Kuvhura Scheduler

Saka ngatitangei Scheduler. Kuti uite izvi, unogona kana:

• Kurudyi tinya bhatani kutanga uye sarudza Computer Management (Computer management)
• Sarudza mu Control Panel: Kutonga - Task Scheduler (Kudzora Panel - Administrative Zvishandiso - Task Scheduler)
• Sarudza kubva kumenyu huru Kutanga - Zvishandiso - Zvishandiso zveSitimu - Task Scheduler
• Dzvanyiridza tsananguro ye keyboard kunda+R, pinda mabasachd.msc uye tigoshingairira pinda

Iwindo rinotevera rinofanira kuoneka pachiratidziro (ndine vhezheni yeChirungu, asi iwe unogona zvakare kuve neshanduro):

Gadzira basa

Kugadzira basa idzva uchishandisa nyore nhanho-ne-nhanho wizard, tinya pane chinongedzo Gadzira basa riri nyore (Gadzira Basa Rekutanga) mupaneru yekurudyi.

Panhanho yekutanga yewizard, isa zita uye tsananguro yebasa richagadzirwa:

Dzvanya pane bhatani Next (Inotevera) uye munhanho inotevera isu tinosarudza chinokonzeresa - kumhanyisa frequency kana chiitiko chinotangisa basa redu (semuenzaniso, kubatidza komputa):

Kana iwe wakasarudza mazuva ose (Mazuva ese), ipapo munhanho inotevera iwe uchafanirwa kusarudza yakatarwa nguva, kutanga zuva rekutevedzana uye nhanho (yega ega 2nd, 5th day, nezvimwewo):

Nhanho inotevera ndeyekusarudza chiito - Mhanya chirongwa ichi (Tanga chirongwa):

Uye pakupedzisira, chinhu chinonyanya kufadza ndechekuti chii chaizvo chinoda kuvhurwa:

Mu Chirongwa kana script (Chirongwa/script) iwe unofanirwa kupinda munzira inoenda kuMicrosoft Excel sechirongwa, kureva kuti yakananga kuExcel inoitiswa. Pamakomputa akasiyana ane mavhezheni akasiyana eWindows neHofisi, faira iri rinogona kunge riri mumaforodha akasiyana, saka hedzi nzira shoma dzekuti uzive kwairi:

• Tinya-kurudyi pane icon (shortcut) kuvhura Excel padesktop kana mubhara rebasa uye sarudza rairo Materials (Zvinhu), uye ipapo pahwindo rinovhurwa, tevedzera nzira kubva mumutsara chipfuro:

                       

• Vhura chero bhuku rebasa reExcel, wobva wavhura Task Manager (Task Manager) pushing Ctrl+alt+From uye nekudzvanya kurudyi pamutsetse Microsoft Excel, sarudza murairo Materials (Zvinhu). Muhwindo rinovhura, unogona kutevedzera nzira, kwete kukanganwa kuwedzera backslash kwairi uye EXCEL.EXE pamagumo:

               

• Vhura Excel, vhura Visual Basic edhita ine keyboard shortcut alt+F11, vhura panhi Pakarepo mubatanidzwa we Ctrl+G, isa mutemo mairi:

  ? Application.Path

  ... uye tinya ipapo pinda

  

  Kopa nzira inobuda, kwete kukanganwa kuwedzera backslash kwairi uye EXCEL.EXE pamagumo.

Mu Wedzera nharo (kusarudza) (Wedzera nharo (sarudzo) iwe unofanirwa kuisa nzira yakazara yebhuku ine macro yatinoda kuvhura.

Kana zvese zvapinda, wobva wadzvanya Next ndokuzo pedza (Pedzisa). Basa racho rinofanira kuwedzerwa kune general list:

Zviri nyore kubata basa rakagadzirwa uchishandisa mabhatani ari kurudyi. Pano unogona kuyedza basa nekurimhanyisa nekukasira (mhanya)pasina kumirira nguva yakatarwa. Unogona kudzima basa kwenguva pfupi (Dzinga)zvekuti inomira kumhanya kwekanguva, senge zororo rako. Zvakanaka, iwe unogona kugara uchichinja ma parameter (mazuva, nguva, zita refaira) kuburikidza nebhatani Materials (Zvinhu).

Wedzera macro kuvhura faira

Ikozvino zvasara kusungirira mubhuku redu kuvhurwa kweiyo macro yatinoda pane iyo faira yakavhurika chiitiko. Kuti uite izvi, vhura bhuku uye enda kuVisual Basic mupepeti uchishandisa nzira yekudimbudzira keyboard alt+F11 kana mabhatani Visual Basic Tab yokuvaka (Mugadziri). Muhwindo rinovhura mukona yekumusoro kuruboshwe, unofanirwa kutsvaga faira redu pamuti uye tinya kaviri kuti uvhure module. Iri bhuku (Bhuku Rebasa rino).

Kana iwe usingaone iyi hwindo muVisual Basic edhita, saka unogona kuivhura kuburikidza nemenyu Tarisa - Project Explorer.

Mufafitera remodule rinovhurwa, wedzera bhuku vhura chiitiko chekubata nekurisarudza kubva pane anodonhedza pasi zvinyorwa kumusoro. Bhuku rebasa и Open, zvichiteerana:

A process template inofanira kuoneka pachiratidziri. Bhuku rekushanda_Vhura, papi pakati pemitsetse Private Sub и End Sub uye iwe unofanirwa kuisa iyo VBA mirairo iyo inofanirwa kuitiswa otomatiki kana iri Excel bhuku rebasa ravhurwa, kana Scheduler anorivhura zvinoenderana nehurongwa. Heano mamwe anobatsira sarudzo dze overclocking:

• Bhuku Rebasa rino.RefreshAll - Inozorodza ese ekunze data mibvunzo, Power Query mibvunzo, uye PivotTables. Sarudzo inosarudzika. Ingo usakanganwa kubvumidza kubatana kune yekunze data nekukasira uye kugadzirisa zvinongedzo kuburikidza Faira - Sarudzo - Trust Center - Trust Center Sarudzo - Zvekunze Zvemukati, kana zvisina kudaro, paunovhura bhuku, yambiro yakajairika ichaonekwa uye Excel, pasina kuvandudza chero chinhu, ichamirira chikomborero chako nenzira yekudzvanya bhatani. Vhura zvirimo (Eneble content):

  

• ActiveWorkbook.Connections(“Connection_Name”).Simudza - kugadzirisa data paConnection_Name yekubatanidza.
• Mapepa(“Sheet5").PivotTables("PivotTable1«).PivotCache.Refresh - kugadzirisa imwe tafura yepivot yakatumidzwa PivotTable1 pabepa Sheet5.
• Kushandisa.Karukureta -kuverengerazve ese akavhurwa eExcel mabhuku ebasa.
• Application.CalculateFullRebuild -kumanikidzwa kuverengerwa kwemafomu ese uye kuvaka patsva zvese zvinotsamira pakati pemaseru mumabhuku ese akavhurika ebasa (zvakaenzana nekuisa zvakare mafomula ese).
• Mapepa ekushanda(“Chirevo”).PrintOut - kudhinda pepa Photos.
• Fonera MyMacro - mhanyisa macro ane zita MyMacro.
• ThisWorkbook.Save - chengetedza bhuku razvino
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Ikozvino, “:”, “-“) & “.xlsx” – chengetedza bhuku racho kufolder D:Chinyorwa pasi pezita Photos nezuva nenguva zvakabatanidzwa kuzita racho.

Kana iwe uchida kuti macro iitwe chete kana faira ravhurwa neMurongwa na5:00 am, uye kwete nguva dzese mushandisi anovhura bhuku rekushanda mukati mezuva rekushanda, saka zvine musoro kuwedzera cheki yenguva, semuenzaniso:

Kana Format(Iyezvino, "hh:mm") = "05:00" Zvadaro Bhuku Rebasa rino.RefreshAll  

Ndizvo zvose. Usakanganwe kuchengetedza bhuku rako rekushandisa mune macro-enabled fomati (xlsm kana xlsb) uye unogona kuvhara zvakachengeteka Excel woenda kumba, uchisiya komputa yako iripo. Pane imwe nguva (kunyangwe PC yakakiyiwa), Murongi achavhura Excel uye ovhura iyo yakatsanangurwa faira mairi, uye macro edu achaita akarongwa zviito. Uye iwe uchanakidzwa pamubhedha apo chirevo chako chinorema chinoverengerwa otomatiki - runako! 🙂

• Chii macros, maitiro ekuashandisa, kupi kwekuisa Visual Basic kodhi muExcel
• Maitiro ekugadzira yako macro yekuwedzera-mukati yeExcel
• Maitiro ekushandisa iyo Personal Macro Workbook seraibhurari yemakros ako muExcel