Excel запуск макроса по расписанию

В Excel есть инструментарий для запуска макроса по расписанию, — Application.Ontime
При помощи этого метода можно запускать макрос с заданным интервалом (например, раз в секунду)

Но есть и другой вариант, — использование объекта htmlfile:
(код надо поместить в модуль ЭтаКнига — ThisWorkbook)

  • 14156 просмотров

Комментарии

Отправить комментарий

Не получается применить макрос? Не удаётся изменить код под свои нужды?

Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.

ПОПУЛЯРНЫЕ ПРОГРАММЫ

© 2009-2019 Игорь Вахненко

Запуск заданий по расписанию в Windows

Краткое описание

В статье рассказывается о возможностях и способах применения стандартного планировщика Windows в связке с преднастроенными макросами и файлами Excel.

Подробное описание

Иногда бывает необходимо, чтобы компьютер выполнял определенные действия без вашего участия. Например, раз в сутки копировался какой-то файл, что-то удалялось, проверялось, запускался некий exe-файл, отправлялись письма, всплывали уведомления и тп. Можно было бы написать vba-макрос, который бы все это делал (макрос для Outlook, Excel и тп). Но чтобы такой макрос работал, нужно держать открытым тот же Outlook, Excel и тд.

Есть решение горозда проще и удобнее — стандартный планировщик заданий Windows.

1. Открываете планировщик:

2. Нажимаете "Создать простую задачу":

3. Вводите необходимые параметры (имя задания, время, частота и периодичность запуска и некоторые др.) и указываете, что нужно сделать:

Если выбрать пункт "Запустить программу", то на следующем шаге вам будет предложено выбрать, какую программу или сценарий вы хотите запустить. Кроме того, вы также можете указать дополнительные аргументы запуска:

Именно пункт "Запустить программу" дает нам очень много свободы действий, ведь в качестве скрипта или программы может быть все, что угодно: vbs/cmd скрипт, excel файл, любое другое приложение. Плюс, можно задать дополнительные параметры (аргументы) запуска.

И что важно, наша операционная система сама будет отслеживать время и необходимость запуска без дополнительных усилий с нашей стороны. Требование одно — должен быть включен компьютер. Если наступило время выполнения задачи, а ПК выключен, то задача будет исполнена, когда компьютер заработает (этот пункт можно настроить: либо игнорировать пропущенную задачу, либо запускать после включения ПК).

Вот такая вроде простая, но многими не востребованная вещь.

Для продвинутых пользователей есть возможность настройки планировщика из командной строки. Кстати, настройка из командной строки может быть очень полезна, когда вы, например, выполняете какой-то макрос в экселе и вам требуется выполнить ряд отложенных действий, а потом снова запустить макрос на продолжение, но уже со следующего этапа. С планировщиком это решается очень просто:

  1. макрос выполняет свою первую часть,
  2. завершает первую часть, через командную строку создает отложенное задание и прекращает свою работу (вплоть до закрытия файла excel),
  3. планировщик отслеживает время запуска нужного задания, запускает задание (например, проверка наличия файла в сети),
  4. по окончании проверки запускает следующее задание — запуск нашего исходного макроса, но уже со второго этапа и с полученным значениям статуса выполнения нашего отложенного задания.

И так сколько угодно раз, исходя из настроек вашего макроса и параметров планировщика.

Читайте также:  Дигма или престижио планшеты что лучше

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

Давайте разберемся с тем, какие в Excel и Windows есть возможности для реализации подобного.

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

Для этого проще всего использовать встроенный в VBA метод Application.OnTime, который запускает заданный макрос в указанный момент времени. Давайте разберемся с этим на практическом примере.

Откройте редактор Visual Basic одноименной кнопкой на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F11 , вставьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

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

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

Дальше идет наш главный макрос MyMacro, который будет выполнять основную задачу — пересчитывать книгу с помощью метода Application.Calculate. Чтобы было нагляднее, я добавил на лист в ячейку А1 формулу =ТДАТА(), которая выводит дату и время — при пересчете её содержимое будет обновляться прямо у нас на глазах (только включите отображение секунд в формате ячейки). Для дополнительного веселья я добавил в макрос еще и команду заливки ячейки А1 случайно выбранным цветом (код цвета — это целое числов в диапазоне 0..56, которое генерит функция Rnd и округляет до целого числа функция Int).

Макрос NextRun добавляет к предыдущему значению TimeToRun еще 3 секунды и затем назначает следующий запуск главного макроса MyMacro на это новое время. Само-собой, на практике можно использовать любые другие нужные вам временные интервалы, задавая аргументы функции TimeValue в формате "чч:мм:сс".

Ну и, наконец, просто для удобства добавлены еще макросы запуска последовательности Start и её завершения Finish. В последнем из них для прерывания последовательности используется четвёртый аргумент метода OnTime равный False.

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

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

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

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

В такой ситуации лучше воспользоваться Планировщиком Windows — специально встроенной в любую версию Windows программой, которая умеет по расписанию выполнять заданные действия. По факту, вы уже используете его, сами того не зная, ведь ваш ПК регулярно проверяет обновления, качает новые антивирусные базы, синхронизирует облачные папки и т.д. — это всё работа Планировщика. Так что наша задача сводится к тому, чтобы добавить к уже имеющимся задачам ещё одну, которая будет запускать Excel и открывать в нём заданный файл. А мы с вами повесим наш макрос на событие Workbook_Open этого файла — и задача решена.

Читайте также:  Собрал компьютер а он не запускается

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

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

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

  • Щелкнуть правой кнопкой мыши по кнопке Пуск и выбрать Управление компьютером (Computer management)
  • Выбрать в Панели управления: Администрирование — Планировщик заданий (Control Panel — Administrative Tools — Task Scheduler )
  • Выбрать в главном меню Пуск — Стандартные — Служебные — Планировщик заданий
  • Нажать сочетание клавиш Win + R , ввести taskschd.msc и нажать Enter

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

Создаем задачу

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

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

Жмем на кнопку Далее (Next) и на следующем шаге выбираем триггер — частоту запуска или событие, которое будет запускать нашу задачу (например, включение компьютера):

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

Следующий шаг — выбираем действие — Запуск программы (Start a program) :

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

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

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

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

Открыть Excel, открыть редактор Visual Basic сочетанием клавиш Alt + F11 , открыть панель Immediate сочетанием Ctrl + G , ввести в неё команду:
? Application.Path
. и нажать на Enter


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

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

Когда всё ввели, то жмем Далее и затем Готово (Finish) . Задача должна добавиться в общий список:

Читайте также:  Как помыть кожаные стельки

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

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

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

Если у вас в редакторе Visual Basic не видно этого окна, то его можно открыть через меню View — Project Explorer.

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

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

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

  • ActiveWorkbook.Connections("Имя_Соединения").Refresh — обновление данных по соединению Имя_Соединения.
  • Sheets("Лист5").PivotTables("СводнаяТаблица1").PivotCache.Refresh — обновление отдельно взятой сводной таблицы с именем СводнаяТаблица1 на листе Лист5.
  • Application.Calculate — пересчет всех открытых книг Excel.
  • Application.CalculateFullRebuild — принудительный пересчет всех формул и перестроение всех зависимостей между ячейками во всех открытых книгах (равносильно повторному вводу всех формул).
  • Worksheets("Отчет").PrintOut — распечатать лист Отчет.
  • Call MyMacro — запустить макрос с именем MyMacro.
  • ThisWorkbook.Save — сохранить текущую книгу
  • ThisWorkbooks.SaveAs "D:АрхивОтчет " & Replace(Now, ":", "-") & ".xlsx" — сохранить книгу в папку D:Архив под именем Отчет с добавлением к имени даты и времени.
  • Если вы хотите, чтобы макрос выполнялся только при открытии файла Планировщиком в 5:00, а не каждый раз при открытии книги пользователем в течение рабочего дня, то имеет смысл добавить проверку на время, например:

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