Ua haujlwm macro raws sijhawm

Ib qho xwm txheej tshwm sim hauv kev xyaum: koj yuav tsum tau khiav ib lossis ntau dua ntawm koj cov macros ntawm ib lub sijhawm lossis ntawm qee zaus. Piv txwv li, koj muaj daim ntawv ceeb toom loj thiab hnyav uas hloov kho ib nrab teev thiab koj xav khiav qhov hloov tshiab ib nrab teev ua ntej koj tuaj txog ntawm chaw ua haujlwm thaum sawv ntxov. Los yog koj muaj qhov macro uas yuav tsum tau xa tawm email rau cov neeg ua haujlwm ntawm lub sijhawm teev tseg. Lossis, thaum ua haujlwm nrog PivotTable, koj xav kom nws hloov kho ntawm ya txhua 10 vib nas this, thiab lwm yam.

Cia peb saib dab tsi Excel thiab Windows muaj peev xwm los siv qhov no.

Khiav ib tug macro ntawm ib zaus

Qhov yooj yim tshaj plaws los ua qhov no yog siv cov txheej txheem VBA built-in Application.OnTimeIb qho uas khiav cov macro teev nyob rau lub sijhawm teev tseg. Cia peb nkag siab qhov no nrog ib qho piv txwv.

Qhib Visual Basic editor nrog lub pob ntawm tib lub npe ntawm lub tab tsim tawm (Tus tsim tawm) los yog keyboard shortcut Alt+F11, ntxig ib qho tshiab los ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov cai hauv qab no rau hauv:

Dim TimeToRun 'global variable where the next run time is stored' this is the main macro Sub MyMacro() Application.Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'sau cell A1 nrog cov xim random :) Hu rau NextRun 'khiav NextRun macro los teeb tsa lub sijhawm ua haujlwm tom ntej End Sub' no macro teeb tsa lub sijhawm rau kev khiav ntawm lub ntsiab macro Sub NextRun() TimeToRun = Tam sim no + TimeValue("00: 00:03") 'ntxiv 3 vib nas this mus rau lub sijhawm tam sim no Application.OnTime TimeToRun, "MyMacro" ' teem caij ua haujlwm tom ntej End Sub 'macro los pib rov ua ntu zus Sub Start() Hu rau NextRun End Sub 'macro kom nres qhov rov ua ntu zus Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Cia peb xam seb yog dab tsi ntawm no.

Ua ntej, peb xav tau qhov sib txawv uas yuav khaws lub sijhawm ntawm kev khiav haujlwm tom ntej ntawm peb cov macro - Kuv hu nws TimeToRun. Thov nco ntsoov tias cov ntsiab lus ntawm qhov hloov pauv no yuav tsum muaj rau tag nrho peb cov macros tom ntej, yog li peb yuav tsum ua kom nws ntiaj teb no, piv txwv li tshaj tawm thaum pib ntawm lub module ua ntej thawj sub.

Tom ntej no los peb lub ntsiab macro MyMacro, uas yuav ua lub luag haujlwm tseem ceeb - rov xam phau ntawv siv txoj kev Daim ntawv thov.Xaiv. Txhawm rau ua kom pom tseeb, kuv ntxiv cov mis =TDATE() rau hauv daim ntawv hauv xov tooj ntawm tes A1, uas qhia txog hnub thiab sijhawm - thaum rov suav dua, nws cov ntsiab lus yuav raug hloov kho ua ntej peb ob lub qhov muag (tsuas yog tig rau ntawm cov duab vib nas this hauv lub xov tooj ntawm tes. format). Rau kev lom zem ntxiv, kuv kuj tau ntxiv rau macro cov lus txib kom sau cov xov tooj ntawm tes A1 nrog cov xim xaiv xaiv (cov xim xim yog tus lej hauv qhov ntau 0..56, uas yog tsim los ntawm kev ua haujlwm. Rnd thiab rounds mus rau ib tug integer muaj nuj nqi cov menyuam).

Macro NextRun ntxiv rau tus nqi dhau los TimeToRun 3 vib nas this ntxiv thiab teem caij ua haujlwm tom ntej ntawm lub ntsiab macro MyMacro rau lub sijhawm tshiab no. Tau kawg, hauv kev xyaum, koj tuaj yeem siv lwm lub sijhawm uas koj xav tau los ntawm kev teeb tsa cov lus sib cav TimeValue ua format hh:mm:ss.

Thiab thaum kawg, tsuas yog kom yooj yim, ntau qhov kev tso tawm macros tau ntxiv. Tsev thiab nws ua tiav Tag. Qhov kawg siv plaub txoj kev sib cav los rhuav tshem qhov sib lawv liag. Nyob rau lub sijhawm sib npaug cuav.

Tag nrho yog tias koj khiav lub macro TsevTom qab ntawd tag nrho cov carousel yuav tig, thiab peb yuav pom cov duab hauv qab no ntawm daim ntawv:

Koj tuaj yeem nres qhov sib lawv liag los ntawm kev khiav, ntsig txog, macro Tag. Txhawm rau kom yooj yim, koj tuaj yeem muab cov keyboard shortcuts rau ob qho tib si macros siv cov lus txib Macros – Kev xaiv tab tsim tawm (Tus tsim tawm - Macros - Kev Xaiv).

Ua haujlwm macro ntawm lub sijhawm

Tau kawg, txhua yam uas tau piav qhia saum toj no tsuas yog ua tau yog tias koj muaj Microsoft Excel khiav thiab peb cov ntaub ntawv qhib rau hauv nws. Tam sim no cia peb saib cov ntaub ntawv nyuaj dua: koj yuav tsum khiav Excel raws li lub sijhawm teem tseg, piv txwv li, txhua hnub ntawm 5:00, qhib daim ntawv qhia loj thiab nyuaj hauv nws thiab hloov kho tag nrho cov kev sib txuas thiab cov lus nug hauv nws kom nws yuav. npaj txhij thaum peb tuaj txog ntawm chaw ua haujlwm 🙂

Hauv qhov xwm txheej zoo li no, nws yog qhov zoo dua los siv Windows Scheduler - ib qho kev pab cuam tshwj xeeb tsim rau hauv ib qho version ntawm Windows uas tuaj yeem ua cov haujlwm teev tseg ntawm lub sijhawm. Qhov tseeb, koj twb tau siv nws yam tsis paub nws, vim tias koj lub PC tsis tu ncua xyuas kev hloov kho, rub tawm cov ntaub ntawv tiv thaiv kab mob tshiab, synchronizes huab folders, thiab lwm yam. Nws yog tag nrho cov haujlwm ntawm Scheduler. Yog li peb txoj haujlwm yog ntxiv rau cov haujlwm uas twb muaj lawm lwm qhov uas yuav tso Excel thiab qhib cov ntaub ntawv teev tseg hauv nws. Thiab peb yuav dai peb cov macro ntawm qhov kev tshwm sim Phau ntawv_Open cov ntaub ntawv no - thiab qhov teeb meem tau daws.

Kuv xav ceeb toom rau koj tam sim ntawd tias kev ua haujlwm nrog Scheduler yuav xav tau cov neeg siv siab tshaj txoj cai, yog li yog tias koj nrhiav tsis tau cov lus txib thiab kev ua haujlwm tau piav qhia hauv qab no ntawm koj lub khoos phis tawj ua haujlwm hauv chaw ua haujlwm, hu rau koj tus kws tshaj lij IT rau kev pab.

Launching lub Scheduler

Yog li cia peb pib lub Scheduler. Txhawm rau ua qhov no, koj tuaj yeem:

• Txoj nyem rau ntawm lub pob Start thiab xaiv Computer Management (Computer Management)
• Xaiv hauv Control Vaj Huam Sib Luag: Administration – Task Scheduler (Control Vaj Huam Sib Luag — Cov Cuab Yeej Tswj Xyuas — Task Scheduler)
• Xaiv los ntawm cov ntawv qhia zaub mov tseem ceeb Pib – Accessories – System Tools – Task Scheduler
• Xovxwm keyboard shortcut Yeej+R, nkag mus taskchd.msc thiab nias Sau

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

Tsim ua haujlwm

Txhawm rau tsim ib txoj haujlwm tshiab siv cov txheej txheem yooj yim-ib-kauj ruam wizard, nyem rau ntawm qhov txuas Tsim ib txoj haujlwm yooj yim (Tsim Basic Task) nyob rau hauv txoj cai vaj huam sib luag.

Hauv thawj kauj ruam ntawm tus wizard, sau lub npe thiab cov lus piav qhia ntawm txoj haujlwm yuav tsum tau tsim:

Nyem rau feem khawm Tom ntej no (Tom ntej) thiab nyob rau hauv cov kauj ruam tom ntej peb xaiv ib tug tshwm sim - lub community launch zaus los yog ib qho kev tshwm sim uas yuav pib peb txoj hauj lwm (piv txwv li, tig lub computer):

Yog koj xaiv Txhua hnub (Txhua hnub), tom qab ntawd hauv kauj ruam tom ntej koj yuav tsum xaiv lub sijhawm tshwj xeeb, hnub pib ntawm qhov sib lawv liag thiab kauj ruam (txhua hnub 2, 5 hnub, thiab lwm yam):

Cov kauj ruam tom ntej yog xaiv qhov kev ua - Khiav qhov kev pab cuam (Pib qhov program):

Thiab thaum kawg, qhov nthuav tshaj plaws yog qhov yuav tsum tau qhib:

Nyob rau hauv lub Program lossis tsab ntawv (Program/script) koj yuav tsum nkag mus rau hauv txoj kev mus rau Microsoft Excel raws li ib qho kev pab cuam, piv txwv li ncaj qha mus rau Excel executable. Ntawm cov khoos phis tawj sib txawv uas muaj qhov sib txawv ntawm Windows thiab Office, cov ntaub ntawv no yuav nyob hauv cov folders sib txawv, yog li ntawm no yog ob peb txoj hauv kev rau koj kom paub nws qhov chaw:

• Txoj cai-nias rau ntawm lub cim (shortcut) txhawm rau qhib Excel ntawm lub desktop lossis hauv lub taskbar thiab xaiv cov lus txib ntaub ntawv (Properties), thiab tom qab ntawd hauv qhov rai uas qhib, luam txoj hauv kev los ntawm kab Target:

                       

• Qhib ib phau ntawv ua haujlwm Excel, tom qab ntawd qhib Task Manager (Task Manager) thawb Ctrl+Alt+Los ntawm thiab los ntawm txoj cai nyem rau ntawm kab Microsoft Excel, xaiv cov lus txib ntaub ntawv (Properties). Hauv qhov rai uas qhib, koj tuaj yeem luam txoj kev, tsis txhob hnov ​​​​qab ntxiv qhov backslash rau nws thiab EXCEL.EXE thaum kawg:

               

• Qhib Excel, qhib Visual Basic editor nrog keyboard shortcut Alt+F11, qhib vaj huam sib luag tam sim ntawd kev sib xyaw ntawm Ctrl+G, sau cov lus txib rau hauv nws:

  ? Application.Path

  … thiab nias rau Sau

  

  Luam txoj kev tshwm sim, tsis txhob hnov ​​​​qab ntxiv qhov backslash rau nws thiab EXCEL.EXE thaum kawg.

Nyob rau hauv lub Ntxiv rau kev sib cav (tsis teb los tau) (Ntxiv cov lus sib cav (yeem) koj yuav tsum tau ntxig tag nrho txoj hauv kev mus rau phau ntawv nrog cov macro uas peb xav qhib.

Thaum txhua yam nkag, ces nyem Tom ntej no thiab ces Tag (Tiav). Txoj haujlwm yuav tsum tau ntxiv rau hauv daim ntawv teev npe dav dav:

Nws yog qhov yooj yim los tswj cov haujlwm tsim siv cov nyees khawm ntawm sab xis. Ntawm no koj tuaj yeem sim ua haujlwm los ntawm kev khiav nws tam sim ntawd (khiav)tsis tau tos rau lub sijhawm teev tseg. Koj tuaj yeem deactivate ib qho haujlwm ib ntus (Lov tes taw)yog li ntawd nws nres khiav rau ib lub sij hawm, xws li koj so. Zoo, koj tuaj yeem hloov pauv qhov tsis muaj (hnub tim, lub sijhawm, cov ntaub ntawv npe) los ntawm lub pob ntaub ntawv (Properties).

Ntxiv ib qho macro los qhib cov ntaub ntawv

Tam sim no nws tseem nyob rau hauv peb phau ntawv tso tawm ntawm macro peb xav tau ntawm cov ntaub ntawv qhib kev tshwm sim. Txhawm rau ua qhov no, qhib phau ntawv thiab mus rau Visual Basic editor siv cov keyboard shortcut Alt+F11 los yog khawm Visual Basic tab tsim tawm (Tus tsim tawm). Nyob rau hauv lub qhov rais uas qhib nyob rau hauv lub Upper sab laug ces kaum, koj yuav tsum nrhiav tau peb cov ntaub ntawv ntawm tsob ntoo thiab ob-nias mus qhib lub module Phau ntawv no (Cov ntaub ntawv no).

Yog tias koj tsis pom qhov rais no hauv Visual Basic editor, ces koj tuaj yeem qhib nws los ntawm cov ntawv qhia zaub mov Saib - Project Explorer.

Nyob rau hauv lub qhov rais module uas qhib, ntxiv ib phau ntawv qhib kev tshwm sim handler los ntawm xaiv nws los ntawm cov npe drop-down nyob rau sab saum toj Workbook и Qhib, feem:

Ib tus qauv txheej txheem yuav tsum tshwm sim ntawm qhov screen. Phau ntawv_Open, qhov twg ntawm kab Private Sub и End Sub thiab koj yuav tsum tau ntxig cov VBA cov lus txib uas yuav tsum tau ua tiav thaum Excel phau ntawv no qhib, thaum lub Scheduler qhib nws raws li lub sijhawm. Nov yog qee qhov kev xaiv muaj txiaj ntsig rau overclocking:

• ThisWorkbook.RefreshAll - Rov ua dua txhua cov lus nug cov ntaub ntawv sab nraud, Cov lus nug Lub Hwj Chim, thiab PivotTables. Qhov ntau yam kev xaiv. Tsuas yog tsis txhob hnov ​​​​qab tso cai rau kev sib txuas rau cov ntaub ntawv sab nraud los ntawm lub neej ntawd thiab hloov kho cov txuas ntawm Cov Ntaub Ntawv – Cov Kev Xaiv – Trust Center – Trust Center Options – Cov ntsiab lus sab nraud, txwv tsis pub, thaum koj qhib phau ntawv, tus qauv ceeb toom yuav tshwm sim thiab Excel, yam tsis muaj kev hloov kho dab tsi, yuav tos koj txoj koob hmoov hauv daim ntawv nyem rau ntawm lub pob. Qhib cov ntsiab lus (Enable content):

  

• ActiveWorkbook.Connections(“Connection_Name”).Refresh - hloov kho cov ntaub ntawv ntawm Connection_Name kev sib txuas.
• Cov ntaub ntawv (“Tshooj 5“).PivotTables(“PivotTable1«).PivotCache.Refresh - hloov kho ib lub rooj pivot lub npe PivotTable1 ntawm daim ntawv Ntawv 5.
• Daim ntawv thov.Xaiv - rov suav tag nrho cov ntaub ntawv qhib Excel.
• Application.CalculateFullRebuild - Kev yuam kom rov suav tag nrho cov qauv thiab rov tsim kho txhua qhov kev cia siab ntawm cov hlwb hauv txhua phau ntawv qhib (sib npaug rau rov nkag txhua cov qauv).
• Cov ntawv ua haujlwm (“Qhia”).PrintOut - luam ntawv duab.
• Hu rau MyMacro - khiav lub npe macro MyMacro.
• ThisWorkbook.Txuag - txuag phau ntawv tam sim no
  
• ThisWorkbooks.SaveAs “D: ArchiveReport” & Hloov (Tam sim no, “:”, “-“) & “.xlsx” - Txuag phau ntawv mus rau ib lub nplaub tshev D: Archive nyob rau hauv lub npe duab nrog hnub thiab sijhawm ntxiv rau lub npe.

Yog tias koj xav kom macro raug tua tsuas yog thaum cov ntaub ntawv qhib los ntawm Scheduler thaum 5:00 teev sawv ntxov, thiab tsis yog txhua zaus tus neeg siv qhib phau ntawv ua haujlwm thaum hnub ua haujlwm, ces nws ua rau kev txiav txim siab ntxiv sijhawm kuaj, piv txwv li:

Yog Format(Tam sim no, "hh:mm") = "05:00" Ces ThisWorkbook.RefreshAll  

Yog tag nrho. Tsis txhob hnov ​​​​qab khaws koj phau ntawv ua haujlwm hauv hom macro-enabled (xlsm lossis xlsb) thiab koj tuaj yeem kaw Excel kom zoo thiab mus tsev, tawm hauv koj lub computer. Thaum lub sijhawm muab sijhawm (txawm tias lub PC kaw lawm), Scheduler yuav pib Excel thiab qhib cov ntaub ntawv teev tseg hauv nws, thiab peb cov macro yuav ua cov haujlwm ua haujlwm. Thiab koj yuav luxuriate hauv txaj thaum koj daim ntawv ceeb toom hnyav tau rov suav dua - kev zoo nkauj! 🙂

• Dab tsi yog macro, siv lawv li cas, qhov twg los ntxig Visual Basic code hauv Excel
• Yuav ua li cas los tsim koj tus kheej macro add-in rau Excel
• Yuav ua li cas siv Tus Kheej Macro Workbook ua lub tsev qiv ntawv rau koj cov macros hauv Excel