Изпълнение на макрос навреме

Много често срещан случай в практиката: трябва да стартирате един или повече от вашите макроси в даден момент или на определена честота. Например, имате голям и тежък отчет, който се актуализира половин час и бихте искали да стартирате актуализацията половин час преди да пристигнете на работа сутринта. Или имате макрос, който трябва автоматично да изпраща имейли до служители с определена честота. Или, когато работите с обобщена таблица, искате тя да се актуализира в движение на всеки 10 секунди и т.н.

Нека да разгледаме какво Excel и Windows имат способността да реализират това.

Изпълнение на макрос на дадена честота

Най-лесният начин да направите това е да използвате вградения VBA метод Application.OnTimeAn, който изпълнява посочения макрос в определеното време. Нека разберем това с практически пример.

Отворете редактора на Visual Basic с бутона със същото име в раздела предприемач (Разработчик) или клавишна комбинация Друг+F11, вмъкнете нов модул през менюто Вмъкване – Модул и копирайте следния код там:

Dim TimeToRun 'глобална променлива, където се съхранява следващото време за изпълнение 'това е основният макрос Sub MyMacro() Application.Calculate 'преизчисляване на книгата 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" 'планирайте следващото изпълнение End Sub 'макро за стартиране на повтарящата се последователност Sub Start() Извикайте NextRun End Sub 'макро за спиране на повтарящата се последователност Sub Finish() Application.OnTime TimeToRun, "MyMacro", , False End Sub  

Нека да разберем какво има тук.

Първо, имаме нужда от променлива, която ще съхранява времето на следващото изпълнение на нашия макрос – нарекох го TimeToRun. Моля, имайте предвид, че съдържанието на тази променлива трябва да е достъпно за всички наши последващи макроси, така че трябва да го направим в световен мащаб, т.е. декларирайте в самото начало на модула преди първия Под.

Следва основният ни макрос MyMacro, който ще изпълнява основната задача – да преизчисли книгата по метода Приложение. Изчисли. За да стане по-ясно, добавих формулата =TDATE() към листа в клетка A1, която показва датата и часа – когато се преизчисли, съдържанието й ще се актуализира точно пред очите ни (просто включете показването на секунди в клетката формат). За допълнително забавление добавих към макроса командата за запълване на клетка A1 с произволно избран цвят (кодът на цвета е цяло число в диапазона 0..56, което се генерира от функцията Rnd и закръгля до целочислена функция Int).

Макро NextRun добавя към предишната стойност TimeToRun Още 3 секунди и след това планира следващото изпълнение на основния макрос MyMacro за това ново време. Разбира се, на практика можете да използвате всякакви други времеви интервали, от които се нуждаете, като зададете аргументите на функцията TimeValue във формат чч:мм:сс.

И накрая, само за удобство, бяха добавени повече макроси за стартиране на последователност. Начало и нейното завършване завършеност. Последният използва четвъртия аргумент на метода, за да прекъсне последователността. Навреме равен Фалшив.

Общо, ако стартирате макроса Начало, тогава цялата тази въртележка ще се завърти и ще видим следната картинка на листа:

Можете да спрете последователността, като изпълните съответно макроса завършеност. За удобство можете да зададете клавишни комбинации и на двата макроса с помощта на командата Макроси – Опции етикет предприемач (Програмист — Макроси — Опции).

Изпълнение на макрос по график

Разбира се, всичко описано по-горе е възможно само ако имате работещ Microsoft Excel и нашият файл е отворен в него. Сега нека разгледаме по-сложен случай: трябва да стартирате Excel по зададен график, например всеки ден в 5:00, да отворите голям и сложен отчет в него и да актуализирате всички връзки и заявки в него, така че да бъдете готови, докато пристигнем на работа 🙂

В такава ситуация е по-добре да използвате Windows Scheduler – програма, специално вградена във всяка версия на Windows, която може да изпълнява определени действия по график. Всъщност вие вече го използвате, без да го знаете, защото компютърът ви редовно проверява за актуализации, изтегля нови антивирусни бази данни, синхронизира облачни папки и т.н. Всичко това е работа на Scheduler. Така че нашата задача е да добавим към съществуващите задачи още една, която да стартира Excel и да отвори посочения файл в него. И ние ще окачи нашия макрос на събитието Workbook_Open този файл – и проблемът е решен.

Искам веднага да ви предупредя, че работата с Scheduler може да изисква разширени потребителски права, така че ако не можете да намерите командите и функциите, описани по-долу на работния си компютър в офиса, свържете се с вашите ИТ специалисти за помощ.

Стартиране на планировчика

И така, нека стартираме Scheduler. За да направите това, можете:

• Щракнете с десния бутон върху бутона Начало И изберете Управление на компютъра (Компютърно управление)
• Изберете в контролния панел: Администриране – Планировчик на задачи (Контролен панел — Административни инструменти — Планировчик на задачи)
• Изберете от главното меню Старт – Аксесоари – Системни инструменти – Планировчик на задачи
• Натиснете клавишната комбинация Печеля+R, въведете taskschd.msc и натиснете Въведете

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

Създайте задача

За да създадете нова задача с помощта на прост съветник стъпка по стъпка, щракнете върху връзката Създайте проста задача (Създаване на основна задача) в десния панел.

В първата стъпка на съветника въведете името и описанието на задачата, която ще бъде създадена:

Кликнете върху бутона Напред (Следващия) и в следващата стъпка избираме тригер – честотата на стартиране или събитие, което ще стартира нашата задача (например включване на компютъра):

Ако сте избрали ежедневно (Ежедневно), тогава в следващата стъпка ще трябва да изберете конкретно време, начална дата на последователността и стъпка (всеки 2-ри ден, 5-ти ден и т.н.):

Следващата стъпка е да изберете действие – Стартирайте програмата (Стартиране на програма):

И накрая, най-интересното е какво точно трябва да се отвори:

в Програма или скрипт (Програма/скрипт) трябва да въведете пътя до Microsoft Excel като програма, тоест директно до изпълнимия файл на Excel. На различни компютри с различни версии на Windows и Office този файл може да е в различни папки, така че ето няколко начина да разберете местоположението му:

• Щракнете с десния бутон върху иконата (пряк път), за да стартирате Excel на работния плот или в лентата на задачите и изберете командата материали (Имоти), а след това в прозореца, който се отваря, копирайте пътя от реда Цел:

                       

• Отворете която и да е работна книга на Excel, след което отворете Task Manager (Диспечер на задачите) натискане Ctrl+Друг+от и като щракнете с десния бутон върху линията Microsoft Excel, изберете команда материали (Имоти). В прозореца, който се отваря, можете да копирате пътя, като не забравяте да добавите обратна наклонена черта към него и EXCEL.EXE в края:

               

• Отворете Excel, отворете редактора на Visual Basic с клавишна комбинация Друг+F11, отворен панел Незабавен комбинация от Ctrl+G, въведете командата в него:

  ? Application.Path

  … и щракнете върху Въведете

  

  Копирайте получения път, като не забравяте да добавите обратна наклонена черта към него и EXCEL.EXE в края.

в Добавяне на аргументи (по избор) (Добавете аргументи (по избор)) трябва да вмъкнете пълния път до книгата с макроса, който искаме да отворим.

Когато всичко е въведено, щракнете Напред и след това завършеност (Завършек). Задачата трябва да се добави към общия списък:

Удобно е да управлявате създадената задача с помощта на бутоните вдясно. Тук можете да тествате задачата, като я стартирате веднага (бягам)без да изчаквате определеното време. Можете временно да деактивирате задача (Деактивиране)така че да спре да работи за определен период от време, като вашата ваканция. Е, винаги можете да промените параметрите (дати, час, име на файл) чрез бутона материали (Имоти).

Добавяне на макрос за отваряне на файл

Сега остава да закачим в нашата книга стартирането на макроса, от който се нуждаем, при събитието за отваряне на файл. За да направите това, отворете книгата и отидете до редактора на Visual Basic, като използвате клавишната комбинация Друг+F11 или бутони Visual Basic етикет предприемач (Разработчик). В прозореца, който се отваря в горния ляв ъгъл, трябва да намерите нашия файл в дървото и да щракнете двукратно, за да отворите модула Тази книга (Тази работна книга).

Ако не виждате този прозорец в редактора на Visual Basic, тогава можете да го отворите чрез менюто Изглед — Project Explorer.

В прозореца на модула, който се отваря, добавете манипулатор на събитие за отваряне на книга, като го изберете от падащите списъци в горната част Workbook и отворено, съответно:

На екрана трябва да се появи шаблон за процедура. Workbook_Open, където между редовете Частен суб и End Sub и трябва да вмъкнете тези VBA команди, които трябва да се изпълняват автоматично, когато тази работна книга на Excel се отвори, когато Планировчикът я отвори според графика. Ето някои полезни опции за овърклок:

• ThisWorkbook.RefreshAll – Обновява всички външни заявки за данни, заявки на Power Query и обобщени таблици. Най-универсалният вариант. Само не забравяйте да разрешите връзки към външни данни по подразбиране и да актуализирате връзките чрез Файл – Опции – Център за сигурност – Опции на центъра за сигурност – Външно съдържание, в противен случай, когато отворите книгата, ще се появи стандартно предупреждение и Excel, без да актуализира нищо, ще чака вашата благословия под формата на щракване върху бутона Разрешете съдържанието (Активиране на съдържание):

  

• ActiveWorkbook.Connections(“Име_на_връзка”).Опресняване — актуализиране на данни за връзката Connection_Name.
• Листове („Лист5“).Обобщени таблици(“Обобщена таблица1«).PivotCache.Refresh – актуализиране на една обобщена таблица с име PivotTable1 върху листа Sheet5.
• Приложение. Изчисли – преизчисляване на всички отворени работни книги на Excel.
• Application.CalculateFullRebuild – принудително преизчисляване на всички формули и повторно изграждане на всички зависимости между клетки във всички отворени работни книги (еквивалентно на повторно въвеждане на всички формули).
• Работни листове („Отчет“). Разпечатване – печатен лист Снимки.
• Обадете се на MyMacro – изпълнете макрос с име MyMacro.
• ThisWorkbook.Save – запазване на текущата книга
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – запишете книгата в папка D: Архив под името Снимки с дата и час, добавени към името.

Ако искате макросът да се изпълнява само когато файлът е отворен от Scheduler в 5:00 сутринта, а не всеки път, когато потребителят отвори работната книга през работния ден, тогава има смисъл да добавите проверка на времето, например:

Ако Format(Now, "hh:mm") = "05:00" тогава ThisWorkbook.RefreshAll  

Това е всичко. Не забравяйте да запазите работната си книга във формат с активирани макроси (xlsm или xlsb) и можете безопасно да затворите Excel и да се приберете вкъщи, оставяйки компютъра си включен. В даден момент (дори компютърът да е заключен) Scheduler ще стартира Excel и ще отвори посочения файл в него, а нашият макрос ще извърши програмираните действия. И ще се наслаждавате в леглото, докато тежкият ви отчет автоматично се преизчислява – красота! 🙂

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