- •1 Договор
- •1.1 Структура договора и его основные условия
- •1.2 Вводная часть (преамбула) договора
- •1.3 Предмет договора, права и обязанности сторон
- •1.4 Дополнительные условия договора
- •1.5 Прочие условия договора
- •2 Практическая часть
- •Ход работы
- •Часть 1 Создание автоматизированного приложения в Excel (4 часа)
- •1.1 База данных контрагентов
- •1.2 Корректировка содержимого договора
- •1.3 Область ввода данных
- •2 Автоматизация ввода даты заключения договора, срока оплаты по нему и формирования номера договора
- •2.1 Дата заключения договора
- •2.2 Определение номера договор и сроков оплаты по нему
- •2.3 Формирование текста срока оплаты по договору
- •2.4 Генерация текста номера договора
- •2.5 Создание элементов управления Переключатель
- •2.6 Создание элемента управления Флажок
- •2.7 Назначение макросов элементам управления
- •2.7.1 Ввод формул создания номера договора и сроков оплаты по нему
- •2.7.2 Ввод формулы заключения договора
- •2.7.3 Присвоение макросов элементам управления
- •2.8 Область формирования элементов договора
- •2.9 Номер договора, дата и место его заключения
- •2.10 Преамбула договора
- •2.11 Пункты договора
- •2.12 Реквизиты
- •Часть 2 Создание интерактивного текстового документа Договор (4 часа)
- •3 Создание интерактивного текстового документа Договор
- •3.1 Создание связи между Excel и Word
- •3.2 Создание базы заключенных договоров
- •3.3 Подпрограмма автоматизации операции регистрации договоров
- •3.3.1 Создание массива переносимых данных
- •3.3.2 Подпрограмма переноса информации в базу данных
- •3.3.3 Механическая запись макроса
- •3.3.4 Редактирование макроса переноса данных
- •3.4 Просмотр предыдущих договоров из базы данных их регистрации
- •3.5 Перемещение по листам рабочей книги и открытие текстовых документов
- •3.6 Командный способ назначения гиперссылки
- •3.6.1 Открытие текстового документа или переход к нему
- •3.6.2 Перемещение по рабочей книге
- •3.6.3 Изменение гиперссылки
- •3.7 Использование функции гиперссылка
- •Порядок выполнения лабораторной работы
- •Содержание отчета
3.2 Создание базы заключенных договоров
После автоматизации заполнения типового договора с изменяющимися данными, необходимо проводить и автоматическую регистрацию этих договоров. Регистрация договоров будет осуществляться в табличной базе данных на рабочем листе Архив, в котором будут фиксироваться изменяющиеся параметры, состоящие из:
Порядковый номер договора в базе данных (столбец А);
Присвоенный договора номер (столбец В);
Преамбула, которая состоит из:
Место заключения (столбец С);
Дата (столбец D);
Кто, с кем, в чьем лице и на основании какого документа заключил этот договор (столбец E);
Предмет договора (столбец F);
Условия договора (столбец G);
Реквизиты Продавца (столбцы Н:Q) и Покупателя (столбцы R:AA).
Данные в этот лист можно вносить вручную, используя копирование ячеек на листе Договор и последующую вставку их на листе Архив. Но это будет занимать много времени. Для автоматизации процесса запишите макрос ВнестиДоговорАрхив.
3.3 Подпрограмма автоматизации операции регистрации договоров
Для переноса всех данных с одного рабочего листа на другой за одно действие, эти данные необходимо сгруппировать в одном месте, в форме удобной для переноса - диапазоне ячеек, расположенных в одной строке или одном столбце. Наиболее простой способ группировки - задание в этом диапазоне ссылок на ячейки, содержащие нужную информацию. Для нашего случая проще организовать такой массив данных в одном столбце, например ВА, выделив для этого диапазон ячеек ВА1:ВА26.
3.3.1 Создание массива переносимых данных
На рис. 3.3. показан фрагмент рабочего листа Договор с массивом переносимых данных собранных в области ячеек ВА1:ВА26. В столбце ВВ, в качестве примера приведены значения, полученные с помощью этих ссылок.
Рис. 3.3. Массив переменных данных договоров, переносимых в базу данных
3.3.2 Подпрограмма переноса информации в базу данных
Для создания подпрограммы может быть использован комбинированный способ. Первоначально запись макроса в механическом режиме, когда записываются все выполненные пользователем в ходе записи действия. После этого редактирование текста кода VBA макроса.
3.3.3 Механическая запись макроса
Последовательность записи макроса состоит из следующих действий:
начните запись макроса, находясь на рабочем листе Договор;
на листе Договор выделите диапазон ячеек ВА1:ВА26 и скопируйте в буфер обмена;
перейдите на лист Архив, поместите табличный курсор в свободной строке в ячейку столбца В и выполните команду Правка/Специальная вставка;
в диалоговом окне Специальная вставка активизируйте переключатель Значения, отметьте опцию Транспонировать, и нажмите кнопку ОК;
перейдите на рабочий лист Договор, установите табличный курсор в ячейку А1 и остановите запись макроса.
После записи макроса создайте кнопку с текстом Внести в Архив и назначьте ей выполнение созданного макроса.
3.3.4 Редактирование макроса переноса данных
Редактирование макроса производится в Редакторе 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.4. Подпрограмма переноса данных на лист Архив