Автоматическое формирование отчета по расписанию и сохранение его в эксель
Добрый день!
Есть Excel файл и нужно каждый день сохранять резервную копию в отдельную папку.
Можно настроить, что бы файл с названием "Отчет лаборатория" каждый день, к примеру в 15.00, сохранялся в папку "C:\Users\user\Desktop\В работе" в формате "Отчет лаборатория дата (в формате дд.мм.гггг)".
Не знаю, нужно прикладывать сам файл или нет.
Сам рабочий файл с совместным доступом.
Можно сделать так, что бы он сам не закрывался, а просто сохранялся как бы фоном, что бы оператор даже не замечал этого?
Добрый день!
Есть Excel файл и нужно каждый день сохранять резервную копию в отдельную папку.
Можно настроить, что бы файл с названием "Отчет лаборатория" каждый день, к примеру в 15.00, сохранялся в папку "C:\Users\user\Desktop\В работе" в формате "Отчет лаборатория дата (в формате дд.мм.гггг)".
Не знаю, нужно прикладывать сам файл или нет.
Сам рабочий файл с совместным доступом.
Можно сделать так, что бы он сам не закрывался, а просто сохранялся как бы фоном, что бы оператор даже не замечал этого? AVI
AVI, мне кажется, это это будет правильнее поручить Аутлуку (если он есть), потому что иного точного решения в Экселе, кроме постоянно работающего в фоновом режиме макроса, я не вижу. А это, как Вы понимаете, жуткий костыль. Можно ещё макрос, проверяющий текущее время вызывать во всех приходящих в голову событиях, но это не точно и тоже не по фэншую.
AVI, мне кажется, это это будет правильнее поручить Аутлуку (если он есть), потому что иного точного решения в Экселе, кроме постоянно работающего в фоновом режиме макроса, я не вижу. А это, как Вы понимаете, жуткий костыль. Можно ещё макрос, проверяющий текущее время вызывать во всех приходящих в голову событиях, но это не точно и тоже не по фэншую. StoTisteg
Думаю, что в 15:00 сохранять - не очень хорошая идея. Именно в это время файл может быть в процессе редактирования. Но, допустим, Вы сохранили. А потом в 15:05 в файле кто-то что-то изменил - копия получится неактуальной.
Поэтому предлагаю создавать копию после сохранения исходного файла. Если сохраняли несколько раз, то будет копия последнего варианта.
И да - формат ДД.ММ.ГГГГ неудобен для сортировки файлов по названию. Гораздо удобнее ГГГГ.ММ.ДДД
И еше - копию нужно сохранять не в ту папку, где лежит исходник. На это специально сделана проверка в макросе. Если такого не будет, то при открытии и сохранении этой копии файла для копии создастся своя копия, а нам этого не нужно. Конечно можно сохранять копию в xlsx, но я не знаю - есть ли у Вас в том файле еще макросы и нужно ли их сохранять.
В модуль книги
[vba]
Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'после сохранения файла
If Success Then 'если сохранение прошло успешно
wbp_ = "C:\Users\user\Desktop\В работе" 'папка для сохранения
' wbp_ = "g:\Моя\Стереть\Для копирования"'это моя папка для проверки работы макроса.
If ThisWorkbook.Path <> wbp_ Then 'если папка этого файла не равна папке для сохранения
Me.SaveCopyAs wbp_ & "\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xlsm" 'сохранить копию в ту папку
End If
End If
End Sub
Думаю, что в 15:00 сохранять - не очень хорошая идея. Именно в это время файл может быть в процессе редактирования. Но, допустим, Вы сохранили. А потом в 15:05 в файле кто-то что-то изменил - копия получится неактуальной.
Поэтому предлагаю создавать копию после сохранения исходного файла. Если сохраняли несколько раз, то будет копия последнего варианта.
И да - формат ДД.ММ.ГГГГ неудобен для сортировки файлов по названию. Гораздо удобнее ГГГГ.ММ.ДДД
И еше - копию нужно сохранять не в ту папку, где лежит исходник. На это специально сделана проверка в макросе. Если такого не будет, то при открытии и сохранении этой копии файла для копии создастся своя копия, а нам этого не нужно. Конечно можно сохранять копию в xlsx, но я не знаю - есть ли у Вас в том файле еще макросы и нужно ли их сохранять.
В модуль книги
[vba]
Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'после сохранения файла
If Success Then 'если сохранение прошло успешно
wbp_ = "C:\Users\user\Desktop\В работе" 'папка для сохранения
' wbp_ = "g:\Моя\Стереть\Для копирования"'это моя папка для проверки работы макроса.
If ThisWorkbook.Path <> wbp_ Then 'если папка этого файла не равна папке для сохранения
Me.SaveCopyAs wbp_ & "\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xlsm" 'сохранить копию в ту папку
End If
End If
End Sub
И да - формат ДД.ММ.ГГГГ неудобен для сортировки файлов по названию. Гораздо удобнее ГГГГ.ММ.ДДД
И еше - копию нужно сохранять не в ту папку, где лежит исходник. На это специально сделана проверка в макросе. Если такого не будет, то при открытии и сохранении этой копии файла для копии создастся своя копия, а нам этого не нужно. Конечно можно сохранять копию в xlsx, но я не знаю - есть ли у Вас в том файле еще макросы и нужно ли их сохранять.
В модуль книги
[vba]
Private Sub Workbook_AfterSave(ByVal Success As Boolean) 'после сохранения файла
If Success Then 'если сохранение прошло успешно
wbp_ = "C:\Users\user\Desktop\В работе" 'папка для сохранения
' wbp_ = "g:\Моя\Стереть\Для копирования"'это моя папка для проверки работы макроса.
If ThisWorkbook.Path <> wbp_ Then 'если папка этого файла не равна папке для сохранения
Me.SaveCopyAs wbp_ & "\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xlsm" 'сохранить копию в ту папку
End If
End If
End Sub
Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "save_"
End Sub
Sub save_()
ThisWorkbook.SaveAs "C:\Users\user\Desktop\В работе\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xls"
End Sub
Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "save_"
End Sub
Sub save_()
ThisWorkbook.SaveAs "C:\Users\user\Desktop\В работе\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xls"
End Sub
Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "save_"
End Sub
Sub save_()
ThisWorkbook.SaveAs "C:\Users\user\Desktop\В работе\Отчет лаборатория " & Format(Date, "YYYY_MM_DD") & ".xls"
End Sub
_Boroda_, Спасибо за советы!
Не критично когда именно можно сохранять. Этот файл в течение рабочего дня постоянно редактируется и за один день восстановить информацию не сложно. Сохраняется он, во-первых, потому что коллеги могу сами это сделать или забывают. И, во-вторых, потому что мало ли кто возьмет и удалит основной рабочий файл.
По большому счету не важен и формат даты. В целом, можно просто делать копию файла с заменой. важен сам факт того, что есть резервная копия там, где она точно не исчезнет. Тут есть вероятность того, что кто-то что-то внутри файла может понаудалять, поэтому и нужно хранить несколько копий, что бы коцаный файл случайно не заменил резервную копию.
В файле больше нет макросов и не планируется быть.
Адрес сохранения копии отличен от адреса самого исходника. Важно, что он для совместного доступа и важно, что исходник лежит в сетевой папке на серваке.
Поэтому предлагаю создавать копию после сохранения исходного файла. Если сохраняли несколько раз, то будет копия последнего варианта.
Это был бы идеальный вариант, но боюсь, что пользователи совсем не будут тыкать кнопочку "сохранить".
_Boroda_, Спасибо за советы!
Не критично когда именно можно сохранять. Этот файл в течение рабочего дня постоянно редактируется и за один день восстановить информацию не сложно. Сохраняется он, во-первых, потому что коллеги могу сами это сделать или забывают. И, во-вторых, потому что мало ли кто возьмет и удалит основной рабочий файл.
По большому счету не важен и формат даты. В целом, можно просто делать копию файла с заменой. важен сам факт того, что есть резервная копия там, где она точно не исчезнет. Тут есть вероятность того, что кто-то что-то внутри файла может понаудалять, поэтому и нужно хранить несколько копий, что бы коцаный файл случайно не заменил резервную копию.
В файле больше нет макросов и не планируется быть.
Адрес сохранения копии отличен от адреса самого исходника. Важно, что он для совместного доступа и важно, что исходник лежит в сетевой папке на серваке.
Поэтому предлагаю создавать копию после сохранения исходного файла. Если сохраняли несколько раз, то будет копия последнего варианта.
Это был бы идеальный вариант, но боюсь, что пользователи совсем не будут тыкать кнопочку "сохранить". AVI
Поэтому предлагаю создавать копию после сохранения исходного файла. Если сохраняли несколько раз, то будет копия последнего варианта.
Это был бы идеальный вариант, но боюсь, что пользователи совсем не будут тыкать кнопочку "сохранить". Автор - AVI
Дата добавления - 15.06.2018 в 15:15
Автоматическое формирование отчетов позволяет создавать отчеты для предопределенных периодов и, при необходимости, рассылать их указанным получателям, сохранять в базе данных, записывать в указанную папку.
Создание задания на автоматическое формирование отчетов выполняется через пункт меню Администрирование→Автоматическое формирование отчетов.
Задание на автоматическое формирование отчетов запускается автоматически по заданному пользователем расписанию, при условии что будет отмечен признак активности задания. Пользователь может настроить количество повторений задания для выбранного интервала запуска.
Для изменения параметров задания учетная запись должна иметь два разрешения: настройка автоматического формирования отчетов и редактирование отчетов.
Для просмотра отчетов сохраненных в базе данных учетная запись должна иметь два разрешения: печать новых отчетов и печать готовых отчетов.
Просмотр сохраненных отчетов выполняется через Центр печати
Редактирование заданий
Создание задания на автоматическое формирование отчетов выполняется через пункт меню Администрирование→Автоматическое формирование отчетов.
В открывшемся окне на панели инструментов надо нажать кнопку :
Форма с параметрами задания для автоматического создания отчетов содержит четыре вкладки:
- общие
- объекты учета
- рассылка
- формат рассылки и экспорта
Общие
На вкладке Общие обязательными параметрами является Наименование задания и Отчет:
От выбора типа отчета зависит тип сущности, для которой будет формироваться отчет.
Переключатель Задание активно используется для активации/деактивации автоматического формирования отчетов.
Если отмечен признак Задание активно, то автоматическое формирование отчетов будет выполняться в соответствии с заданным расписанием.
Если признак Задание активно не отмечен, то автоматическое формирование отчетов выполняться не будет до тех пор, пока его не активируют.
Если отмечен признак Необходимо подтверждение перед рассылкой, то автоматически сформированные отчетные документы будут разосланы только после подтверждения рассылки.
В расписании работы задания задается когда его надо выполнять: ежедневно, еженедельно, ежемесячно или в определенные дни месяца. В зависимости от того что выбрано необходимо:
- при ежедневном формировании указать с какой периодичностью (в днях);
- при еженедельном формировании указать по каким дням недели;
- при ежемесячном формирование указать по каким числам месяца;
- при формировании в определенные дни выбрать дни месяца.
В интервале указывается время суток, когда будет выполняться задание, и его периодичность повторения, например, каждые 30 минут. Признак повтора задания имеет смысл указывать, если в расписании автоматического опроса тоже задан аналогичный признак.
Записывать сформированные отчеты в базу данных или нет зависит от положения переключателя Сохранять отчеты в базе данных.
Записывать сформированные отчеты в папку или нет, зависит от переключателя Выгружать отчеты в папку. При сохранении отчета в папку надо указать полный путь к папке.
Важно
Учетная запись сервера (по умолчанию это NETWORK SERVICE ) должна иметь права на запись в указанную папку.
Совет
Пример задания сетевой папки: \\ИмяКомпьютера\C$\Temp\Reports
Если путь к папке не содержит наименование компьютера, то запись будет выполняться в локальную папку находящуюся на одном компьютере с сервером ЛЭРС УЧЁТ.
Пример задания локальной папки: C:\Temp\Reports
При сохранении отчета в папку, в которой уже есть ранее записанный файл, в зависимости от признака Перезаписывать существующие файлы, новый отчет либо заменит имеющийся, либо будет записан с новым наименованием. Новое наименование строится путем добавления к имеющемуся наименованию порядкового номера.
При нажатии на кнопку Изменить открывается форма для построения имени папки с использованием подстановочного блока
Подстановочный блок вставляется в текущую позицию шаблона двойным щелчком мыши.
При записи автоматически сформированного отчета в папку подстановочный блок заменяется на отформатированную дату записи.
Например, если задан шаблон C:\Test\ , то имя папки, в которую будут сохраняться отчеты сформированные 05.12.2017, имеет вид C:\Test\2017-12-05 .
Примечание
Если формат даты не задавать, то используется формат даты dd.MM.yyyy.
В строке Образец показывается полное наименование папки, которое будет использоваться для записи автоматически формируемых отчетов.
Важно
Если на компьютере, на котором установлен Сервер ЛЭРС УЧЁТ, нет папки с заданным именем, то она будет создана при условии наличия соответствующих разрешений для учетной записи сервера.
Объекты учёта
На вкладке Объекты выбираются объекты учета, для которых необходимо формировать отчеты. Можно выбирать либо конкретные объекты учета, либо группу объектов.
Какая конкретно отчетная форма будет использоваться определяется настройками объектов учета, точек учета и выбранным типом отчета.
Выбор объектов, по которым необходимо сформировать отчеты:
Для выбора объектов учета их необходимо отметить на списке и нажать кнопку Добавить выбранные.
Выбор группы, по всем объектам которой необходимо сформировать отчеты:
Группа объектов выбирается из выпадающего списка групп.
Примечание
Если выбирается группа объектов, то отчеты формируются для всех объектов входящих в группу на момент выполнения задания.
Для автоматического формирования отчетов по точкам учета надо отметить объекты учета и системы снабжения, которым принадлежат требуемые точки учета.
Важно
Если выбран отчет для точек учета, то автоматическое формирование отчетов выполняется для точек учета одновременно принадлежащих выбранному объекту и выбранной инженерной системе.
Для точек учета не удовлетворяющих этому условию отчет не формируется.
Если выбирается отчет для объекта учета, то он формируется независимо от выбранной инженерной системы.
Рассылка
На вкладке Рассылка настраивается рассылка и задаются получатели автоматически сформированных отчетов.
Для включения рассылки необходимо отметить признак 'Рассылать сформированные отчеты'.
Для выбора/удаления получателя используются кнопки на панели инструментов. Назначение каждой кнопки отображается на экране, если подвести к ней указатель мыши:
Для выбора получателей надо отметить те учетные записи, на электронную почту которых надо отправлять отчеты и нажать кнопку Добавить выбранные.
Отчеты отправляются на электронный адрес получателя автоматически после формирования.
Если у получателя не задан электронный адрес, то отчет не отправляется.
Для каждого из выбранных объектов формируется отдельный отчет.
Если получателю отправляются отчеты по нескольким объектам учета, то отчеты могут быть скомпонованы в одном файле или в разных. Разбивка на отдельные файлы выполняется по принципу: один отчет - один файл.
Примечание
Объединение отчетов в один файл поддерживается только для PDF и Excel форматов.
Если отмечен признак 'Упаковать вложения в архив (ZIP)', то все отчеты упаковываются в один zip-файл.
Файл(ы) с отчетами сохраняется во вложении письма отправляемого получателю.
Отчет может быть отправлен в одном из следующих форматов:
Документ Adobe Acrobat (pdf)
Специфичные параметры экспорта в PDF-файл задаются на форме:
Интервал страниц - определяет диапазон страниц, которые будут включены в результирующий файл. Для разделения номеров страниц используйте запятые. Чтобы установить диапазоны страниц, используйте дефисы.
Эти шрифты не встроены - указывает имена шрифтов, которые не должны быть встроены в результирующий файл, чтобы уменьшить размер файла. Для разделения шрифтов используйте точки с запятой.
Конвертировать изображения в Jpeg - указывает, следует ли преобразовывать все растровые изображения, содержащиеся в документе, в формат JPEG при экспорте в PDF.
Качество изображения - определяет уровень качества изображения документа. Чем выше качество, тем больше размер файла и наоборот.
PDF/A совместимость - указывает, следует ли включить совместимость документов со спецификацией PDF/A. Все версии PDF/A неявно запрещают шифрование. Все шрифты, используемые в документах PDF/A, должны быть встроены. Стандарты PDF/A-1b и PDF/A-2b не поддерживают вложения. Стандарт PDF/A-1b не поддерживает прозрачность, и альфа-канал в изображениях будет игнорироваться.
Защита паролем - эти параметры позволяют настроить параметры безопасности полученного PDF-файла (например,
включить защиту открытого документа, редактирование, печать и копирование, а также указать, какие изменения разрешены).
Цифровая подпись - если к отчету применяется сертификат X.509, его параметры можно сохранить с помощью этого свойства.
Дополнительные опции - приложение, автор, ключевые слова, предмет, название. Эти параметры определяют свойства PDF-документа.
Книга Excel (xlsx)
Специфичные параметры экспорта в Xlsx-файл задаются на форме:
Режим экспорта - определяет, как документ экспортируется в XLSX. В зависимости от установки признака Объединить отчеты в один файл отчеты будут сохраняться либо в разные листы одного Excel-файла, либо каждый отчет в отдельный Excel-файл.
Диапазон страниц - параметр не используется, т.к. все страницы одного отчета всегда записываются на один лист.
Название листа - указывает имя листа в созданном файле XLSX. Используется при экспорте в режиме Разбивать отчеты на отдельные файлы. При экспорте в режиме Объединить отчеты в один файл наименование листов определяется шаблоном заданным в параметрах отчета.
Режим экспорта текста - указывает, следует ли преобразовывать форматирование значения в строку собственного формата XLSX (если это возможно) или встраивать в значения ячеек в виде простого текста.
Показать линии сетки - указывает, должны ли линии сетки быть видимыми в результирующем файле XLSX.
Экспорт гиперссылок - Указывает, следует ли экспортировать гиперссылки в документ XLSX.
Экспортировать только данные - указывает, следует ли включить режим экспорта необработанных данных. В этом режиме в XLSX экспортируются только фактические данные документа, игнорируются такие элементы как изображения, графическое содержимое, настройки шрифта и внешнего вида.
Книга Excel 97-2003 (xls)
Режим экспорта - определяет, как документ экспортируется в XLS. В зависимости от установки признака Объединить отчеты в один файл отчеты будут сохраняться либо в разные листы одного Excel-файла, либо каждый отчет в отдельный Excel-файл.
Диапазон страниц - параметр не используется, т.к. все страницы одного отчета всегда записываются на один лист.
Название листа - указывает имя листа в созданном файле XLS. Используется при экспорте в режиме Разбивать отчеты на отдельные файлы. При экспорте в режиме Объединить отчеты в один файл наименование листов определяется шаблоном заданным в параметрах отчета.
Режим экспорта текста - указывает, следует ли преобразовывать форматирование значения в строку собственного формата XLS (если это возможно) или встраивать в значения ячеек в виде простого текста.
Показать линии сетки - указывает, должны ли линии сетки быть видимыми в результирующем файле XLS.
Экспорт гиперссылок - Указывает, следует ли экспортировать гиперссылки в документ XLS.
Экспортировать только данные - указывает, следует ли включить режим экспорта необработанных данных. В этом режиме в XLS экспортируются только фактические данные документа, игнорируются такие элементы как изображения, графическое содержимое, настройки шрифта и внешнего вида.
Текстовый документ (csv)
Специфичные параметры экспорта в Csv-файл задаются на форме:
Кодировка - определяет кодировку, используемую в экспортированном документе.
Режим экспорта текста - указывает, следует ли использовать форматирование полей данных в связанном источнике данных для ячеек в экспортированном документе. Если эта опция установлена на текст, все поля данных экспортируются в файл CSV в виде строк с соответствующим форматированием, встроенным в эти строки. Если для параметра задано значение «Значение», все форматирование будет потеряно в результирующем документе.
Разделитель текста - определяет символ, используемый для разделения текстовых элементов (по умолчанию точка с запятой).
Цитата строки с разделителями - указывает, следует ли помещать строки с разделителями в кавычки в экспортированном документе.
Форматированный текст (rtf)
Специфичные параметры экспорта в Rtf-файл задаются на форме:
Режим экспорта - определяет, как документ экспортируется в RTF. Доступны следующие режимы. Режим Один файл без разбивки на страницы позволяет экспортировать документ в один файл, не разбивая его на страницы. Режим Один файл с разбивкой на страницы позволяет экспортировать документ в один файл, разделенный на страницы. В этом режиме доступна опция Интервал страниц.
Интервал страниц - определяет диапазон страниц, которые будут включены в результирующий файл. Для разделения номеров страниц используйте запятые. Чтобы установить диапазоны страниц, используйте дефисы.
Экспорт водяных знаков - указывает, должен ли экспортируемый документ содержать водяные знаки (если они существуют).
Соавтор(ы): Jack Lloyd. Джек Ллойд — автор и редактор, пишущий о технологиях для wikiHow. Имеет более двух лет опыта в написании и редактировании статей на технические темы. Энтузиаст всего связанного с технологиями и учитель английского языка.
Из этой статьи вы узнаете, как в Microsoft Excel автоматизировать создание отчетов о данных. Здесь описывается, как в таблице Excel запросить данные из внешнего источника (MySQL, Postgres, Oracle и так далее) и создать отчет с помощью специального плагина, который свяжет таблицу Excel с внешними источниками данных.
Чтобы создать отчет о данных в таблице Excel, воспользуемся макросом, который к тому же позволяет сохранять отчеты в различных файловых форматах одним нажатием клавиши. Имейте в виду, что в Excel встроена функция, которая позволяет создавать макросы без необходимости писать программный код.
Если данные, о которых необходимо составить отчет, уже находятся в таблице Excel, автоматизируйте процесс создания отчета с помощью макросов. Макросы поддерживаются встроенной в Excel функцией, которая позволяет автоматизировать сложные и повторяющиеся задачи.
- На компьютере Mac вам, возможно, придется нажать «Файл» > «Пустая книга».
- Если у вас уже есть отчет Excel, который нужно автоматизировать, дважды щелкните по файлу отчета, чтобы открыть его в Excel.
Введите данные своей электронной таблицы, если необходимо. Если вы еще не добавили заголовки столбцов и числа, о которых нужно составить отчет, сделайте это сейчас.
- Windows — нажмите «Файл» > «Параметры» > «Настроить ленту» (на левой панели), установите флажок у «Разработчик» в нижнем правом углу (если вы не видите эту опцию, прокрутите страницу вниз), а затем нажмите «ОК». [1] X Источник информации
- Mac — нажмите «Excel» > «Параметры» > «Лента и панель инструментов», установите флажок у «Разработчик» в списке «Основные», а затем нажмите «Сохранить». [2] X Источник информации
Нажмите Разработчик . Вы найдете эту вкладку вверху окна Excel. Откроется панель инструментов «Разработчик».
- Например, если макрос будет строить график на основе доступных данных, назовите его «График1» или как-то аналогично.
- На компьютере Mac сочетанием клавиш будет ⌥ Option + ⌘ Command и еще одна клавиша (например, ⌥ Option + ⌘ Command + T ).
- Файл Excel нужно сохранить в специальном формате с поддержкой макросов.
Щелкните по OK . Это кнопка внизу окна. Настройки макроса будут сохранены и включится режим записи. С этого момента все ваши действия будут записываться (до момента, когда вы остановите запись).
- Например, чтобы выбрать данные и создать диаграмму, выделите нужные данные, нажмите «Вставка» в верхней части окна Excel, выберите тип диаграммы и отредактируйте диаграмму так, как нужно.
- Другой пример: чтобы создать макрос для построения графика из данных, которые находятся в ячейках с A1 по A12, щелкните по пустой ячейке, введите =СУММ(A1:A12) и нажмите ↵ Enter .
Щелкните по Остановить запись . Эта опция находится на панели инструментов «Разработчик». Запись будет остановлена, а все ваши действия, которые вы совершили во время записи, будут сохранены в виде отдельного макроса.
Весьма частый случай на практике: вам нужно запускать один или несколько ваших макросов в заданное время или с определенной периодичностью. Например, у вас есть большой и тяжелый отчет, который обновляется полчаса и вы хотели бы запускать обновление за полчаса до вашего прихода на работу утром. Или у вас есть макрос, который должен делать автоматическую рассылку сотрудникам с заданной периодичностью. Или, работая со сводной таблицей, вы хотите, чтобы она обновлялась "на лету" каждые 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) :
И, наконец, самое интересное - что именно нужно открывать:
-
Щелкнуть правой кнопкой мыши по иконке (ярлычку) запуска Excel на рабочем столе или в панели задач и выбрать команду Свойства (Properties) , а затем в открывшемся окне скопировать путь из строки Target:
Когда всё ввели, то жмем Далее и затем Готово (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) :
Если вы хотите, чтобы макрос выполнялся только при открытии файла Планировщиком в 5:00, а не каждый раз при открытии книги пользователем в течение рабочего дня, то имеет смысл добавить проверку на время, например:
Вот и всё. Не забудьте сохранить книгу в формате с поддержкой макросов (xlsm или xlsb) и можно смело закрывать Excel и отправляться домой, оставив компьютер включенным. В заданный момент (даже если ПК заблокирован) Планировщик запустит Excel и откроет в нём заданный файл, а наш макрос выполнит запрограммированные действия. А вы будете нежиться в постели, пока ваш тяжелый отчёт автоматически пересчитывается - красота! :)
Добрый день! Подскажите, пожалуйста, формулу для В6:AF16 Лист2. На Листе1 B1=СЕГОДНЯ. При сохранении файла, должен заполняться Лист2 на соответствующую дату. И так весь месяц. Заранее, спасибо.
Добрый день! Подскажите, пожалуйста, формулу для В6:AF16 Лист2. На Листе1 B1=СЕГОДНЯ. При сохранении файла, должен заполняться Лист2 на соответствующую дату. И так весь месяц. Заранее, спасибо. Ёжж
Если нужно, чтобы значения сохранялись - то только макросом (см. вложенный файл).
Если сделать на формулах, то они каждый раз при сохранении будут пересчитываться, и данные предыдущих дней будут обнуляться.
Если нужно, чтобы значения сохранялись - то только макросом (см. вложенный файл).
Если сделать на формулах, то они каждый раз при сохранении будут пересчитываться, и данные предыдущих дней будут обнуляться. Samaretz
Samaretz, доработал под себя, всё работает. Спасибо. Подскажите, что поменять в коде, что бы при редактировании в течении дня сохранялись последние данные на Листе2?
Samaretz, доработал под себя, всё работает. Спасибо. Подскажите, что поменять в коде, что бы при редактировании в течении дня сохранялись последние данные на Листе2? Ёжж
Ёжж, код вызывается при сохранении файла, поэтому каждый раз, когда файл будет сохраняться - данные с листа1 будут переноситься в соответствующую колонку листа2.
Соответственно, все, что Вам нужно сделать - просто сохранить файл сегодня (странно звучит, но, тем не менее, это так :-)); если сохраните файл завтра - то на листе2 останутся данные с последнего сегодняшнего сохранения, а текущие данные с листа1 будут перенесены уже в завтрашнюю колонку.
Ёжж, код вызывается при сохранении файла, поэтому каждый раз, когда файл будет сохраняться - данные с листа1 будут переноситься в соответствующую колонку листа2.
Соответственно, все, что Вам нужно сделать - просто сохранить файл сегодня (странно звучит, но, тем не менее, это так :-)); если сохраните файл завтра - то на листе2 останутся данные с последнего сегодняшнего сохранения, а текущие данные с листа1 будут перенесены уже в завтрашнюю колонку. Samaretz
Соответственно, все, что Вам нужно сделать - просто сохранить файл сегодня (странно звучит, но, тем не менее, это так :-)); если сохраните файл завтра - то на листе2 останутся данные с последнего сегодняшнего сохранения, а текущие данные с листа1 будут перенесены уже в завтрашнюю колонку. Автор - Samaretz
Дата добавления - 16.05.2016 в 16:56
Samaretz, при редактировании (удалении) в течении дня уже сохраненных данных, удаленные данные остаются. Как с этим бороться?
Samaretz, при редактировании (удалении) в течении дня уже сохраненных данных, удаленные данные остаются. Как с этим бороться? Ёжж
Кажется понял что Вы имеете ввиду. Немножко поправил код; посмотрите этот файлик.
PS. В этом случае код будет выводить нули на листе2 даже напротив названий групп (синие строчки); можно скрыть эти нули установкой формата ячейки в виде трех точек с запятой (;;;).
Кажется понял что Вы имеете ввиду. Немножко поправил код; посмотрите этот файлик.
PS. В этом случае код будет выводить нули на листе2 даже напротив названий групп (синие строчки); можно скрыть эти нули установкой формата ячейки в виде трех точек с запятой (;;;). Samaretz
PS. В этом случае код будет выводить нули на листе2 даже напротив названий групп (синие строчки); можно скрыть эти нули установкой формата ячейки в виде трех точек с запятой (;;;). Автор - Samaretz
Дата добавления - 17.05.2016 в 13:16
Читайте также: