Запуск макроса вовремя

Очень распространенный случай на практике: вам нужно запустить один или несколько макросов в заданное время или с определенной частотой. Например, у вас есть большой и тяжелый отчет, который обновляется в течение получаса, и вы хотите запустить обновление за полчаса до вашего прихода на работу утром. Или у вас есть макрос, который должен автоматически рассылать электронные письма сотрудникам с определенной частотой. Или при работе со сводной таблицей вы хотите, чтобы она обновлялась «на лету» каждые 10 секунд и так далее.

Давайте посмотрим, какие возможности Excel и Windows позволяют это реализовать.

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

Самый простой способ сделать это — использовать встроенный метод VBA. Приложение.OnTimeОбъект, запускающий указанный макрос в указанное время. Давайте разберемся в этом на практическом примере.

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

Dim TimeToRun 'глобальная переменная, в которой хранится время следующего выполнения 'это основной макрос Sub MyMacro() Application.Calculate 'пересчитать книгу Range("A1").Interior.ColorIndex = Int(Rnd() * 56) 'fill ячейка A1 случайного цвета :) Call 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  

Давайте разберемся, что здесь к чему.

Во-первых, нам нужна переменная, которая будет хранить время следующего запуска нашего макроса — я назвал ее ВремяToRun. Обратите внимание, что содержимое этой переменной должно быть доступно всем нашим последующим макросам, поэтому нам нужно сделать ее Глобальный, т.е. объявляем в самом начале модуля перед первым ниже.

Далее идет наш основной макрос МойМакрос, который выполнит основную задачу – пересчитать книгу методом Приложение.Рассчитать. Чтобы было понятнее, я добавил на лист в ячейку А1 формулу =TDATE(), которая отображает дату и время — при перерасчете ее содержимое обновится прямо на глазах (достаточно включить отображение секунд в ячейке формат). Для большего интереса я также добавил в макрос команду заполнения ячейки A1 случайно выбранным цветом (код цвета — целое число в диапазоне 0..56, которое генерируется функцией Rnd и округляет до целочисленной функции Int).

Макрос Следующий запуск добавляет к предыдущему значению ВремяToRun еще 3 секунды, а затем планирует следующий запуск основного макроса МойМакрос для этого нового времени. Конечно, на практике вы можете использовать любые другие нужные вам временные интервалы, задав аргументы функции ВремяЗначение в формате чч:мм:сс.

И, наконец, просто для удобства добавлено больше макросов последовательного запуска. Главная и его завершение Завершить. Последний использует четвертый аргумент метода, чтобы разорвать последовательность. В срок равный Ложь.

Итого, если запустить макрос Главная, то вся эта карусель закрутится, и мы увидим на листе следующую картинку:

Остановить последовательность можно, запустив соответственно макрос Завершить. Для удобства можно назначить обоим макросам сочетания клавиш с помощью команды Макросы – Параметры таб разработчик (Разработчик — Макросы — Параметры).

Запуск макроса по расписанию

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

В такой ситуации лучше использовать Планировщик Windows – программа, специально встроенная в любую версию Windows, способная выполнять заданные действия по расписанию. По сути, вы уже пользуетесь им, сами того не подозревая, ведь ваш ПК регулярно проверяет наличие обновлений, загружает новые антивирусные базы, синхронизирует облачные папки и т. д. Это все работа Планировщика. Итак, наша задача — добавить к существующим задачам еще одну, которая будет запускать Excel и открывать в нем указанный файл. И мы повесим наш макрос на событие Рабочая книга_Открыть этот файл – и проблема решена.

Хочу сразу предупредить, что для работы с Планировщиком могут потребоваться расширенные права пользователя, поэтому, если вы не можете найти описанные ниже команды и функции на своем рабочем компьютере в офисе, обратитесь за помощью к своим ИТ-специалистам.

Запуск планировщика

Итак, давайте запустим Планировщик. Для этого вы можете:

• Щелкните правой кнопкой мыши на кнопке Start , а затем выбрать Управление компьютером (Компьютерное управление)
• Выберите в Панели управления: Администрирование – Планировщик задач (Панель управления — Администрирование — Планировщик задач.)
• Выберите в главном меню Пуск – Стандартные – Системные инструменты – Планировщик задач.
• Нажмите сочетание клавиш Выигрыш+R, войти taskschd.msc и нажмите Enter

На экране должно появиться следующее окно (у меня английская версия, но можно и у вас):

Создать задачу

Чтобы создать новую задачу с помощью простого пошагового мастера, нажмите на ссылку Создайте простую задачу (Создать базовую задачу) в правой панели.

На первом шаге мастера введите название и описание создаваемой задачи:

Нажмите на кнопку Следующая (Следующий) и на следующем шаге выбираем триггер — частоту запуска или событие, которое запустит нашу задачу (например, включение компьютера):

Если вы выбрали Daily (Ежедневно), то на следующем шаге вам нужно будет выбрать конкретное время, дату начала последовательности и шаг (каждые 2-й день, 5-й день и т. д.):

Следующий шаг – выбор действия – Запуск программы (Запустить программу):

И наконец, самое интересное, что именно нужно открыть:

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

• Щелкните правой кнопкой мыши значок (ярлык) запуска Excel на рабочем столе или в панели задач и выберите команду Материалы (Свойства), а затем в открывшемся окне скопируйте путь из строки цель:

                       

• Откройте любую книгу Excel, затем откройте Диспетчер задач (Диспетчер задач) толкая Ctrl+другой+от и щелкнув правой кнопкой мыши по строке Microsoft Excel, выберите команду Материалы (Свойства). В открывшемся окне вы можете скопировать путь, не забыв добавить к нему обратную косую черту и EXCEL.EXE в конце:

               

• Откройте Excel, откройте редактор Visual Basic с помощью сочетания клавиш. другой+F11, открыть панель немедленная сочетание Ctrl+G, введите в него команду:

  ? Приложение.Путь

  … и нажмите Enter

  

  Скопируйте полученный путь, не забыв добавить к нему обратную косую черту и EXCEL.EXE в конце.

В Добавить аргументы (необязательно) (Добавьте аргументы (необязательно)) нужно вставить полный путь к книге с макросом, который мы хотим открыть.

Когда все введено, то нажмите Следующая , а затем Завершить (Заканчивать). Задачу следует добавить в общий список:

Созданным заданием удобно управлять с помощью кнопок справа. Здесь вы можете протестировать задачу, запустив ее сразу (бежать)не дожидаясь указанного времени. Вы можете временно деактивировать задачу (Запрещать)чтобы он прекратил работу на какой-то период времени, например, во время вашего отпуска. Ну а изменить параметры (даты, время, имя файла) вы всегда можете через кнопку Материалы (Свойства).

Добавьте макрос для открытия файла

Теперь осталось повесить в нашей книге запуск нужного нам макроса по событию открытия файла. Для этого откройте книгу и перейдите в редактор Visual Basic с помощью сочетания клавиш. другой+F11 или кнопки Визуальный Бейсик таб разработчик (Разработчик). В открывшемся окне в левом верхнем углу нужно найти наш файл на дереве и двойным щелчком мыши открыть модуль. Эта книга (Эта книга).

Если вы не видите это окно в редакторе Visual Basic, то открыть его можно через меню Просмотр — Обозреватель проекта.

В открывшемся окне модуля добавьте обработчик события открытия книги, выбрав его из выпадающих списков вверху. Workbook и Откройтесоответственно:

На экране должен появиться шаблон процедуры. Рабочая книга_Открыть, где между строк Частный суб и End Sub и вам нужно вставить те команды VBA, которые должны автоматически выполняться при открытии этой книги Excel, когда Планировщик открывает ее по расписанию. Вот несколько полезных опций для разгона:

• ThisWorkbook.RefreshAll – Обновляет все запросы к внешним данным, запросы Power Query и сводные таблицы. Самый универсальный вариант. Только не забудьте разрешить подключения к внешним данным по умолчанию и обновлять ссылки через Файл – Параметры – Центр управления безопасностью – Параметры центра управления безопасностью – Внешний контент, иначе при открытии книги появится стандартное предупреждение и Excel, ничего не обновляя, будет ждать вашего благословения в виде нажатия на кнопку Включить контент (Включить контент):

  

• ActiveWorkbook.Connections («Имя_соединения»). Обновить — обновление данных о соединении Connection_Name.
• Листы("Лист5").PivotTables("Сводная таблица1«).PivotCache.Обновить – обновление одной сводной таблицы с именем Сводная таблица1 на листе Sheet5.
• Приложение.Рассчитать – пересчет всех открытых книг Excel.
• Приложение.CalculateFullRebuild – принудительный пересчет всех формул и перестроение всех зависимостей между ячейками во всех открытых книгах (эквивалент повторного ввода всех формул).
• Рабочие листы («Отчет»). Распечатать. — распечатать лист Фото.
• Вызов MyMacro – запустить макрос с именем МойМакрос.
• ThisWorkbook.Сохранить – сохранить текущую книгу
  
• ThisWorkbooks.SaveAs «D:ArchiveReport» и заменить (теперь «:», «-») и «.xlsx» – сохранить книгу в папку Д:Архив под именем Фото с датой и временем, добавленными к имени.

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

Если Format(Now, «чч:мм») = «05:00», то ThisWorkbook.RefreshAll  

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

• Что такое макросы, как их использовать, куда вставлять код Visual Basic в Excel
• Как создать собственную надстройку макроса для Excel
• Как использовать личную книгу макросов в качестве библиотеки макросов в Excel