Генерация документов и отчетов из реестра или базы данных Excel с помощью кода VBA для просмотра, анализа и вывода на печать. Создание, заполнение, удаление.
Нет смысла хранить множество документов и отчетов в отдельных файлах, если существуют реестры и базы данных Excel, в которых уже есть вся необходимая информация. Каждый документ или отчет можно сгенерировать с помощью кода VBA в виде временного файла, просмотреть его, проанализировать, распечатать. Затем удалить его, а при необходимости сформировать снова.
Создание документа или отчета из шаблона
Допустим, у нас есть реестр документов или база данных Excel с какой-либо информацией. Чтобы создать документ или отчет и заполнить его данными, необходим его шаблон (печатная форма).
Шаблон создаем вручную на отдельном рабочем листе или с помощью кода VBA при заполнении документа. Способы создания шаблона определяют место его хранения:
- В коде VBA. Такой способ возможен для несложных печатных форм.
- В рабочей книге Excel с реестром или базой данных на скрытом листе.
- В отдельном файле, который не обязательно сохранять как шаблон Excel (*.xlt*).
Я предпочитаю создавать шаблон вручную и хранить его на скрытом листе в книге с реестром или базой данных. Такой способ подходит для подавляющего большинства случаев.
Из редких исключений можно назвать печать ценников на основании товарной накладной, сохраненной в Excel. Шаблон ценника и программный код VBA приходится хранить в отдельном файле, по команде из которого открывается и обрабатывается товарная накладная.
Назовем лист с шаблоном документа или отчета — «Shablon». Для создания новой печатной формы скрытый лист с шаблоном необходимо скопировать:
|
1 2 3 4 5 |
With Worksheets("Shablon") .Visible = True .Copy After:=Worksheets(Worksheets.Count) .Visible = False End With |
Данный код вставит новый лист с шаблоном в ту же книгу после всех имеющихся листов. Чтобы шаблон скопировать в новую книгу, заменяем строку .Copy After:=Worksheets(Worksheets.Count) на .Copy. Печатная форма откроется в новом файле.
Сразу после создания, обращаться к новому листу можно как к активному (ActiveSheet), даже в том случае, если он скопирован в новую книгу. Чтобы воспользоваться этой возможностью, порядок действий в программе должен быть следующим:
- Копирование информации в переменную массива из реестра или базы данных.
- Создание нового документа или отчета из шаблона.
- Заполнение документа или отчета, обращаясь к нему как к ActiveSheet.
Копирование информации и заполнение формы
Копирование информации
Информация о документе обычно хранится в реестре или базе данных Excel в одной строке. Информация для отчета может располагаться как в одной, так и в нескольких или во многих строках.
Если информация нужна из одной строки, перед запуском кода VBA следует выделить любую ячейку в строке с данными. Тогда в ходе выполнения программы можно будет определить номер строки по активной ячейке и извлечь из нее информацию.
Допустим, что лист называется «Reestr», а необходимая информация расположена в десяти первых ячейках каждой строки:
|
1 2 3 |
Dim nomer As Long, massiv() As Variant nomer = ActiveCell.Row massiv = Range(Cells(nomer, 1), Cells(nomer, 10)) |
Если для отчета требуется информация из нескольких строк, можно выделить их вертикально по любому столбцу и использовать следующий код:
|
1 2 3 4 |
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. Если новый документ или отчет создан в той же книге, ее можно автоматически сохранить:
|
1 |
ThisWorkbook.Save |
Чтобы не наблюдать за мерцаниями экрана во время выполнения процедуры VBA Excel, можно временно отключить его обновления:
|
1 2 3 4 5 |
Sub GeneratsiyaDokumenta() Application.ScreenUpdating = False ------- операторы ------- Application.ScreenUpdating = True End Sub |
Удаление печатной формы
Если лист документа или отчета временно сохраняется в книгу с реестром или базой данных, можно предусмотреть его простое удаление без выведения дополнительного диалогового окна.
Для этого необходимо на лист шаблона правее печатной формы вставить кнопку из коллекции «Элементы управления ActiveX». Дважды кликнуть по ней и в открывшемся модуле листа внутрь автоматически сгенерированного объявления процедуры вставить строку Call UdaleniyeLista:
|
1 2 3 |
Sub CommandButton1_Click() Call UdaleniyeLista End Sub |
Затем в модуль текущей книги добавляем процедуру:
|
1 2 3 4 5 |
Sub UdaleniyeLista() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub |
Кнопка вместе с шаблоном будет копироваться в новую печатную форму. При нажатии кнопки активный лист будет удаляться кодом VBA без предупреждения от Excel.