Водење макро на време

Многу чест случај во пракса: треба да извршите едно или повеќе од вашите макроа во дадено време или на одредена фреквенција. На пример, имате голем и тежок извештај што се ажурира половина час и сакате да го извршите ажурирањето половина час пред да пристигнете на работа наутро. Или имате макро што автоматски треба да испраќа е-пошта до вработените на одредена фреквенција. Или, кога работите со PivotTable, сакате таа да се ажурира на секои 10 секунди и така натаму.

Ајде да погледнеме што Excel и Windows имаат способност да го имплементираат ова.

Водење макро на дадена фреквенција

Најлесен начин да го направите ова е да го користите вградениот метод VBA Апликација.НавремеAn што го извршува наведеното макро во одреденото време. Ајде да го разбереме ова со практичен пример.

Отворете го уредникот на Visual Basic со истоименото копче на јазичето инвеститорот (програмер) или кратенка на тастатура Alt+F11, вметнете нов модул низ менито Вметни – Модул и копирајте го следниов код таму:

Dim TimeToRun 'глобална променлива каде што е зачувано следното време на извршување 'ова е главната макро Sub MyMacro() Application. Calculate 'recalculate the book Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill ќелијата A1 со случајна боја :) Повикајте NextRun 'изврши го макрото NextRun за да го поставите следното време на извршување End Sub 'ова макро го поставува времето за следното извршување на главното макро Sub NextRun() TimeToRun = Now + TimeValue("00: 00:03") 'додадете 3 секунди на тековното време Application.OnTime TimeToRun, "MyMacro" 'закажи го следното извршување Крај Под 'макро за да започне повторувачката низа Sub Start() Повикајте NextRun End Sub' макро за да се запре секвенцата на повторување Под Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Ајде да откриеме што е што овде.

Прво, потребна ни е променлива која ќе го зачува времето на следното извршување на нашето макро - ја повикав TimeToRun. Ве молиме имајте предвид дека содржината на оваа променлива мора да биде достапна за сите наши следни макроа, па затоа треба да ја направиме глобална, односно декларирајте на самиот почеток на модулот пред првиот под.

Следно доаѓа нашето главно макро MyMacro, кој ќе ја изврши главната задача – повторно да ја пресмета книгата користејќи го методот Примена.Пресметај. За да биде појасно, ја додадов формулата =TDATE() на листот во ќелијата А1, кој ги прикажува датумот и времето - кога повторно ќе се пресметаат, неговата содржина ќе се ажурира пред нашите очи (само вклучете го приказот на секунди во ќелијата формат). За дополнителна забава, на макрото ја додадов и командата за пополнување на ќелијата А1 со случајно избрана боја (кодот на бојата е цел број во опсегот 0..56, кој се генерира од функцијата Рнд и се заокружува на цел бројна функција Int).

Макро Следно Стартувај додава на претходната вредност TimeToRun Уште 3 секунди и потоа го закажува следното извршување на главното макро MyMacro за ова ново време. Се разбира, во пракса, можете да ги користите сите други временски интервали што ви се потребни со поставување на функциските аргументи Временска вредност во формат hh:mm:ss.

И конечно, само за погодност, додадени се повеќе макроа за лансирање на секвенца. Почетна и неговото завршување Заврши. Последниот го користи аргументот на четвртиот метод за да ја прекине низата. На време еднаков Лажни.

Вкупно ако го стартувате макрото Почетна, тогаш целиот овој рингишпил ќе се врти, а на листот ќе ја видиме следната слика:

Можете да ја запрете низата со извршување, соодветно, макро Заврши. За погодност, можете да доделите кратенки на тастатурата на двете макроа користејќи ја командата Макроа - Опции табот инвеститорот (Програмер - Макроа - Опции).

Водење макро според распоред

Се разбира, сè што е опишано погоре е можно само ако имате Microsoft Excel работи и нашата датотека е отворена во неа. Сега да погледнеме покомплициран случај: треба да го стартувате Excel според даден распоред, на пример, секој ден во 5:00 часот, да отворите голем и сложен извештај во него и да ги ажурирате сите врски и прашања во него, така што ќе бидете спремни додека да пристигнеме на работа 🙂

Во таква ситуација, подобро е да се користи Распоред на Windows – програма специјално вградена во која било верзија на Windows која може да врши одредени дејства на распоред. Всушност, веќе го користите без да знаете, бидејќи вашиот компјутер редовно проверува дали има ажурирања, презема нови бази на податоци за анти-вируси, синхронизира папки во облак, итн. Сето тоа е работа на Распоредувачот. Значи, нашата задача е да додадеме на постојните задачи уште една што ќе го стартува Excel и ќе ја отвори наведената датотека во неа. И ние ќе го обесиме нашето макро на настанот Работна тетратка_Отворено оваа датотека - и проблемот е решен.

Сакам веднаш да ве предупредам дека работата со Распоредувачот може да бара напредни кориснички права, па ако не можете да ги најдете командите и функциите опишани подолу на вашиот работен компјутер во канцеларијата, контактирајте со вашите ИТ специјалисти за помош.

Стартување на Распоредувачот

Значи, да го започнеме Распоредувачот. За да го направите ова, можете или:

• Десен клик на копчето почеток и изберете Компјутерски менаџмент (Управување со компјутер)
• Изберете во контролниот панел: Администрација – Распоред на задачи (Контролен панел - Административни алатки - Распоредувач на задачи)
• Изберете од главното мени Почеток – Додатоци – Системски алатки – Распоредувач на задачи
• Притиснете кратенка на тастатурата победа+R, влезе задачи.md и притиснете Внесете

Следниот прозорец треба да се појави на екранот (имам англиска верзија, но можете да имате и верзија):

Создадете задача

За да креирате нова задача користејќи едноставен волшебник чекор-по-чекор, кликнете на врската Направете едноставна задача (Креирај основна задача) во десниот панел.

На првиот чекор од волшебникот, внесете го името и описот на задачата што треба да се креира:

Кликнете на копчето Следна (Следно) и во следниот чекор избираме активирач - фреквенцијата на стартување или настан што ќе ја стартува нашата задача (на пример, вклучување на компјутерот):

Ако сте одбрале Секојдневно (Дневно), потоа во следниот чекор ќе треба да изберете одредено време, датум на почеток на низата и чекор (секој 2-ри ден, 5-ти ден, итн.):

Следниот чекор е да изберете акција - Извршете ја програмата (Започнете програма):

И, конечно, најинтересно е што точно треба да се отвори:

Во Програма или скрипта (Програма/скрипта) треба да ја внесете патеката до Microsoft Excel како програма, односно директно до извршната датотека на Excel. На различни компјутери со различни верзии на Windows и Office, оваа датотека може да се наоѓа во различни папки, па затоа еве неколку начини како да ја дознаете нејзината локација:

• Десен-клик на иконата (кратенка) за да го стартувате Excel на работната површина или во лентата со задачи и изберете ја командата материјали (Својства), а потоа во прозорецот што се отвора, копирајте ја патеката од линијата Целна:

                       

• Отворете која било работна книга на Excel, а потоа отворете Задачата на менаџерот (Менаџер за задачи) туркање Ctrl+Alt+Од и со десен клик на линијата Microsoft Excel, изберете команда материјали (Својства). Во прозорецот што се отвора, можете да ја копирате патеката, не заборавајќи да додадете назад црта на него и EXCEL.EXE на крајот:

               

• Отворете Excel, отворете Visual Basic уредник со кратенка на тастатурата Alt+F11, отворете го панелот непосредна комбинација на Ctrl+G, внесете ја командата во неа:

  ? Примена.Пат

  … и кликнете на Внесете

  

  Копирајте ја добиената патека, не заборавајќи да додадете назад црта на него и EXCEL.EXE на крајот.

Во Додадете аргументи (опционално) (Додадете аргументи (изборно)) треба да ја вметнете целосната патека до книгата со макрото што сакаме да го отвориме.

Кога сè е внесено, потоа кликнете Следна и потоа Заврши (Заврши). Задачата треба да се додаде на општата листа:

Удобно е да се управува со креираната задача користејќи ги копчињата од десната страна. Овде можете да ја тестирате задачата со тоа што веднаш ќе ја извршите (трчај)без да се чека наведеното време. Можете привремено да деактивирате задача (Оневозможи)така што ќе престане да работи одреден временски период, како што е вашиот одмор. Па, секогаш можете да ги промените параметрите (датуми, време, име на датотека) преку копчето материјали (Својства).

Додајте макро за да отворите датотека

Сега останува да го закачиме во нашата книга лансирањето на макрото што ни треба на настанот отворена датотека. За да го направите ова, отворете ја книгата и одете до уредникот на Visual Basic користејќи ја кратенката на тастатурата Alt+F11 или копчиња Visual Basic табот инвеститорот (програмер). Во прозорецот што се отвора во горниот лев агол, треба да ја пронајдете нашата датотека на дрвото и да кликнете двапати за да го отворите модулот Оваа книга (Оваа Работна тетратка).

Ако не го гледате овој прозорец во уредникот на Visual Basic, тогаш можете да го отворите преку менито Преглед - Проект Explorer.

Во прозорецот на модулот што се отвора, додајте управувач со настани за отворена книга со избирање од паѓачките списоци на врвот Работа и Отворено, соодветно:

На екранот треба да се појави образец за процедура. Работна тетратка_Отворено, каде меѓу линиите Приватен под и Под крајот и треба да ги вметнете оние VBA команди кои треба автоматски да се извршат кога ќе се отвори оваа работна книга на Excel, кога Распоредувачот ќе ја отвори според распоредот. Еве неколку корисни опции за оверклокување:

• This Workbook.RefreshAll – Ги освежува сите надворешни барања за податоци, прашања за Power Query и PivotTables. Најразновидна опција. Само не заборавајте стандардно да дозволите поврзување со надворешни податоци и да ги ажурирате врските преку Датотека – Опции – Центар за доверба – Опции на Центарот за доверба – Надворешна содржина, во спротивно, кога ќе ја отворите книгата, ќе се појави стандардно предупредување и Excel, без да ажурира ништо, ќе го чека вашиот благослов во вид на кликнување на копчето Овозможи содржина (Овозможи содржина):

  

• ActiveWorkbook.Connections(„Connection_Name“).Освежи — ажурирање на податоците за врската Connection_Name.
• Листови („Лист 5“).PivotTables(“PivotTable1«).PivotCache.Освежи – ажурирање на единечна стожерна табела со име Стожерна табела 1 на листот Sheet5.
• Примена.Пресметај – повторна пресметка на сите отворени работни книги на Excel.
• Апликација.CalculateFullRebuild – принудно повторно пресметување на сите формули и обнова на сите зависности помеѓу ќелиите во сите отворени работни книги (што е еквивалентно на повторно внесување на сите формули).
• Работни листови („Извештај“).PrintOut – лист за печатење слики.
• Јавете се на MyMacro – стартувајте макро со име MyMacro.
• This Workbook.Save – зачувајте ја тековната книга
  
• ThisWorkbooks.SaveAs „D:ArchiveReport“ & Replace(Сега, „:“, „-“) и „.xlsx“ – зачувајте ја книгата во папка Д: Архива под името слики со датум и време додадени на името.

Ако сакате макрото да се извршува само кога датотеката е отворена од Распоредувачот во 5:00 часот наутро, а не секогаш кога корисникот ја отвора работната книга во текот на работниот ден, тогаш има смисла да додадете проверка на времето, на пример:

If Format(Now, "hh:mm") = "05:00" Потоа ThisWorkbook.RefreshAll  

Тоа е се. Не заборавајте да ја зачувате работната книга во макро-овозможен формат (xlsm или xlsb) и можете безбедно да го затворите Excel и да одите дома, оставајќи го вашиот компјутер вклучен. Во даден момент (дури и ако компјутерот е заклучен), Распоредувачот ќе го стартува Excel и ќе ја отвори одредената датотека во неа, а нашето макро ќе ги изврши програмираните дејства. И ќе се луксузирате во кревет додека вашиот тежок извештај автоматски се пресметува – убавина! 🙂

• Што се макроа, како да ги користите, каде да го вметнете кодот на Visual Basic во Excel
• Како да креирате сопствен додаток за макро за Excel
• Како да ја користите личната работна книга за макро како библиотека за вашите макроа во Excel