- •Предисловие
- •1. Основы программирования в Microsoft Office
- •1.2. Что такое язык VBA
- •1.3. Макрорекордер: быстрое создание макросов
- •1.4. Четыре способа запуска макроса
- •2.1. Общие сведения
- •2.3.1. Как открыть редактор кода и как он устроен
- •2.3.2. Список объектов и список событий
- •2.3.3. Закладки и разделение окна редактирования
- •2.3.4. Как редактор помогает писать код
- •2.4. Работа со справкой
- •3. Синтаксис и программные конструкции VBA
- •3.1. Основы синтаксиса
- •3.2. Операторы
- •3.3. Переменные и типы данных
- •3.4. Константы
- •3.5. Операторы условного и безусловного перехода
- •3.5.2. Оператор Select Case
- •3.5.3. Оператор GoTo
- •3.6. Работа с циклами
- •3.7. Массивы
- •3.8. Процедуры и функции
- •3.8.1. Виды процедур
- •3.8.2. Область видимости процедур
- •3.8.3. Объявление процедур
- •3.8.4. Передача параметров
- •3.8.5. Вызов и завершение работы процедур
- •3.9. Встроенные функции языка VBA
- •3.9.1. Что такое встроенные функции
- •3.9.2. Функции преобразования и проверки типов данных
- •3.9.3. Строковые функции
- •3.9.4. Функции для работы с числовыми значениями
- •3.9.5. Функции для работы с датой и временем
- •3.9.6. Функции для форматирования данных
- •3.9.7. Функции для организации взаимодействия с пользователем
- •3.9.9. Функции для работы с массивами
- •3.9.10. Функции для работы с файловой системой
- •3.9.11. Другие функции VBA
- •4. Работа с объектами и объектные модели
- •4.1. Что такое классы и объекты
- •4.2. Создание и удаление объектов
- •4.3. Методы объекта
- •4.4. Свойства объекта
- •4.6. Просмотр объектов
- •4.7. Объектные модели
- •5. Формы, элементы управления и события
- •5.1. Для чего нужны формы
- •5.2. Создание форм и самые важные свойства и методы форм
- •5.3. Элементы управления
- •5.3.1. Что такое элемент управления
- •5.3.2. Элемент управления Label
- •5.3.3. Элемент управления TextBox
- •5.3.4. Элемент управления ComboBox
- •5.3.5. Элемент управления ListBox
- •5.3.7. Элементы управления OptionButton и Frame
- •5.3.8. Элемент управления CommandButton
- •5.3.9. Элементы управления ScrollBar и SpinButton
- •5.3.10. Элементы управления TabStrip и MultiPage
- •5.3.11. Элемент управления Image
- •5.3.12. Применение дополнительных элементов управления
- •6. Отладка и обработка ошибок в программе
- •6.1. Типы ошибок
- •6.2.1. Тестирование
- •6.2.2. Переход в режим паузы
- •6.2.3. Действия в режиме паузы
- •6.2.4. Окно Immediate
- •6.2.5. Окно Locals
- •6.2.6. Окно Watches
- •6.3. Перехват и обработка ошибок времени выполнения
- •7. Работа с помощником
- •8. Работа с панелями инструментов и меню
- •9.1. Зачем нужно работать с базами данных
- •9.2. Что такое ADO
- •9.3. Объект Connection и коллекция Errors
- •9.4. Подключение к таблице на листе Excel
- •9.5. Объект Recordset и коллекция Fields
- •9.5.1. Открытие Recordset
- •9.5.3. Перемещение по Recordset
- •9.5.4. Коллекция Fields и объекты Field
- •9.5.5. Сортировка и фильтрация данных
- •9.5.6. Изменение записей на источнике при помощи объекта Recordset
- •9.5.7. Прочие свойства и методы объекта Recordset
- •10. Программирование в Word
- •10.1. Зачем программировать в Word
- •10.3. Объект Application
- •10.3.1. Как работать с объектом Application
- •10.3.2. Свойства, методы и события объекта Application
- •10.4. Коллекция Documents и объекты Document
- •10.4.1. Как работать с коллекцией Documents
- •10.4.2. Свойства и методы коллекции Documents
- •10.4.3. Работа с объектом Document, его свойства и методы
- •10.5. Объекты Selection, Range и Bookmark
- •10.5.1. Работа с объектом Selection
- •10.5.2. Свойства и методы объекта Selection
- •10.5.3. Работа с объектом Range, его свойства и методы
- •10.5.4. Объект Bookmark
- •10.6. Другие объекты Word
- •10.6.1. Коллекция AddIns и объекты AddIn
- •10.6.2. Объект AutoCorrect
- •10.6.3. Коллекция Languages и объект Language
- •10.6.4. Объект Options
- •10.6.5. Объекты Find и Replacement
- •10.6.6. Объекты Font и ParagraphFormat
- •10.6.7. Объект PageSetup
- •10.6.8. Объекты Table, Column, Row и Cell
- •10.6.9. Объект System
- •10.6.10. Коллекция Tasks и объект Task
- •10.6.11. Коллекция Windows и объект Window
- •11. Программирование в Excel
- •11.1. Зачем программировать в Excel
- •11.2. Объект Application
- •11.3. Свойства и методы объекта Application
- •11.4. Коллекция Workbooks и объект Workbook, их свойства и методы
- •11.5. Коллекция Sheets и объект Worksheet, их свойства и методы
- •11.6. Объект Range, его свойства и методы
- •11.7. Коллекция QueryTables и объект QueryTable
- •11.9. Работа с диаграммами (объект Chart)
- •11.10. Другие объекты Excel
- •12. Программирование в Access
- •12.1. отличительные особенности создания приложений в Access
- •12.2. Основные этапы создания приложений в Access
- •12.4. Макрокоманды и объект DoCmd
- •12.5. Работа с формами Access из VBA (объект Form)
- •12.6. Свойства, методы и события форм
- •12.7. Работа с отчетами (объект Report)
- •13. Программирование в Outlook
- •13.1. Зачем программировать в Outlook
- •13.2. Некоторые особенности программирования в Outlook
- •13.4. Объект Namespace
- •13.5. Коллекция Folders и объект MAPIFolder
- •13.7. Другие объекты Outlook
- •14. Программирование в PowerPoint
- •15. Программирование в Project
- •15.1. Основы программирования в Project Professional. Объект Application
168 |
Глава 9 |
ние, невозможно обратиться к компьютеру по сети и т. п.), поэтому настоятельно рекомендуется реализовывать в программе обработку ошибок. Самый простой вариант реализации обработчика ошибок может выглядеть так:
Dim cn As ADODB.Connection
Set cn = CreateObject("ADODB.Connection") cn.Provider = "SQLOLEDB"
cn.ConnectionString = "User ID=SA;Password=password;" _ & "Data Source = LONDON1;Initial Catalog = Northwind"
On Error GoTo CnErrorHandler cn.Open
Exit Sub CnErrorHandler:
For Each ADOErr In cn.Errors Debug.Print ADOErr.Number Debug.Print ADOErr.Description
Next
На практике при возникновении ошибки пользователю предлагается ее исправить и еще раз произвести подключение.
Для получения информации о том, почему возникла ошибка, используется специальный объект ADOError (при возникновении ошибки он создается автоматически). Далее представлены самые важные свойства этого объекта.
Description — описание ошибки. Обычно наиболее важная информация содержится именно в описании.
Number — номер ошибки. По номеру удобно производить поиск в базе зна-
ний Microsoft (www.microsoft.com/support) и в Интернете.
Source — источник ошибки. Эта информация полезна только в том случае, если в коллекции Errors могут оказаться ошибки из разных источников.
SQLState и NativeError — информация о возникшей ошибке, которая пришла с SQL-совместимого источника данных.
9.4. Подключение к таблице на листе Excel
Очень часто в практической работе возникает необходимость подключиться к таблице на листе Excel как к базе данных. Конечно, можно работать и с объектной моделью Excel (см. гл. 11), но использование объектов ADO дает значительные преимущества:
намного проще и удобнее производить поиск записи, вставку новых записей в таблицу, изменение существующих записей. Объекты ADO изначально проектировались именно для этих целей;
Работа с базами данных и применение объектной модели ADO |
169 |
объектную модель Excel можно использовать только в Excel, а объекты ADO универсальны и могут применяться для подключения к любым источникам данных. Если вы используете объекты ADO, то можете использовать фактически одно и то же приложение как для работы с данными в Excel, так и для работы с информацией в "большой" базе данных, например, в SQL Server или Oracle. Ситуация, когда часть информации находится в базе данных, а другая часть — в книге Excel, встречается на практике очень часто.
Подключиться к таблице на листе Excel совсем не сложно, но самостоятельно догадаться до всей последовательности действий бывает трудно. Поэтому далее приведена пошаговая последовательность действий.
Предположим, что у нас есть книга Excel, которая называется Fact.xls и лежит в корневом каталоге диска C:. На первом листе этой книге есть несложная таблица, представленная на рис. 9.7.
Рис. 9.7. Таблица в Excel, к которой нужно обратиться средствами ADO
Нам необходимо подключиться к этой таблице, как к базе данных.
Первый этап — это подготовка. Иногда можно обойтись и без нее (если лист Excel — это одна таблица). На практике же часто бывает так, что на листе у нас несколько таблиц, или таблица с комментариями, или под таблицей посчитаны итоги и т. п. Чтобы не смущать Excel, лучше явно указать нашу таблицу. Сделать это очень просто: нужно ее выделить (в нашем случае — диапазон с B3 по D8) и присвоить выделенному диапазону имя. Для этого в Excel в меню Вставка нужно выбрать Имя | Присвоить и ввести имя. В нашем случае мы присвоили имя Volumes (рис. 9.8).
170 |
Глава 9 |
||
|
|
|
|
|
|
|
|
Рис. 9.8. Присвоение имени диапазону
Обратите внимание, что диапазон нужно выбирать вместе с названиями столбцов.
После того, как имя присвоено, документ Excel нужно сохранить и можно закрывать — он больше нам не нужен.
Далее по плану нужно было бы создать UDL-файл и настроить в нем подключение к нашему файлу C:\Fact.xls. Однако напрямую из UDL-файла можно работать только с драйверами OLE DB, а нужного нам драйвера, к сожалению, нет (Microsoft JET 4.0 OLE DB Provider хочет работать только с файлами mdb). Поэтому делаем еще один подготовительный шаг — создаем источник данных ODBC (поскольку драйвер ODBC для подключения к Excel есть). Первое действие — в Панели управления Windows открываем Administrative Tools (Средства администрирования) и два раза щелкаем по иконке Data Sources (ODBC) (Источники данных ODBC). Откроется окно, представленное на рис. 9.9.
Работа с базами данных и применение объектной модели ADO |
171 |
|
|
|
|
|
|
|
Рис. 9.9. Окно управления источниками данных ODBC
В вашем распоряжении три вкладки с DSN (Data Source Name — источников данных ODBC):
User DSN (пользовательские источники данных) — информация об этих источниках данных хранится в части реестра, специфической для пользователя, поэтому эти источники данных доступны только тому пользователю, который их создал;
System DSN (системные источники данных) — информация об этих источниках данных хранится в общей части реестра и доступна для всех пользователей на этом компьютере;
File DSN (файловые источники данных) — информация об этих источниках данных записывается в файл в файловой системе.
Чаще всего используются системные источники данных, поэтому переходим на вкладку System DSN и нажимаем кнопку Add.
На первом экране мастера создания нового подключения нам потребуется ввести информацию о типе драйвера, который мы хотим использовать. Выбираем, конечно, Microsoft Excel Driver и нажимаем кнопку Finish. Но создание источника данных на этом далеко не кончилось.
На следующем экране мастера нам потребуется:
в поле Data Source Name ввести имя источника данных. Можно ввести любое имя — главное, чтобы вы его не забыли. Введите имя
ExcelVolumes;
172 |
Глава 9 |
нажать кнопку Select Workbook и |
выбрать нужную рабочую книгу |
(в нашем случае это C:\Fact.xls); |
|
нажать кнопку Options, чтобы открылись дополнительные параметры подключения, и, если вы собираетесь изменять таблицу Excel из программы, снять флажок Read Only.
В итоге окно может выглядеть так, как представлено на рис. 9.10.
Рис. 9.10. Настроенный источник ODBC для подключения к файлу Excel
Осталось нажать две кнопки OK, чтобы закрыть окно создания источника данных ODBC.
В принципе, в коде программы можно написать значение свойства ConnectionString вручную, воспользовавшись документацией по ADO. Выглядеть соответствующая строка может, например, так:
cn.ConnectionString = "Provider=MSDASQL.1;DSN=FactExcel;" _ & "DBQ=C:\Fact.xls;"
Но зачем писать что-то руками, когда очень просто можно сгенерировать нужное значение автоматически:
так же, как было описано в предыдущем разделе, создаем UDL-файл (можно воспользоваться уже готовым);
щелкаем по нему два раза мышью, переходим на вкладку Provider и вы-
бираем Microsoft OLE DB Provider for ODBC Drivers;
переходим на вкладку Connection и в списке Use Data Source Name выбираем созданный нами источник данных ExcelVolumes. Остальные поля
Работа с базами данных и применение объектной модели ADO |
173 |
можно не заполнять (рис. 9.11). Для проверки можно нажать кнопку Test Connection, а затем OK.
Рис. 9.11. Настройка параметров подключения к созданному источнику ODBC
последнее действие — открываем созданный UDL-файл в блокноте, копируем из него строку подключения и используем в нашей программе.
Итоговый код процедуры для подключения к Excel может выглядеть так:
Public Sub ConnectToExcel()
Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=MSDASQL.1;" _
& "Data Source=ExcelVolumes" cn.Open
'Про Recordset мы будем говорить в следующем разделе 'Этот код помещен для наглядной проверки
Dim rs As New ADODB.Recordset rs.Open "Volumes", cn
MsgBox rs.GetString
End Sub