Шаг 248.
VBA в MSExcel. Автоматизация формирования текстовых документов на примере типового договора купли-продажи. Создание базы заключенных договоров

    На этом шаге мы рассмотрим создание базы заключенных договоров.

    После автоматизации заполнения типового договора с изменяющимися данными, необходимо проводить и автоматическую регистрацию этих договоров. Регистрация договоров будет осуществляться в табличной базе данных на рабочем листе Архив, в котором будут фиксироваться изменяющиеся параметры, состоящие из:

1. Порядкового номера договора в базе данных (столбец А);
2. Присвоенного договору номера (столбец В);
3. Преамбулы, которая состоит из:
   • места заключения (столбец С);
   • даты (столбец D);
   • кто, с кем, в чьем лице и на основании какого документа заключил этот договор (столбец E);
4. Предмета договора (столбец F);
5. Условий договора (столбец G);
6. Реквизитов Продавца (столбцы Н:Q) и Покупателя (столбцы R:AA).

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

Подпрограмма автоматизации операции регистрации договоров

    Для переноса всех данных с одного рабочего листа на другой за одно действие, эти данные необходимо сгруппировать в одном месте в форме, удобной для переноса - диапазоне ячеек, расположенных в одной строке или одном столбце. Наиболее простой способ группировки - задание в этом диапазоне ссылок на ячейки, содержащие нужную информацию. Для нашего случая проще организовать такой массив данных в одном столбце, например ВА, выделив для этого диапазон ячеек ВА1:ВА26.

Рис.1. Последовательность операций для переноса данных регистрируемого договора в базу данных

    На рисунке 2 показан фрагмент рабочего листа Договор с массивом переносимых данных, собранных в области ячеек ВА1:ВА26. В столбце ВВ, в качестве примера приведены значения, полученные с помощью этих ссылок.

Рис.2. Массив переменных данных договоров, переносимых в базу данных

Подпрограмма переноса информации в базу данных

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

Механическая запись макроса

Последовательность записи макроса состоит из следующих действий:

• начните запись макроса, находясь на рабочем листе Договор;
• на листе Договор выделите диапазон ячеек ВА1:ВА26 и скопируйте в буфер обмена;
• перейдите на лист Архив, поместите табличный курсор в свободной строке в ячейку столбца В и выполните команду Правка | Специальная вставка;
• в диалоговом окне Специальная вставка активизируйте переключатель Значения, отметьте опцию Транспонировать и нажмите кнопку ОК;
• перейдите на рабочий лист Договор, установите табличный курсор в ячейку А1 и остановите запись макроса.

    После записи макроса создайте кнопку с текстом Внести в Архив и назначьте ей выполнение созданного макроса.

Редактирование макроса переноса данных

Редактирование макроса производится в Редакторе Visual Basic и заключается в дополнении текста кода VBA элементами, которые невозможно записать в механическом режиме.

    Метод определения первой пустой строки рассматривался в прошлом примере и заключается в определении в столбце А активного рабочего листа (ActiveSheet) количества заполненных ячеек с помощью функции СЧЕТЗ (CountA), к которому добавляется значение 1. Полученный результат присваивается переменной Row. На этом же активном рабочем листе выделяется ячейка, имеющая адрес:

• строка равна значению переменной Row;
• столбец В, потому что во второй аргумент адреса равен значению 2.

    Замените ссылку на ячейку столбца В после перехода на лист Архив строками кода VBA:

  Row = Application.CountA(ActiveSheet.Range("A:A")) + 1
  ActiveSheet.Cells(Row, 2).Select

    В строке кода VBA вставки через диалоговое окно Специальная вставка оставьте только аргументы вставки значений и транспонирования, после которой добавьте две строки:

  ActiveCell.Offset(0, -1).Select
  ActiveCell.Value = Application.Max(ActiveSheet.Range("A:A")) + 1

    Первая строка встречалась ранее. Ее задача - перемещение относительно активной ячейки по той же строке на один столбец влево.

    Задача второй строки - присвоение содержимому активной ячейке, значения определенного функцией МАКС (Max) в диапазоне столбца А и увеличенного на единицу. Преимущества такой записи перед вводом формулы и последующей заменой ее на вычисленное значение очевидно - меньшее количество действий, а значит и большее быстродействие.

Рис.3. Подпрограмма переноса данных на лист Архив

    На следующем шаге мы рассмотрим просмотр предыдущих договоров.

Предыдущий шаг Содержание Следующий шаг