Сайт о телевидении

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

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

Теперь давайте рассмотрим, как пользоваться этими двумя видами инструментов.

Способ 1: встроенный объект для ввода данных Excel

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

1. Нужно отметить, что по умолчанию значок, который её запускает, скрыт и его нужно активировать. Для этого переходим во вкладку «Файл» , а затем щелкаем по пункту «Параметры» .



2. В открывшемся окне параметров Эксель перемещаемся в раздел «Панель быстрого доступа» . Большую часть окна занимает обширная область настроек. В левой её части находятся инструменты, которые могут быть добавлены на панель быстрого доступа, а в правой – уже присутствующие.

   В поле «Выбрать команды из» устанавливаем значение «Команды не на ленте» . Далее из списка команд, расположенного в алфавитном порядке, находим и выделяем позицию «Форма…» . Затем жмем на кнопку «Добавить» .



3. После этого нужный нам инструмент отобразится в правой части окна. Жмем на кнопку «OK» .



4. Теперь данный инструмент располагается в окне Excel на панели быстрого доступа, и мы им можем воспользоваться. Он будет присутствовать при открытии любой книги данным экземпляром Excel.



5. Теперь, чтобы инструмент понял, что именно ему нужно заполнять, следует оформить шапку таблицы и записать любое значение в ней. Пусть табличный массив у нас будет состоять из четырех столбцов, которые имеют названия «Наименование товара» , «Количество» , «Цена» и «Сумма» . Вводим данные названия в произвольный горизонтальный диапазон листа.



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



7. После этого выделяем любую ячейку заготовки таблицы и щелкаем на панели быстрого доступа по значку «Форма…» , который мы ранее активировали.



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



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



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



11. Заполняем окно инструмента теми значениями, которые хотим видеть во второй строке табличной области, и снова щелкаем по кнопке «Добавить» .



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



13. Таким образом, заполняем табличный массив всеми значениями, которые хотим в неё ввести.



14. Кроме того, при желании, можно производить навигацию по ранее введенным значениям с помощью кнопок «Назад» и «Далее» или вертикальной полосы прокрутки.



15. При необходимости можно откорректировать любое значение в табличном массиве, изменив его в форме. Чтобы изменения отобразились на листе, после внесения их в соответствующий блок инструмента, жмем на кнопку «Добавить» .



16. Как видим, изменение сразу произошло и в табличной области.



17. Если нам нужно удалить, какую-то строчку, то через кнопки навигации или полосу прокрутки переходим к соответствующему ей блоку полей в форме. После этого щелкаем по кнопке «Удалить» в окошке инструмента.



18. Открывается диалоговое окно предупреждения, в котором сообщается, что строка будет удалена. Если вы уверены в своих действиях, то жмите на кнопку «OK» .



19. Как видим, строчка была извлечена из табличного диапазона. После того, как заполнение и редактирование закончено, можно выходить из окна инструмента, нажав на кнопку «Закрыть» .



20. После этого для предания табличному массиву более наглядного визуального вида можно произвести форматирование.

Способ 2: создание пользовательской формы

Кроме того, с помощью макроса и ряда других инструментов существует возможность создать собственную пользовательскую форму для заполнения табличной области. Она будет создаваться прямо на листе, и представлять собой её диапазон. С помощью данного инструмента пользователь сам сможет реализовать те возможности, которые считает нужными. По функционалу он практически ни в чем не будет уступать встроенному аналогу Excel, а кое в чем, возможно, превосходить его. Единственный недостаток состоит в том, что для каждого табличного массива придется составлять отдельную форму, а не применять один и тот же шаблон, как это возможно при использовании стандартного варианта.

1. Как и в предыдущем способе, прежде всего, нужно составить шапку будущей таблицы на листе. Она будет состоять из пяти ячеек с именами: «№ п/п» , «Наименование товара» , «Количество» , «Цена» , «Сумма» .



2. Далее нужно из нашего табличного массива сделать так называемую «умную» таблицу, с возможностью автоматического добавления строчек при заполнении соседних диапазонов или ячеек данными. Для этого выделяем шапку и, находясь во вкладке «Главная» , жмем на кнопку «Форматировать как таблицу» в блоке инструментов «Стили» . После этого открывается список доступных вариантов стилей. На функционал выбор одного из них никак не повлияет, поэтому выбираем просто тот вариант, который считаем более подходящим.



3. Затем открывается небольшое окошко форматирования таблицы. В нем указан диапазон, который мы ранее выделили, то есть, диапазон шапки. Как правило, в данном поле заполнено все верно. Но нам следует установить галочку около параметра «Таблица с заголовками» . После этого жмем на кнопку «OK» .



4. Итак, наш диапазон отформатирован, как «умная» таблица, свидетельством чему является даже изменение визуального отображения. Как видим, помимо прочего, около каждого названия заголовка столбцов появились значки фильтрации. Их следует отключить. Для этого выделяем любую ячейку «умной» таблицы и переходим во вкладку «Данные» . Там на ленте в блоке инструментов «Сортировка и фильтр» щелкаем по значку «Фильтр» .

   

   Существует ещё один вариант отключения фильтра. При этом не нужно даже будет переходить на другую вкладку, оставаясь во вкладке «Главная» . После выделения ячейки табличной области на ленте в блоке настроек «Редактирование» щелкаем по значку «Сортировка и фильтр» . В появившемся списке выбираем позицию «Фильтр» .



5. Как видим, после этого действия значки фильтрации исчезли из шапки таблицы, как это и требовалось.



6. Затем нам следует создать саму форму ввода данных. Она тоже будет представлять собой своего рода табличный массив, состоящий из двух столбцов. Наименования строк данного объекта будут соответствовать именам столбцов основной таблицы. Исключение составляют столбцы «№ п/п» и «Сумма» . Они будут отсутствовать. Нумерация первого из них будет происходить при помощи макроса, а расчет значений во втором будет производиться путем применения формулы умножения количества на цену.

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



7. После этого создаем ещё одну небольшую таблицу. Она будет состоять из одного столбца и в ней разместится список товаров, которые мы будем выводить во вторую колонку основной таблицы. Для наглядности ячейку с заголовком данного перечня («Список товаров» ) можно залить цветом.



8. Затем выделяем первую пустую ячейку объекта ввода значений. Переходим во вкладку «Данные» . Щелкаем по значку «Проверка данных» , который размещен на ленте в блоке инструментов «Работа с данными» .



9. Запускается окно проверки вводимых данных. Кликаем по полю «Тип данных» , в котором по умолчанию установлен параметр «Любое значение» .



10. Из раскрывшихся вариантов выбираем позицию «Список» .



11. Как видим, после этого окно проверки вводимых значений несколько изменило свою конфигурацию. Появилось дополнительное поле «Источник» . Щелкаем по пиктограмме справа от него левой клавишей мыши.



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



13. Происходит возврат к окошку проверки вводимых значений. Как видим, координаты выделенного диапазона в нем уже отображены в поле «Источник» . Кликаем по кнопке «OK» внизу окна.



14. Теперь справа от выделенной пустой ячейки объекта ввода данных появилась пиктограмма в виде треугольника. При клике на неё открывается выпадающий список, состоящий из названий, которые подтягиваются из табличного массива «Список товаров» . Произвольные данные в указанную ячейку теперь внести невозможно, а только можно выбрать из представленного списка нужную позицию. Выбираем пункт в выпадающем списке.



15. Как видим, выбранная позиция тут же отобразилась в поле «Наименование товара» .



16. Далее нам нужно будет присвоить имена тем трем ячейкам формы ввода, куда мы будем вводить данные. Выделяем первую ячейку, где уже установлено в нашем случае наименование «Картофель» . Далее переходим в поле наименования диапазонов. Оно расположено в левой части окна Excel на том же уровне, что и строка формул. Вводим туда произвольное название. Это может быть любое наименование на латинице, в котором нет пробелов, но лучше все-таки использовать названия близкие к решаемым данным элементом задачам. Поэтому первую ячейку, в которой содержится название товара, назовем «Name» . Пишем данное наименование в поле и жмем на клавишу Enter на клавиатуре.



17. Точно таким же образом присваиваем ячейке, в которую будем вводить количество товара, имя «Volum» .



18. А ячейке с ценой – «Price» .



19. После этого точно таким же образом даем название всему диапазону из вышеуказанных трех ячеек. Прежде всего, выделим, а потом дадим ему наименование в специальном поле. Пусть это будет имя «Diapason» .



20. После последнего действия обязательно сохраняем документ, чтобы названия, которые мы присвоили, смог воспринимать макрос, созданный нами в дальнейшем. Для сохранения переходим во вкладку «Файл» и кликаем по пункту «Сохранить как…» .



21. В открывшемся окне сохранения в поле «Тип файлов» выбираем значение «Книга Excel с поддержкой макросов (.xlsm)» . Далее жмем на кнопку «Сохранить» .



22. Затем вам следует в своей версии Excel и включить вкладку «Разработчик» , если вы это до сих пор не сделали. Дело в том, что обе эти функции по умолчанию в программе отключены, и их активацию нужно выполнять принудительно в окне параметров Excel.
23. После того, как вы сделали это, переходим во вкладку «Разработчик» . Кликаем по большому значку «Visual Basic» , который расположен на ленте в блоке инструментов «Код» .



24. Последнее действие приводит к тому, что запускается редактор макросов VBA. В области «Project» , которая расположена в верхней левой части окна, выделяем имя того листа, где располагаются наши таблицы. В данном случае это «Лист 1» .



25. После этого переходим к левой нижней области окна под названием «Properties» . Тут расположены настройки выделенного листа. В поле «(Name)» следует заменить кириллическое наименование («Лист1» ) на название, написанное на латинице. Название можно дать любое, которое вам будет удобнее, главное, чтобы в нем были исключительно символы латиницы или цифры и отсутствовали другие знаки или пробелы. Именно с этим именем будет работать макрос. Пусть в нашем случае данным названием будет «Producty» , хотя вы можете выбрать и любое другое, соответствующее условиям, которые были описаны выше.

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

    Как видим, после этого автоматически изменится и наименование Листа 1 в области «Project» , на то, которое мы только что задали в настройках.



26. Затем переходим в центральную область окна. Именно тут нам нужно будет записать сам код макроса. Если поле редактора кода белого цвета в указанной области не отображается, как в нашем случае, то жмем на функциональную клавишу F7 и оно появится.



27. Теперь для конкретно нашего примера нужно записать в поле следующий код:

    Sub DataEntryForm()
    Dim nextRow As Long
    nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row
    With Producty
    If .Range("A2").Value = "" And .Range("B2").Value = "" Then
    nextRow = nextRow - 1
    End If
    Producty.Range("Name").Copy
    
    
    
    
    .Range("A2").Formula = "=IF(ISBLANK(B2), """", COUNTA($B$2:B2))"
    If nextRow > 2 Then
    Range("A2").Select
    
    
    End If
    .Range("Diapason").ClearContents
    End With
    End Sub
    

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

    Итак, первая строка:

    Sub DataEntryForm()

    «DataEntryForm» — это название самого макроса. Вы можете оставить его как есть, а можете заменить на любое другое, которое соответствует общим правилам создания наименований макросов (отсутствие пробелов, использование только букв латинского алфавита и т.д.). Изменение наименования ни на что не повлияет.

    Везде, где встречается в коде слово «Producty» вы должны его заменить на то наименование, которое ранее присвоили для своего листа в поле «(Name)» области «Properties» редактора макросов. Естественно, это нужно делать только в том случае, если вы назвали лист по-другому.

    

    Теперь рассмотрим такую строку:

    nextRow = Producty.Cells(Producty.Rows.Count, 2).End(xlUp).Offset(1, 0).Row

    Цифра «2» в данной строчке означает второй столбец листа. Именно в этом столбце находится колонка «Наименование товара» . По ней мы будем считать количество рядов. Поэтому, если в вашем случае аналогичный столбец имеет другой порядок по счету, то нужно ввести соответствующее число. Значение «End(xlUp).Offset(1, 0).Row» в любом случае оставляем без изменений.

    

    If .Range("A2").Value = "" And .Range("B2").Value = "" Then

    «A2» — это координаты первой ячейки, в которой будет выводиться нумерация строк. «B2» — это координаты первой ячейки, по которой будет производиться вывод данных («Наименование товара» ). Если они у вас отличаются, то введите вместо этих координат свои данные.

    

    Переходим к строке

    Producty.Range("Name").Copy

    В ней параметр «Name» означат имя, которое мы присвоили полю «Наименование товара» в форме ввода.

    

    В строках

    Cells(nextRow, 2).PasteSpecial Paste:=xlPasteValues
    .Cells(nextRow, 3).Value = Producty.Range("Volum").Value
    .Cells(nextRow, 4).Value = Producty.Range("Price").Value
    .Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value
    

    наименования «Volum» и «Price» означают названия, которые мы присвоили полям «Количество» и «Цена» в той же форме ввода.

    

    В этих же строках, которые мы указали выше, цифры «2» , «3» , «4» , «5» означают номера столбцов на листе Excel, соответствующих колонкам «Наименование товара» , «Количество» , «Цена» и «Сумма» . Поэтому, если в вашем случае таблица сдвинута, то нужно указать соответствующие номера столбцов. Если столбцов больше, то по аналогии нужно добавить её строки в код, если меньше – то убрать лишние.

    

    В строке производится умножение количества товара на его цену:

    Cells(nextRow, 5).Value = Producty.Range("Volum").Value * Producty.Range("Price").Value

    Результат, как видим из синтаксиса записи, будет выводиться в пятый столбец листа Excel.

    

    В этом выражении выполняется автоматическая нумерация строк:

    If nextRow > 2 Then
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A" & nextRow)
    Range("A2:A" & nextRow).Select
    End If
    

    Все значения «A2» означают адрес первой ячейки, где будет производиться нумерация, а координаты «A» — адрес всего столбца с нумерацией. Проверьте, где именно будет выводиться нумерация в вашей таблице и измените данные координаты в коде, если это необходимо.

    

    В строке производится очистка диапазона формы ввода данных после того, как информация из неё была перенесена в таблицу:

    Range("Diapason").ClearContents

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

    

    Дальнейшая часть кода универсальна и во всех случаях будет вноситься без изменений.

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



28. После этого возвращаемся на лист Excel. Теперь нам следует разместить кнопку, которая будет активировать созданный макрос. Для этого переходим во вкладку «Разработчик» . В блоке настроек «Элементы управления» на ленте кликаем по кнопке «Вставить» . Открывается перечень инструментов. В группе инструментов «Элементы управления формы» выбираем самый первый – «Кнопка» .



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



30. После того, как область обведена, отпускаем клавишу мыши. Затем автоматически запускается окно назначения макроса объекту. Если в вашей книге применяется несколько макросов, то выбираем из списка название того, который мы выше создавали. У нас он называется «DataEntryForm» . Но в данном случае макрос один, поэтому просто выбираем его и жмем на кнопку «OK» внизу окна.
31. После этого можно переименовать кнопку, как вы захотите, просто выделив её текущее название.

    

    В нашем случае, например, логично будет дать ей имя «Добавить» . Переименовываем и кликаем мышкой по любой свободной ячейке листа.



32. Итак, наша форма полностью готова. Проверим, как она работает. Вводим в её поля необходимые значения и жмем на кнопку «Добавить» .



33. Как видим, значения перемещены в таблицу, строке автоматически присвоен номер, сумма посчитана, поля формы очищены.



34. Повторно заполняем форму и жмем на кнопку «Добавить» .



35. Как видим, и вторая строка также добавлена в табличный массив. Это означает, что инструмент работает.

Создание рабочего листа "Товары.xls"

Сначала рассмотрим, как можно выполнять операции с рабочим листом Microsoft Excel из программы Access. Для этого нам потребуется файл Товары.хls, содержащий рабочую книгу Excel с единственным рабочим листом "Товары", который представляет собой список товаров из демонстрационной базы данных "Борей". Вы можете использовать готовый файл, находящийся на сопровождающем книгу компакт-диске, либо создать его самим, экспортировав в Excel таблицу "Товары" с помощью команды меню Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel) (см. разд. "Быстрый экспорт данных в другие приложения Microsoft Office" гл. 3).

Для того чтобы подготовить файл Товары.xls к дальнейшим экспериментам, выполните следующие действия:

1. Откройте файл Товары.xls, запустив Microsoft Excel.
2. Выделите ячейки с А4 по D12 в рабочем листе. Выберите команду меню Вставка, Имя, Присвоить (Insert, Name, Define). В диалоговом окне Присвоение имени (Define Name) введите в текстовое поле Имя (Names in workbook) имя диапазона: workRange (рис. 15.25) и щелкните по кнопке ОК. Тем самым создается именованный диапазон, который будет использоваться в последующих примерах.

Рис. 15.25. Создание именованного диапазона в таблице "Товары.xls"

1. Выберите в меню Сервис (Tools) команду Надстройки (Adds-Ins) и снимите все флажки в списке Список надстроек (Add-Ins Available) диалогового окна Надстройки (Add-Ins). Удаление надстроек уменьшает время, требующееся для запуска приложения Excel. Чтобы закрыть диалоговое окно, нажмите на кнопку ОК.
2. Сохраните изменения, выполнив команду Файл, Сохранить (File, Save) или Файл, Сохранить как (File, Save As).
3. Закройте Microsoft Excel.

Иерархия объектов VBA приложения Microsoft Excel

Для того чтобы программно работать с объектами Excel, нужно иметь представление об объектной модели Microsoft Excel. Мы не будем здесь подробно описывать эту модель, поскольку она достаточно сложна, представим только ее основные объекты.

• Объект Application является экземпляром собственно Microsoft Excel. Применяя к этому объекту различные методы, можно воспользоваться практически любыми командами меню Excel. Объект Application обладает свойствами ActiveWorkbook и

  ActiveSheet, которые указывают на текущие объекты Workbook (рабочая книга) и Worksheet (рабочий лист). Можно указать Excel.Application в качестве значения аргумента <класс> функций CreateObject () и GetObjectO, а также в операторе Dim objPlMH As New <класс>.

• Основной объект Excel - Workbook (рабочая книга). С помощью этого объекта осуществляется доступ к свойствам и структуре рабочей книги Excel. Объекты workbook - это файлы, которые состоят из нескольких объектов, создаваемых Microsoft Excel: Worksheet (рабочий лист) и Chart (диаграмма). Объекты Worksheet и Chart содержатся В семействах Worksheets и Charts.
• Объекты Worksheet являются подчиненными по отношению к объекту Workbook и обеспечивают доступ к свойствам и структуре рабочих листов книги Excel. Основным объектом взаимодействия приложений Microsoft Access 2002 и Microsoft Excel 2002 является именно объект Worksheet. Имеется возможность перенести информацию из строк и столбцов объекта Recordset приложения Microsoft Access в ячейки объекта Worksheet приложения Microsoft Excel и наоборот. Если в качестве значения аргумента <класс> функции GetObject() либо в операторе Dim оbj Имя As New <класс> указать Excel.Sheet, то по умолчанию откроется первый объект семейства Worksheets - первый рабочий лист (ActiveSheet) объекта Workbook.
• Объекты sheet аналогичны объектам Worksheet, но они входят в семейство Sheets, которое включает в себя как объекты Worksheet, так и объекты Chart, представляющие листы диаграмм в рабочей книге.
• Объект Range - диапазон ячеек рабочего листа Excel. Это могут быть отдельная ячейка, строка, столбец, прямоугольная область рабочего листа или несвязанный (произвольный набор ячеек) или трехмерный диапазон ячеек, включающий ячейки на нескольких рабочих листах. Можно получить либо установить значения диапазона ячеек, определив объект Range. Таких объектов, как ячейка, строка, столбец просто не существует. Именно объект Range со своими свойствами и методами обеспечивает работу как с отдельной ячейкой, так и с их диапазоном. Для ссылки на необходимую группу ячеек можно использовать имя диапазона, состоящего из этих ячеек. Если именованные диапазоны отсутствуют на рабочем листе, можно использовать метод Cells объекта worksheet, чтобы задать координаты ячейки или группы ячеек. Ниже приводятся два способа изменения значения ячейки А1:

ActiveSheet.Range ("Al").Value = 7 ActiveSheet.Cells (1,1).Value = 7

Microsoft Excel предоставляет также многие другие объекты для применения их в качестве объектов приложения сервера, но описанные выше типы являются наиболее часто используемыми в технологии автоматизации с помощью Access VBA.

Открытие существующего рабочего листа Excel и работа с ним

Прежде чем работать с автоматизированными объектами Microsoft Excel, установим ссылку на библиотеку объектов Microsoft Excel. Для этого:

1. Запустите Access и, если необходимо, откройте базу данных "Борей". Вызовите редактор VBA, открыв любой модуль.
2. Выберите в окне редактора VBA команду меню Сервис, Ссылки (Tools, .References), чтобы открыть диалоговое окно References (Ссылки).
3. Установите флажок у элемента Microsoft Excel 10.0 Object Library (рис. 15.26), затем нажмите на кнопку ОК, чтобы закрыть окно References.

Рис. 15.26.

Команды автоматизации удобно изучать при помощи окна отладки Immediate. Поэтому, выведите данное окно на экран, если оно не отображается. Для этого достаточно нажать соответствующую кнопку на панели инструментов или комбинацию клавиш +.

Чтобы программно открыть рабочий лист рабочей книги "Товары":

1. Закройте Excel, если он запущен.
2. Создайте новый модуль, выполнив команду меню Insert, Module (Вставка, Модуль).
3. Добавьте в раздел описаний следующие описания переменных:

Private xlaProd As Excel.Application Private xlwProd As Excel.Workbook Private xlsProd As Excel.Worksheet

1. Введите в окне отладки следующий оператор (рис. 15.27):

Set xlwProd = GetObject(CurDir & "\Товары.хls","Excel.Sheet")

При нажатии затем на клавишу приложение Microsoft Excel запускается в режиме /automation. Функция CurDir возвращает полное имя текущей папки. Если файл Товары.хls был сохранен где-нибудь в другом месте, измените в предыдущем операторе путь к этому файлу. В зависимости от скорости функционирования компьютера, запуск Excel может продолжаться достаточно долю. Загрузка приложения Excel завершена, когда в строке состояния окна отладки надпись Выполнение (Running) исчезает и появляется надпись Готово (Ready). В результате будет создан экземпляр класса Application Microsoft Excel и переменной xlwProd будет присвоена ссылка на объект Workbook. Обратите внимание, что функция Getobject () открывает скрытый экземпляр приложения Excel, значок Excel не появляется на панели задач и интерактивно обратиться к рабочей книге Excel нельзя.

Замечание

В данном операторе аргумент Excel. Sheet является необязательным. Если его не указать, то тип создаваемого объекта будет определен автоматически по расширению файла, указанного в первом аргументе.

Рис. 15.27.

1. Чтобы убедиться, что рабочая книга открыта и мы можем к ней обратиться, введите в окне отладки следующую команду?xlwProd.Name

Свойство Name созданного объекта workbook содержит имя файла Excel: Това-pbi.xls (рис. 15.28).

Рис. 15.28. Команды, позволяющие читать и устанавливать значения отдельных ячеек в рабочем листе "Товары"

1. Файл Toвapы.xls содержит только один объект Worksheet, поэтому рабочий лист "Товары" является активным рабочим листом - объектом ActiveSheet. Чтобы убедиться в этом, введите в окно отладки команду:

?xlwProd. ActiveSheet.Name

Свойство Name этого объекта содержит имя рабочего листа: Товары.

1. Теперь попробуйте обратиться к первой ячейке рабочего листа. Введите?xlwProd.ActiveSheet.Celled, 1). После короткой паузы появится ожидаемый результат - строка "Код товара". Это заголовок первого столбца таблицы.
2. Метод Cells позволяет обратиться к любой ячейке рабочего листа. Введите?xlwProd. ActiveSheet. Cells (R, С), где R - номер строки, а С - номер столбца заданной ячейки, т. е. ее координаты (рис. 15.28).
3. Можно изменить содержимое ячейки, если ввести выражение вида: xlwProd.ActiveSheet.Cells(2,2).Value = "brown rice". Подобно тому как многие объекты Access при введении имени объекта возвращают его значение, метод Cells не требует явного указания свойства Value по умолчанию. Чтобы убедиться в том, что содержимое ячейки изменилось, введите?xlwProd.ActiveSheet.Cells (3,2), не дописывая выражения.Value.

Для установления значения ячейки можно также использовать свойство Formula. Преимущество использования свойства Formula состоит в возможности его применения с целью введения формул с использованием "родного" синтаксиса Microsoft Excel, т. е. в виде ссылок на конкретные ячейки, например "=А2+С6".

Использование именованных диапазонов ячеек

Если в рабочем листе Excel создан именованный диапазон ячеек, то можно получить значения ячеек, содержащихся в этом диапазоне, если сослаться на свойство Range объекта Worksheet. Сначала посмотрим, какие именованные диапазоны присутствуют в открытом нами объекте. Введите в окно отладки команду (рис. 15.29)

XlwProd.Names(1).Name

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

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

XlwProd.Names(1).Value Результат будет: =Товары!$А$4:$D$12,

т. е. прямоугольная область А4-D12 на рабочем листе "Товары".

На рис. 15.29 приведены выражения для управления объектом Range.

Рис. 15.29.

Пусть переменная xlsProd ссылается на рабочий лист "Товары". Для этого введите команду:

Set xlsProd = xlwProd.ActiveSheet.

Для указания конкретной ячейки внутри именованного объекта Range можно использовать следующий оператор:

XlsProd.Range("WorkRange").Cells(1,1)

Здесь используется свойство Range объекта Worksheet для доступа к именованному диапазону, а затем свойство Cells объекта Range - для указания конкретной ячейки в диапазоне. Первая цифра указывает строку, а вторая - столбец.

Для того чтобы обратиться к объекту, который находится на уровень выше в иерархии объектов модели, можно воспользоваться свойством Parent. На рис. 15.29 представлено, как обратиться к рабочей книге Excel, содержащей текущий рабочий лист, и как установить объектную переменную xlaProd, которая должна ссылаться на объект Application Microsoft Excel:

Set хlwРабочаяКнига = хlsРабочийЛист.Parent.

Закрытие объектов Workbook и Application

Объект Microsoft Worksheet закрыть нельзя. Для закрытия объекта Excel Workbook может быть использован метод Close, а для выхода из приложения - метод Quit. Следующие операторы закрывают объект Workbook и затем осуществляют выход из приложения сервера автоматизации, освобождая системные ресурсы:

XlwProd.Close xlaProd.Quit Set xlsProd = Nothing Set xlwProd = Nothing Set xlaProd = Nothing

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

Замечание

После присвоения переменной, указывающей на объект Application значения Nothing, соответствующее приложение не закрывается, хотя память, занятая переменной, освобождается. Поэтому необходимо закрывать приложение с помощью метода Quit перед освобождением соответствующей объектной переменной.

Создание рабочего листа Excel с помощью кода автоматизации

Те же действия, что происходят при нажатии кнопки Анализ в MS Excel, можно осуществить при помощи кода автоматизации VBA. Преимуществом такого способа является возможность форматировать созданный объект специально под нужды конкретного приложения. Рассмотрим функцию CreateCustomSheet (), создающую новый объект Worksheet и заполняющую его данными из таблицы "Товары" базы данных Microsoft Access:

Function CreateCustomSheet() As Integer "Создание рабочего листа MS Excel из таблицы "Товары" "Описание локальных переменных "(Объектные переменные описаны на уровне модуля) Dim сйэБорей As Database "Текущая база данных Dim rstProd As Recordset "Объект Recordset Dim intRow As Integer "Счетчик строк Dim intCol As Integer "Счетчик столбцов "Открытие таблицы в текущей базе данных Set dbБорей = CurrentDb() Set rstProd = dbBopeu.OpenRecordset("Товары", dbdpenTable) DoCmd.Hourglass True "Создание нового объекта Excel Workbook Set xlwProd = CreateObject("Excel.Sheet") ""Создание объекта Application для применения метода Quit Set xlaProd = xlwProd.Parent intRow = 1 intCol = 1 rstProd.MoveFirst "Переход к первой записи Do Until rstProd.EOF "Цикл с шагом в одну запись For intCol = 1 То rstProd.Count "Цикл с шагом в одно поле If (Not IsNull(rstProd(intCol -1))) Then xlwProd.ActiveSheet.Cells(intRow, intCol).Value = CStr(rstProd(intCol -I}} End If Next intCol rstProd.MoveNext intRow = intRow + 1 Loop For intCol = 1 To xlwProd.ActiveSheet.Columns.Count "Форматирование каждого столбца рабочего листа xlwProd.ActiveSheet.Columns(intCol).Font.Size = 8 xlsCust.ActiveSheet.Columns(intCol).AutoFit If intCol = 8 Then "Выравнивание по левому краю числовых и "смешанных почтовых кодов xlwProd.ActiveSheet.Columns(intCol).HorizontalAlignment = _xlLeft End If Next intCol DoCmd.Hourglass False xlwProd.SaveAs (CurDir & "\Товары_2.xls") xlaProd.Quit End Function

Тип данных, возвращаемых выражением rstProd(intCol-l), следует специально изменить с variant на string при помощи функции cstr(), иначе Microsoft Excel вместо нужной величины отобразит в соответствующем столбце #н/д (#N/A#). Если объект Recordset содержит поля, типы которых отличны от Text, то для определения типа данных в столбце используйте соответствующую функцию СТуре().

Константа xlLeft, присвоенная в качестве значения свойству HorizontalAlignment восьмого столбца, представляет собой встроенную константу Excel, определяемую в тот момент, когда устанавливается ссылка на объектную библиотеку Microsoft Excel 10.0 Object Library. Выделение элемента Constants (Константы) в списке Модули/ Классы окна просмотра объектов при подключенной библиотеке Excel отображает константы xlConst. На рис. 15.30 приведено числовое значение константы xlLeft, которая является одной из констант для задания значения свойства HorizontalAlignment.

Рис. 15.30. Значения встроенных констант xlConst в окне Object Browser Access

Ввод оператора? CreateCustomSheet () в окне отладки запускает функцию, которую мы рассматривали выше. На рис. 15.31 приведена рабочая книга "ToBapы_2.xls" с рабочим листом, созданным при помощи функции CreateCustomSheet () и открытым в Microsoft Excel.

Рис. 15.31. Часть рабочего листа Excel, созданного из таблицы "Товары"

Практика показывает, что приложения Microsoft Office (Excel, Word, Power Point и т.п.) являются одними из наиболее часто используемых Windows-приложений. Каждое из них является СОМ-сервером, а следовательно, любой входящий в него объект может быть использован вашей программой как собственный.

Существуют два способа обращения к методам и свойствам СОМ-объекта: путем ссылки на его библиотеку типов (раннее связывание) и по имени (позднее связывание). Для Object Pascal предпочтительным является раннее связывание, так как в этом случае компилятор может проконтролировать правильность обращения к свойствам и методам внешних объектов, а создаваемый им код исполняется, как правило, быстрее. В то же время базовый язык обращения к серверам Microsoft Office - Visual Basic for Application (VBA) не поддерживает работу с указателями и, следовательно, не может использовать интерфейсы. Специально для такого рода языков (помимо VBA c указателями не работают также языки JavaScript, SmallTalk и некоторые другие) в технологию СОМ введены диспинтерфейсы, позволяющие обращаться к методам и свойствам по имени, а не по адресу. При инсталлировании Office можно установить справку по VBA, в которой детально описываются интерфейсы серверов Microsoft Office с указанием назначения методов и свойств, а также параметров обращения к ним. Фактически это единственные доступные программисту документы, на которые ему следует опираться при программировании доступа к мощным возможностям серверов Microsoft Office. Замечу, что при стандартном инсталлировании Microsoft Office справки по VBA не устанавливаются. Если в каталоге Program Files | Microsoft Office | Office вы не найдете файлов vbaxl8.hlp (справка по Excel), vbawrd8.hlp (справка по Word) и т. п., вы должны их добавить с помощью аплета Пуск | Настройка | Панель управления | Установка и удаление программ.

В версию 5 Delphi включены компоненты страницы Servers, позволяющие обращаться к СОМ-объектам этих серверов с помощью библиотек типов, однако эти компоненты практически не документированы. Более того, сами библиотеки уже внедрены в пакет dclaxserver50, так что с помощью этой версии Delphi мне так и не удалось получить их тексты. Во всех случаях изучение обширных текстов библиотек (например, файл Excel_TLB.pas содержит более 20 тыс. строк) мало что дает даже опытному программисту.

В этом разделе приводятся краткое описание основных объектов двух наиболее популярных серверов - Excel и Word, а также примеры использования Excel в стиле VBA (по имени) и с помощью компонентов страницы Servers. Поскольку специально для версии MS Office 97 язык VBA был существенно расширен, этот материал нельзя использовать для работы с более ранними версиями пакета.

Основные объекты серверов Excel и Word

В терминологии VBA используются понятия «объект» и «коллекция». Объект - это обычный интерфейсный объект СОМ, имеющий свойства, методы и события. Коллекция - это группа однотипных объектов. Например, главный объект сервера Excel - Application определяет основные свойства и методы сервера, а коллекция Worksheets представляет собой набор табличных страниц в текущей рабочей книге и т.д. Представленные ниже иерархии объектов и коллекций взяты из файлов vbaXXX.hlp. В отличие от объектов VCL они построены не по принципу наследования, а по функциональной подчиненности.

Объекты Excel

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

Теперь небольшие пояснения. Переменные Sheet и Range введены только для сокращения текста программы: везде вместо Sheet, например, можно писать Excel.Workbooks.Sheets. С версией Delphi 4 поставлялись файлы XLCONST.PAS и XLCONST.DCU, в которых определены используемые в документации vbaxl8.hlp константы xlXXX. С версией 5 эти файлы не поставляются, поэтому я использую их числовые эквиваленты. Ширина полей печатного документа Excel задается во внутренних единицах, соответствующих приблизительно 3,5 мм, так что указанные в операторах Sheet.PageSetup.ХХХMargin значения установят левое, нижнее и правое поля шириной 1,1 см, а верхнее - 1,4 см. Ширина столбца определяется в символах текста, умещающегося в столбце без отсечения.

Переменная Excel определяет поле класса TForm1. При создании класса в него автоматически помещается значение VarEmpty. После завершения работы с Excel пользователь может закрыть его. Но в моей программе Excel не визуализировался, его работа проходила «за кулисами», а созданная таблица записывалась в указанный пользователем файл с помощью оператора Excel.Workbooks.SaveAs(FileName).

После этого Excel закрывался. Поскольку в нашем случае Excel показывает свое окно, а пользователь может его не закрыть, полезно написать такой обработчик события OnDestroy формы:

Procedure TForm1.FormDestroy(Sender: TObject); begin if not VarIsEmpty(Excel) then Excel.Quit end;

Запуская пример, помните, что создание прайс-листа с помощью Excel - процесс достаточно длительный. На моем компьютере (400 МГц, 64 Мбайт) он занял около минуты (для примера - аналогичный прайс-лист средствами Quick Report создается менее чем за 2 с). В конце обработчика в метку lb помещается общее время работы.

Раннее связывание

Следующий пример в функциональном плане повторяет предыдущий. В нем также с помощью Excel создается прайс-лист, но на этот раз используется доступ непосредственно через интерфейсы сервера. Вас ожидает «сюрприз»: время выполнения второго примера на 40 с больше! Я не смог найти разумного объяснения этому феномену, но оба примера находятся на сопровождающем диске, так что вы в любой момент можете убедиться в этом сами.

Поскольку форма второго примера в точности повторяет форму первого, я не буду объяснять, что нужно сделать для ее создания. Добавьте только на форму компонент TExcelApplication и настройте его свойства: Name=Excel, AutoConnect=True, AutoQuit=True. Если вы используете форму предыдущего примера как шаблон, не вставляйте поле Excel в класс TForm1. Обработчик Button1Click должен выглядеть так (см. листинг 2).

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

При обращении к свойству SheetsInNewWorkbook, как и во многих других случаях обращения к интерфейсным свойствам и методам, требуется указание идентификатора языка локализации (lcid). Значением 0 кодируется умалчиваемый язык. Этот же идентификатор передается вторым параметром обращения к методу Excel.Workbooks.Add. Первым параметром нужно указать имя файла (в формате WideString), если рабочая книга уже была ранее создана, или «пустой» параметр EmptyParam, если книга создается впервые.

Все мои попытки работать с объектами Range оказались неудачными. Чтобы вы не слишком осуждали меня, я поместил библиотеку типов Excel_TLB.pas в каталог размещения примера - полистайте ее на досуге и попробуйте найти нужное решение для изменения ширины колонок и полей листа, а также для раскрашивания диапазона, выравнивания текста и т.п.

Есть свои нюансы и при обращении к ячейкам. Во-первых, ими владеет объект Application, а не Sheet. Во-вторых, обращение к конкретному элементу коллекции Cells (как и любой другой коллекции) возможно только через ее свойство Item.

Подводя итоги, еще раз хочу обратить ваше внимание на то, что по времени выполнения позднее связывание хотя бы не проигрывает раннему - во всяком случае, для рассмотренных примеров. Если учесть, что единственными доступными подавляющему большинству программистов документами по серверам MS Office являются справочные файлы vbaXXX.hlp, можно сделать вывод: использование вариантов (позднее связывание) проще, удобнее, а главное - намного понятнее, чем непосредственная работа с интерфейсами (раннее связывание).

КомпьютерПресс 6"2001