Автоматизация создания документов в Excel

• Что такое Макрос?
• Создание макроса – практический пример
• Выполнение макроса в Excel
• Заглянем под капот: Как работает макрос?
• Добавим ещё один шаг к нашей задаче…

Excel располагает мощнейшей, но в то же время очень редко используемой, возможностью создавать автоматически выполняющиеся последовательности действий с помощью макросов. Макрос – идеальный выход, если Вы имеете дело с однотипной задачей, которая повторяется множество раз. Например, обработка данных или форматирование документов по стандартизированному шаблону. При этом Вам не требуется знание языков программирования.

Вам уже любопытно, что такое макрос, и как он работает? Тогда смелей вперёд – далее мы шаг за шагом проделаем весь процесс создания макроса вместе с Вами.

Что такое Макрос?

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования Visual Basic for Applications (VBA), сохранённый внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить со страницей HTML, тогда макрос – это аналог Javascript. То, что Javascript умеет делать с данными в формате HTML, находящимися на web-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.

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

• Применять стили и форматирование.
• Выполнять различные операции с числовыми и текстовыми данными.
• Использовать внешние источники данных (файлы баз данных, текстовые документы и т.д.)
• Создавать новый документ.
• Проделывать все вышеперечисленные действия в любой их комбинации.

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV. Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

Как уже было сказано, макрос – это код, написанный на языке программирования VBA. Но в Excel Вы можете создать программу, не написав и строчки кода, что мы и сделаем прямо сейчас.

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК.

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

Теперь, когда режим записи макроса включен, давайте займёмся нашей задачей. Первым делом, добавим заголовки для итоговых данных.

Далее, введите в ячейки формулы в соответствии с названиями заголовков (даны варианты формул для англоязычной и русифицированной версии Excel, адреса ячеек – всегда латинские буквы и цифры):

Теперь выделите ячейки с формулами и скопируйте их во все строки нашей таблицы, потянув за маркер автозаполнения.

После выполнения этого действия в каждой строке должны появиться соответствующие итоговые значения.

Далее, мы подведем итоги для всей таблицы, для этого делаем ещё несколько математических действий:

Соответственно:

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A, либо щелкните по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

Далее, изменим внешний вид заголовков столбцов и строк:

• Жирное начертание шрифта.
• Выравнивание по центру.
• Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

Чтобы использовать созданный макрос, нам нужно сохранить документ Excel в формате, который поддерживает макросы. Для начала необходимо удалить все данные из созданной нами таблицы, т.е. сделать из неё пустой шаблон. Дело в том, что в дальнейшем, работая с этим шаблоном, мы будем импортировать в него самые свежие и актуальные данные.

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все, которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

Теперь наш лист полностью очищен от всех данных, при этом макрос остался записан. Нам нужно сохранить книгу, как шаблон Excel с поддержкой макросов, который имеет расширение XLTM.

Важный момент! Если Вы сохраните файл с расширением XLTX, то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT, он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Выполнение макроса в Excel

Прежде чем раскрыть все возможности созданного Вами макроса, считаю правильным обратить внимание на пару важных моментов, касающихся макросов в целом:

• Макрос может нанести вред.
• Ещё раз прочти предыдущий пункт.

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

Чтобы запустить наш макрос, форматирующий данные, откройте файл шаблона, который мы создали в первой части этого урока. Если у Вас стандартные настройки безопасности, то при открытии файла сверху над таблицей появится предупреждение о том, что запуск макросов отключен, и кнопка, чтобы включить их выполнение. Так как шаблон мы сделали самостоятельно и себе мы доверяем, то нажимаем кнопку Enable Content (Включить содержимое).

Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).

При выполнении импорта данных из CSV-файла, возможно, Excel попросит Вас настроить некоторые параметры для правильной передачи данных в таблицу.

Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).

В открывшемся диалоговом окне мы увидим строку с именем нашего макроса FormatData. Выберите его и нажмите Run (Выполнить).

Когда макрос начнёт работать, Вы увидите, как табличный курсор прыгает с ячейки на ячейку. Через несколько секунд с данными будут проделаны те же операции, что и при записи макроса. Когда всё будет готово, таблица должна выглядеть так же, как и оригинал, который мы форматировали вручную, только с другими данными в ячейках.

Заглянем под капот: Как работает макрос?

Как уже не раз упоминалось, макрос – это программный код на языке программирования Visual Basic for Applications (VBA). Когда Вы включаете режим записи макроса, Excel фактически записывает каждое сделанное Вами действие в виде инструкций на языке VBA. По-простому, Excel пишет программный код вместо Вас.

Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).

Откроется окно Visual Basic for Applications, в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.

Добавим ещё один шаг к нашей задаче…

Представьте, что наш исходный файл с данными data.csv создаётся автоматически каким-то процессом и сохраняется на диске всегда в одном и том же месте. Например, C:\Data\data.csv – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:

1. Откройте файл шаблона, в котором мы сохранили макрос – FormatData.
2. Создайте новый макрос с именем LoadData.
3. В процессе записи макроса LoadData сделайте импорт данных из файла data.csv – как мы это делали в предыдущей части урока.
4. Когда импорт будет завершён, остановите запись макроса.
5. Удалите все данные из ячеек.
6. Сохраните файл, как шаблон Excel с поддержкой макросов (расширение XLTM).

Таким образом, запустив этот шаблон, Вы получаете доступ к двум макросам – один загружает данные, другой их форматирует.

Если есть желание заняться программированием, Вы можете объединить действия этих двух макросов в один – просто скопировав код из LoadData в начало кода FormatData.

Оцените качество статьи. Нам важно ваше мнение:

Итог: Изучите несколько трюков и советов, чтобы сохранить и закрыть все открытые книги Excel.

Уровень мастерства: Начинающий

Если вы используете Excel весь день каждый день, как я, то у вас, наверняка, открыто много рабочих книг одновременно. Я стараюсь дисциплинировать себя, но иногда это трудно. И в конце дня мы просто хотим закрыть все открытые рабочие тетради и пойти домой! Итак, этот пост содержит несколько советов по быстрому закрытию всех открытых файлов, чтобы помочь сократить трафик в час пик.

Совет № 1: Shift + кнопка закрытия окна

Самый быстрый способ закрыть все открытые книги — это удерживать клавишу Shift, нажимая кнопку «Закрыть окно». Кнопка «Закрыть окно» — это «х» в правом верхнем углу окна приложения

Если все открытые книги были сохранены, все они будут закрыты.

Если какая-либо из открытых книг НЕ была сохранена, вам будет предложено сохранить файл. Появится всплывающее окно, и вам придется нажимать Сохранить или Не сохранять для каждой несохраненной книги.

Окно также содержит кнопку Сохранить все. Вы можете нажать эту кнопку, чтобы сохранить все несохраненные книги за один шаг.

Нажатие на кнопку «Отмена» отменит всю операцию, а все еще открытые книги останутся открытыми.

Но что, если мы не хотим сохранить все или некоторые из открытых рабочих книг? Мы вернемся к этому в совет № 3.

Совет № 2: добавьте кнопку «Закрыть все» на панель быстрого доступа

Мы также можем добавить кнопку «Закрыть все» на панель быстрого доступа (QAT), чтобы закрыть все открытые книги. Это делает то же самое, что и сочетание клавиш Shift + Close Window.

Преимущество здесь в том, что мы можем использовать сочетание клавиш для нажатия кнопки. Доступ к кнопкам QAT можно получить с помощью сочетания клавиш, нажав клавишу Alt и номер, назначенный кнопке.

Чтобы добавить кнопку «Закрыть все» в QAT, откройте окно параметров Excel («Файл»> «Параметры») и следуйте инструкциям на изображении ниже.

После нажатия кнопки «Закрыть все» нам будет предложено то же окно, как описано в совете № 1.

Бонусный совет: мы также можем использовать сочетание клавиш Ctrl + W, чтобы закрыть одну книгу. Я упомянул этот совет в своем посте по 17 ярлыкам Excel на 2017 год.

Совет № 3: Закройте все открытые книги с помощью макроса

В этом разделе я поделюсь несколькими макросами, которые можно использовать для закрытия всех открытых рабочих книг. Макросы дают нам большую гибкость, когда мы хотим контролировать, какие книги сохраняются перед закрытием.

Вы можете скачать файл Excel, который содержит макросы здесь.

Save And Close All Files Macros.xlsm (15.8 KB)

Макрос для закрытия всех книг без сохранения

Если мы не хотим сохранять какие-либо открытые файлы, мы можем использовать следующий макрос.

Sub Close_All_Files_No_Save() ‘ Закройте все открытые книги и не сохраняйте Dim wb As Workbook ‘ Просмотрите каждую рабочую книгу For Each wb In Application.Workbooks ‘ Предотвратить рабочую книгу, которая содержит ‘ код от закрытия If wb.Name <> ThisWorkbook.Name Then ‘ Закройте книгу и не сохраняйте изменения wb.Close SaveChanges:=False End If Next wb End Sub

Как работает этот макрос?

Приведенный выше макрос просматривает все открытые книги на компьютере с помощью цикла For Next Loop. Он использует оператор If, чтобы проверить, является ли файл в цикле (wb.Name) НЕ (<>) файлом, который содержит код ThisWorkbook.Name.

Затем он использует метод Workbooks.Close (страница справки MSDN), чтобы закрыть книгу. Метод Close имеет необязательный параметр для SaveChanges. Мы можем установить значение True, чтобы сохранить изменения, и False, чтобы закрыть без сохранения.

Примечание. Этот макрос следует запускать только в том случае, если вы уверены, что не хотите сохранять изменения ни в одном из открытых файлов. Его нельзя отменить. Возможно, вы также захотите добавить окно сообщения Да / Нет, чтобы предупредить пользователя перед запуском этого макроса.

Макрос для сохранения всех, кроме новых несохраненных книг (блокнот)

В конце рабочего дня у меня обычно есть несколько книг, которые я хочу сохранить, и некоторые, которые я НЕ хочу сохранять. Я часто открываю новые рабочие книги, чтобы использовать файлы Excel для быстрых вычислений. Я называю их «блокноты». Я не хочу сохранять их, но хочу сохранить все другие открытые файлы.

Файлы — блокноты никогда не сохранялись, поэтому они еще не имеют расширения. Они называются: Book1, Book2, Book2 и т.д. Обратите внимание, что на снимке экрана отсутствует расширение файла (.xlsx, .xlsm).

Таким образом, мы можем добавить оператор If в наш макрос, чтобы увидеть, существует ли расширение файла. Если расширение файла существует, то мы сохраним файл. Если нет, то мы закроем без сохранения.

Sub Save_and_Close_All_Files_Except_ScratchPads() ‘ Закройте все открытые книги, кроме новых несохраненных файлов Dim wb As Workbook ‘ Просмотрите каждую рабочую книгу For Each wb In Application.Workbooks ‘ Предотвратить рабочую книгу, которая содержит ‘ код от закрытия If wb.Name <> ThisWorkbook.Name Then ‘ Проверьте, имеет ли имя файла расширение If InStr(Right(wb.Name, 5), «.xls») > 0 Then wb.Close SaveChanges:=True Else ‘ Не сохраняйте изменения, если это блокнот. wb.Close SaveChanges:=False End If End If Next wb End Sub

Этот макрос похож на макрос для закрытия всех книг. Разница в том, что мы используем оператор If, чтобы проверить, имеет ли файл расширение.

Функция Right используется для возврата 5 символов в конце имени файла. Если wb — это существующий файл, который уже был сохранен, то Right (wb.Name, 5) вернет «.xlsx» или «.xlsm».

Функция Instr (страница справки MSDN) используется для поиска строки в строке. Возвращает начальный номер строки. Таким образом, если Instr находит «.xls» в пределах «.xlsx» или «.xlsm», он возвращает 1. В противном случае он возвращает 0.

Оператор If проверяет, является ли возвращаемое значение из Instr больше 0, и сохраняет изменения при закрытии книги. В противном случае изменения не сохраняются для книг, которые НЕ содержат расширения.

Макрос для сохранения и закрытия всех книг и автоматического именования файлов

Иногда нам можем быть нужно сохранить некоторые из этих новых файлов (блокноты), но у нас нет времени, чтобы просмотреть каждый файл и сохранить его с именем.

Следующий макрос сохранит и закроет ВСЕ файлы и автоматически назовет новые файлы, которые не были сохранены. Он помещает все новые файлы (блокнот) в указанную вами папку.

Затем вы можете вернуться в эту папку, когда у вас будет свободное время для перемещения и переименования файлов.

Sub Save_and_Close_All_Files() ‘ Закройте все открытые книги, кроме новых несохраненных файлов Dim wb As Workbook Dim sPath As String ‘ Путь, где будут сохранены новые несохраненные файлы. ‘ Измените это на папку на вашем компьютере. Конец с обратной косой чертой \ sPath = «C:\Users\username\Documents\Excel Campus\Scratch Pads\» ‘ Просмотрите каждую рабочую книгу For Each wb In Application.Workbooks ‘ Предотвратить рабочую книгу, которая содержит ‘ код от закрытия If wb.Name <> ThisWorkbook.Name Then ‘ Проверьте, имеет ли имя файла расширение If InStr(Right(wb.Name, 5), «.xls») > 0 Then wb.Close SaveChanges:=True Else ‘ Сохранить блокноты в папке wb.Close SaveChanges:=True, _ Filename:=sPath &amp; wb.Name &amp; Format(Now, » yyyy-mm-dd-hhmm») End If End If Next wb End Sub

Этот макрос работает аналогично двум другим выше. Единственное отличие состоит в том, что он сохраняет новые файлы (блокноты) в папку на вашем компьютере.

Чтобы использовать этот макрос, вам нужно изменить переменную sPath на папку на вашем компьютере. Обязательно заканчивайте строку пути к файлу обратной косой чертой.

У метода Workbooks.Close есть еще один необязательный параметр (Filename), который позволяет нам указать имя файла, в котором мы хотим сохранить файл, если файл еще не был сохранен.

Если мы не используем параметр «Имя файла», Excel предложит нам меню «Сохранить как» для каждого файла. Таким образом, указание имени файла обходит это окно и может сэкономить нам много времени.

Значение параметра Filename объединяет строку для полного пути к файлу. Он также изменяет текущую дату и время до конца имени файла, используя функции «Формат» и «Сейчас». Функция Format в VBA аналогична функции TEXT в Excel и позволяет нам указать числовой формат. Функция «Сейчас» возвращает текущую дату и время на компьютере пользователя.

Я рекомендую добавить эти макросы в вашу личную книгу макросов, чтобы вы могли использовать их в любое время. Чтобы узнать больше, ознакомьтесь с моей статьей и видео о личной книге макросов.

У вас есть свои советы по сохранению и закрытию файлов?