Своєчасне виконання макросу

Дуже поширений випадок на практиці: вам потрібно запустити один або кілька ваших макросів у певний час або з певною частотою. Наприклад, у вас є великий і важкий звіт, який оновлюється півгодини, і ви хочете запустити оновлення за півгодини до ранкового приходу на роботу. Або у вас є макрос, який має автоматично надсилати електронні листи співробітникам із заданою частотою. Або під час роботи зі зведеною таблицею ви хочете, щоб вона оновлювалася на льоту кожні 10 секунд тощо.

Давайте подивимося, що Excel і Windows мають можливість це реалізувати.

Запуск макросу із заданою частотою

Найпростіший спосіб зробити це — використати вбудований метод VBA Application.OnTime, який запускає вказаний макрос у вказаний час. Давайте розберемося в цьому на практичному прикладі.

Відкрийте редактор 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. Будь ласка, зверніть увагу, що вміст цієї змінної має бути доступним для всіх наших наступних макросів, тому нам потрібно зробити це в цілому, тобто оголосити на самому початку модуля перед першим нижче.

Далі йде наш основний макрос Мій макрос, яка виконуватиме основне завдання – перераховувати книгу за методом Застосування. Обчислити. Щоб було зрозуміліше, я додав на аркуш у комірку A1 формулу =TDATE(), яка відображає дату і час – при перерахунку її вміст оновиться прямо на очах (досить увімкнути відображення секунд у комірці формат). Для додаткового задоволення я також додав до макросу команду заповнення комірки A1 довільно вибраним кольором (код кольору є цілим числом у діапазоні 0..56, яке генерується функцією Rnd і округлює до цілої функції Int).

Macro NextRun додає до попереднього значення TimeToRun Ще 3 секунди, а потім запланує наступний запуск основного макросу Мій макрос для цього нового часу. Звичайно, на практиці ви можете використовувати будь-які інші часові інтервали, які вам потрібні, встановивши аргументи функції TimeValue у форматі гг:хх:сс.

І, нарешті, для зручності було додано більше макросів запуску послідовності. Головна та його завершення обробка. Останній використовує четвертий аргумент методу, щоб розірвати послідовність. В термін рівним Помилковий.

Усього, якщо запустити макрос Головна, то вся ця карусель закрутиться, і ми побачимо на аркуші таку картинку:

Ви можете зупинити послідовність, запустивши, відповідно, макрос обробка. Для зручності ви можете призначити комбінації клавіш для обох макросів за допомогою команди Макроси – параметри таб розробник (Розробник — Макроси — Параметри).

Запуск макросу за розкладом

Звичайно, все описане вище можливо тільки в тому випадку, якщо у вас запущений Microsoft Excel і в ньому відкритий наш файл. Тепер розглянемо більш складний випадок: вам потрібно запускати Excel за заданим розкладом, наприклад, кожен день о 5:00, відкривати в ньому великий і складний звіт і оновлювати в ньому всі підключення і запити, щоб він бути готовим до того часу, як ми прийдемо на роботу 🙂

У такій ситуації краще використовувати Планувальник Windows – програма, спеціально вбудована в будь-яку версію Windows, яка може виконувати певні дії за розкладом. Насправді ви вже використовуєте його, не підозрюючи про це, оскільки ваш ПК регулярно перевіряє наявність оновлень, завантажує нові антивірусні бази, синхронізує хмарні папки тощо. Це все робота Планувальника. Тому наше завдання додати до існуючих завдань ще одне, яке буде запускати Excel і відкривати в ньому вказаний файл. І ми повісимо наш макрос на подію Workbook_Open цей файл – і проблема вирішена.

Відразу хочу попередити, що для роботи з Планувальником можуть знадобитися розширені права користувача, тому, якщо ви не можете знайти команди та функції, описані нижче, на робочому комп’ютері в офісі, зверніться за допомогою до IT-фахівців.

Запуск планувальника

Отже, запустимо Планувальник. Для цього ви можете:

• Клацніть правою кнопкою миші на кнопці Start І вибирай управління комп'ютером (Керування комп'ютером)
• Виберіть на панелі керування: Адміністрування – Планувальник завдань (Панель керування — Адміністрування — Планувальник завдань)
• Виберіть у головному меню Пуск – Стандартні – Системні інструменти – Планувальник завдань
• Натисніть комбінацію клавіш Win+R, введіть taskschd.msc і натисніть Що натомість? Створіть віртуальну версію себе у

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

Створіть завдання

Щоб створити нове завдання за допомогою простого покрокового майстра, натисніть на посилання Створіть просте завдання (Створити основне завдання) на правій панелі.

На першому кроці майстра введіть назву та опис створюваного завдання:

Натисніть на кнопку МАЙБУТНІ (Далі) і на наступному кроці вибираємо тригер – частоту запуску або подію, яка буде запускати наше завдання (наприклад, включення комп’ютера):

Якщо ви вибрали щодня (Щодня), то на наступному кроці вам потрібно буде вибрати певний час, дату початку послідовності та крок (кожний 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, ви можете відкрити його через меню Перегляд — Провідник проекту.

У вікні модуля, що відкриється, додайте обробник події відкриття книги, вибравши його зі спадних списків угорі робочий зошит и відкритийвідповідно:

На екрані має з’явитися шаблон процедури. Workbook_Open, де між рядками Приватний суб и End Sub і вам потрібно вставити ті команди VBA, які повинні автоматично виконуватися під час відкриття цієї книги Excel, коли Планувальник відкриває її відповідно до розкладу. Ось кілька корисних опцій для розгону:

• This Workbook.RefreshAll – Оновлює всі запити зовнішніх даних, запити Power Query і зведені таблиці. Найбільш універсальний варіант. Тільки не забудьте дозволити підключення до зовнішніх даних за умовчанням і оновлювати посилання через Файл – Параметри – Центр безпеки – Параметри центру безпеки – Зовнішній вміст, інакше при відкритті книги з'явиться стандартне попередження і Excel, нічого не оновлюючи, буде чекати вашого благословення у вигляді натискання на кнопку Увімкнути вміст (Увімкнути вміст):

  

• ActiveWorkbook.Connections(“Connection_Name”).Оновити — оновлення даних про підключення Connection_Name.
• Аркуші («Аркуш5“).PivotTables(“Зведена таблиця1«).PivotCache.Refresh – оновлення однієї зведеної таблиці під назвою Зведена таблиця1 на аркуші Sheet5.
• Застосування. Обчислити – перерахунок усіх відкритих книг Excel.
• Application.CalculateFullRebuild – примусовий перерахунок усіх формул і перебудова всіх залежностей між клітинками у всіх відкритих книгах (еквівалентно повторному введенню всіх формул).
• Робочі аркуші («Звіт»). Роздрук – аркуш друку фотографії.
• Зателефонуйте в MyMacro – запустити макрос під назвою Мій макрос.
• Цей зошит. Зберегти – зберегти поточну книгу
  
• ThisWorkbooks.SaveAs “D:ArchiveReport” & Replace(Now, “:”, “-“) & “.xlsx” – зберегти книгу в папку D: Архів під назвою фотографії з датою та часом, доданими до імені.

Якщо ви хочете, щоб макрос виконувався лише тоді, коли файл відкривається Планувальником о 5:00 ранку, а не кожного разу, коли користувач відкриває книгу протягом робочого дня, то має сенс додати перевірку часу, наприклад:

If Format(Now, "hh:mm") = "05:00" Then ThisWorkbook.RefreshAll  

Це все. Не забудьте зберегти робочу книгу у форматі з підтримкою макросів (xlsm або xlsb), і ви зможете безпечно закрити Excel і піти додому, залишивши комп’ютер увімкненим. У певний момент (навіть якщо ПК заблокований) Планувальник запустить Excel і відкриє в ньому вказаний файл, а наш макрос виконає запрограмовані дії. А ви розкошуєте в ліжку, поки ваш важкий звіт автоматично перераховується – краса! 🙂

• Що таке макроси, як ними користуватися, куди в Excel вставляти код Visual Basic
• Як створити власну надбудову макросів для Excel
• Як використовувати особисту книгу макросів як бібліотеку для ваших макросів у Excel