Перейти к содержимому

VBA Excel. Генерация документов и отчетов

Генерация документов и отчетов из реестра или базы данных Excel с помощью кода VBA для просмотра, анализа и вывода на печать. Создание, заполнение, удаление.

  1. Создание документа или отчета из шаблона
  2. Копирование информации и заполнение формы

Нет смысла хранить множество документов и отчетов в отдельных файлах, если существуют реестры и базы данных Excel, в которых уже есть вся необходимая информация. Каждый документ или отчет можно сгенерировать с помощью кода VBA в виде временного файла, просмотреть его, проанализировать, распечатать. Затем удалить его, а при необходимости сформировать снова.

Создание документа или отчета из шаблона

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

Шаблон создаем вручную на отдельном рабочем листе или с помощью кода VBA при заполнении документа. Способы создания шаблона определяют место его хранения:

  • В коде VBA. Такой способ возможен для несложных печатных форм.
  • В рабочей книге Excel с реестром или базой данных на скрытом листе.
  • В отдельном файле, который не обязательно сохранять как шаблон Excel (*.xlt*).

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

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

Назовем лист с шаблоном документа или отчета - «Shablon». Для создания новой печатной формы скрытый лист с шаблоном необходимо скопировать:

With Worksheets("Shablon")
  .Visible = True
  .Copy After:=Worksheets(Worksheets.Count)
  .Visible = False
End With

Данный код вставит новый лист с шаблоном в ту же книгу после всех имеющихся листов. Чтобы шаблон скопировать в новую книгу, заменяем строку .Copy After:=Worksheets(Worksheets.Count) на .Copy. Печатная форма откроется в новом файле.

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

  1. Копирование информации в переменную массива из реестра или базы данных.
  2. Создание нового документа или отчета из шаблона.
  3. Заполнение документа или отчета, обращаясь к нему как к ActiveSheet.

Копирование информации и заполнение формы

Копирование информации

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

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

Допустим, что лист называется «Reestr», а необходимая информация расположена в десяти первых ячейках каждой строки:

Dim nomer As Long, massiv() As Variant
nomer = ActiveCell.Row
massiv = Range(Cells(nomer, 1), Cells(nomer, 10))

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

Dim nomer1 As Long, nomer2 As Long, massiv() As Variant
nomer1 = Selection.Cells(1).Row
nomer2 = Selection.Cells(Selection.Cells.Count).Row
massiv = Range(Cells(nomer1, 1), Cells(nomer2, 10))

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

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

Создание и заполнение формы

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

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

ThisWorkbook.Save

Чтобы не наблюдать за мерцаниями экрана во время выполнения процедуры VBA Excel, можно временно отключить его обновления:

Sub GeneratsiyaDokumenta()
Application.ScreenUpdating = False
------- операторы -------
Application.ScreenUpdating = True
End Sub

Удаление печатной формы

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

Для этого необходимо на лист шаблона правее печатной формы вставить кнопку из коллекции «Элементы управления ActiveX». Дважды кликнуть по ней и в открывшемся модуле листа внутрь автоматически сгенерированного объявления процедуры вставить строку Call UdaleniyeLista:

Sub CommandButton1_Click()
  Call UdaleniyeLista
End Sub

Затем в модуль текущей книги добавляем процедуру:

Sub UdaleniyeLista()
  Application.DisplayAlerts = False
    ActiveSheet.Delete
  Application.DisplayAlerts = True
End Sub

Кнопка вместе с шаблоном будет копироваться в новую печатную форму. При нажатии кнопки активный лист будет удаляться кодом VBA без предупреждения от Excel.

Добавить комментарий

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