Как подключить adodb в excel
Предположим, что у нас есть база данных которую мы создали в Microsoft Access версии 2010 или 2003 или более ранних, значения не имеет. В базе данных имеется 1 (одна) таблица с именем "Клиенты". Структура таблицы в базе данных следующая:
| Код | Фамилия | Имя | Отчество | Дата рождения |
Где:
1. Код - имеет тип данных: Счетчик (размер поля длинное целое)
2. Фамилия - имеет тип данных: Текстовый (размер поля 50 символов)
3. Имя - имеет тип данных: Текстовый (размер поля 50 символов)
4. Отчество - имеет тип данных: Текстовый (размер поля 50 символов)
5. Дата рождения - имеет тип данных: Дата/время (формат поля краткий формат даты)
Созданную базу данных для удобства поместим в каталог с нашей программой. Назовем ее к примеру "DataBase". Теперь создадим новый проект на Visual Basic 6.0, сохраним его в каталог с программой. В проекте создадим 1 (одну) форму и 1 (один) модуль. Имя формы оставим по умолчанию "Form1", имя модуля тоже оставим по умолчанию "Module1". Следующим шагом, подключим библиотеку под названием "Microsoft ActiveX Data Objects 2.8 Library". Подключение выполняем нажав на меню "Project" ---> "References" в открывшемся окне находим "Microsoft ActiveX Data Objects 2.8 Library" ставим галочку рядом с названием нажимаем на кнопку "OK".
Следующим шагом будет объявление переменных для ADODB.Connection и ADODB.Recordset. Откроем наш созданный модуль "Module1" и в самый верх модуля, вставим следующие строки кода:
Получается следующие, что переменная conn - ADODB.Connection будет отвечать за соединение с базой данных и выполнения команд поступающих от программы к базе данных в виде SQL запроса. Переменная rs - ADODB.Recordset является набором записей, она будет содержать в себе набор записей которая вернет база данных при выполнении того или иного запроса во время работы программы.
После объявления переменных спустимся ниже на пару строк и вставим следующие строки кода:
Наша программа будет начинать работу с процедуры "Sub Main". То есть нам надо вернуться в настройки программы, а именно сюда: на панели меню проекта выберите "Project" ---> "Project1 Properties" в открывшемся окне на вкладке "General" выберем из списка "Startup Object" ---> "Sub Main", нажмем кнопку "ОК". В созданном коде получается следующие:
1. При запуске программы, происходит обращение к процедуре под именем "OpenDB".
2. Открывается Form1, созданная нами ранее.
Теперь спустимся еще на пару строк ниже и вставим код процедуры "OpenDB".
Тут переменная "conn" получает строку соединения:
далее выполняется соединение. Если соединение установлено, тогда параметр conn.State получает свойство = 1, по созданному условию мы предупреждаем пользователя о выполненном с базой данных:
После чего по коду выше открывается Form1. В форме, вставляем следующий код:
Очень часто в практической работе возникает необходимость подключиться к таблице на листе Excel, как к базе данных. Конечно, можно работать и средствами объектной модели Excel (см. раздел 11 "Программирование в Excel"), но использование объектов ADO дает значительные преимущества:
- намного проще и удобнее производить поиск записи, вставку новых записей в таблицу, изменение существующих записей. Объекты ADO изначально проектировались именно для этих целей;
- объектную модель 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)
Рис. 9.8 Присваиваем имя диапазону
Обратите внимание, что нужно выбирать диапазон вместе с названиями столбцов.
После того, как имя присвоено, Excel можно закрывать — он больше нам не нужен.
Дальше по плану нужно было бы создать файл *.UDL и настроить в нем подключение к нашему файлу C:\Fact.xls. Однако напрямую из файла UDL можно работать только с драйверами OLE DB, а нужного драйвера, к сожалению, нет (Microsoft JET 4.0 OLE DB Provider хочет работать только с файлами MDB). Поэтому делаем еще один подготовительный шаг — создаем источник данных ODBC (поскольку драйвер ODBC для подключения к Excel есть). Первое действие — в Панели управления открываем Administrative Tools (Средства администрирования) и два раза щелкаем по иконке Data Sources (ODBC) (Источники данных ODBC). Откроется окно, аналогичное представленному на рис. 9.9.
Рис. 9.9 Окно управления источниками данных ODBC
В вашем распоряжении — три типа DSN (Data Source Name, то есть источников данных ODBC):
- UserDSN — информация об этих источниках данных хранится в части реестра, специфической для пользователя, поэтому эти источники данных доступны только тому пользователю, который их создал;
- SystemDSN — информация об этих источниках данных хранится в общей части реестра и доступна для всех пользователей на этом компьютере;
- FileDSN — информация об этих источниках данных записывается в файл в файловой системе.
Чаще всего используются System DSN — системные источники данных, поэтому переходим на вкладку System DSN и нажимаем на кнопку Add (Создать).
Первое, о чем нас спросят — это о типе драйвера, который мы хотим использовать. Выбираем, конечно, Microsoft Excel Driver и нажимаем на кнопку Finish. Но создание источника данных на этом далеко не кончилось.
На следующем экране нам потребуется:
- в поле Data Source Name ввести имя источника данных. Можно ввести любое имя — главное, чтобы вы его не забыли. Мы введем имя ExcelVolumes;
- нажать на кнопку SelectWorkbook и выбрать нужную нам рабочую книгу (в нашем случае — C:\Fact.xls);
- нажать на кнопку Options и подумать, будем ли изменять из программы нашу таблицу. Если да, то флажок 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. Остальные поля можно не заполнять (см. рис. 9.11). Для проверки можно нажать на кнопку Test Connection, а затем — OK.
Рис. 9.11 Настраиваем параметры подключения к созданному источнику ODBC
Вы можете перенести содержимое набора записей ADO на лист Microsoft Excel, автоматизируя Excel. Способ, который можно использовать, зависит от версии Excel, для которой выполняется Автоматизация. Excel 97, Excel 2000 и Excel 2002 имеют метод Копифромрекордсет, который можно использовать для переноса объекта Recordset в диапазон. Копифромрекордсет в Excel 2000 и 2002 можно использовать для копирования либо объекта DAO, либо набора записей ADO. Однако Копифромрекордсет в Excel 97 поддерживает только наборы записей DAO. Чтобы перенести набор записей ADO в Excel 97, можно создать массив из набора записей, а затем заполнить диапазон содержимым этого массива.
В этой статье обсуждаются оба подхода. В приведенном примере кода показано, как можно перенести набор записей ADO в Excel 97, Excel 2000, Excel 2002, Excel 2003 или Excel 2007.
Дополнительные сведения
В приведенном ниже примере кода показано, как скопировать набор записей ADO на лист Microsoft Excel с помощью автоматизации из Microsoft Visual Basic. Код сначала проверяет версию Excel. Если обнаруживается Excel 2000 или 2002, используется метод Копифромрекордсет, так как он эффективен и требует меньше кода. Однако если обнаруживается Excel 97 или более ранняя версия, набор записей сначала копируется в массив с помощью метода GetRows объекта Recordset объекта ADO. Затем массив передается таким образом, чтобы записи направляются в первое измерение (в строках), а поля — во второе измерение (в столбцах). Затем массив копируется на лист Excel путем присвоения массива диапазону ячеек. (Массив копируется за один шаг, а не циклически по каждой ячейке на листе.)
В примере кода используется пример базы данных Northwind, которая входит в состав Microsoft Office. Если вы выбрали папку по умолчанию при установке Microsoft Office, база данных находится в:
\Program Files\Microsoft Оффице\оффице\самплес\норсвинд.МДБ
Если база данных "Борей" расположена в другой папке на компьютере, необходимо изменить путь к базе данных в приведенном ниже коде.
Если в системе не установлена база данных "Борей", для установки образцов баз данных можно использовать параметр "Добавить/удалить" для установки Microsoft Office.
Note (Примечание ) База данных "Борей" не устанавливается при установке 2007 Microsoft Office. Чтобы получить базу данных Northwind 2007, посетите следующий веб-сайт корпорации Майкрософт:
Действия по созданию примера
Запустите Visual Basic и создайте новый стандартный проект EXE. По умолчанию создается форма Form1.
Добавьте CommandButton в Form1.
Выберите Референцесфром меню проект. Добавьте ссылку на библиотеку Microsoft ActiveX Data Objects 2,1.
Вставьте приведенный ниже код в раздел Code формы Form1:
Нажмите клавишу F5, чтобы запустить проект. Откроется форма Form1.
Нажмите CommandButton на Form1 и обратите внимание, что содержимое таблицы Заказы отображается в новой книге Excel.
Использование Копифромрекордсет
Ошибка во время выполнения 430: класс не поддерживает автоматизацию или не поддерживает ожидаемый интерфейс. В этом примере кода можно избежать этой ошибки путем проверки версии Excel, чтобы не использовать Копифромрекордсет для версии 97.
Note (Примечание ) При использовании Копифромрекордсет следует иметь в виду, что используемый набор записей ADO или DAO не может содержать поля объекта OLE или массивы данных, такие как иерархические наборы записей. Если вы включаете поля любого типа в набор записей, метод Копифромрекордсет завершается со следующей ошибкой:
Ошибка во время выполнения — 2147467259: сбой метода Копифромрекордсет в диапазоне объектов.
Использование GetRows
Если обнаруживается Excel 97, используйте метод GetRows объекта Recordset ADO, чтобы скопировать набор записей в массив. Если вы назначаете массиву, возвращаемому методом GetRows, диапазону ячеек на листе, данные передаются по столбцам, а не по строкам. Например, если в наборе записей есть два поля и 10 строк, массив будет отображаться в виде двух строк и 10 столбцов. Поэтому необходимо переставить массив с помощью функции Транспоседим (), прежде чем присваивать массив диапазону ячеек. При назначении массива диапазону ячеек существуют некоторые ограничения, которые следует учитывать.
При назначении массива объекту диапазона Excel применяются следующие ограничения:
Массив не может содержать поля объекта OLE или массив данных, например, иерархические наборы записей. Обратите внимание, что пример кода проверяет это условие и отображает "поле массива", чтобы пользователь знал, что это поле невозможно отобразить в Excel.
Массив не может содержать поля даты, которые имеют дату, предшествующую 1900 году. (Обратитесь к разделу "ссылки" для ссылки на статью базы знаний Майкрософт). Обратите внимание на то, что пример кода форматирует поля даты как строки Variant, чтобы избежать возникновения этой проблемы.
Обратите внимание на использование функции Транспоседим () для переставит массив перед копированием массива на лист Excel. Вместо того чтобы создавать собственную функцию для пересчета массива, можно использовать функцию транспонировать Excel, изменив пример кода, чтобы назначить массив ячейкам, как показано ниже:
Если вы решили использовать метод транспонировать Excel вместо функции Транспоседим () для перебытия массива, следует помнить о следующих ограничениях с помощью метода транспонировать:
- Массив не может содержать элемент, длина которого превышает 255 символов.
- Массив не может содержать значения NULL.
- Число элементов в массиве не может превышать 5461.
Если приведенные выше ограничения не учитываются при копировании массива на лист Excel, может возникнуть одна из следующих ошибок во время выполнения:
Ошибка во время выполнения 13: несоответствие типов
Ошибка во время выполнения 5: недопустимая процедура
Ошибка во время выполнения вызова или аргумента 1004: ошибка, определенная приложением или объектом
Ссылки
Чтобы получить дополнительные сведения об ограничениях на передачу массивов в различные версии Excel, щелкните следующий номер статьи базы знаний Майкрософт:
177991 XL: ограничения передаваемых массивов в Excel с помощью автоматизации
Объект ADO.Connection, подключение к базе данных из VBScript, свойство ConnectionString, генерация строки подключения OLE DB при помощи файла UDL, обработка ошибок при подключении к базе данных, коллекция Errors
Создание объекта Connection производится очень просто. Например, чтобы подключиться к базе данных Northwind на сервере SQL Server с именем LONDON , можно использовать код вида
Set cn = CreateObject("ADODB.Connection")
cn.Provider = "SQLOLEDB"
cn.ConnectionString = "User Source = LONDON1;" _
& " Initial Catalog = Northwind "
cn . Open
В принципе, этого вполне достаточно, чтобы создать работающий объект соединения (чтобы в этом убедиться, можно, например, выполнить команду Wscript . Echo cn . State до и после открытия). Однако есть смысл подробнее поговорить про различные свойства и методы этого объекта.
· свойство Provider определяет драйвер, который будет использован для подключения к базе данных. Обычно используются два типа драйверов для подключения - драйверы OLE DB и ODBC . Если есть возможность, необходимо использовать подключение по OLE DB - более современный способ, который работает намного быстрее. Свойство Provider необходимо указывать только при подключении по OLE DB , при подключении по ODBC все необходимые параметры передаются при помощи свойства ConnectionString . Значения свойств Provider для подключения к разным источникам данных могут выглядеть так:
o " Microsoft . Jet . OLEDB .4.0" - для подключений к файлам Access и Excel и другим источникам данных на основе Jet ;
o " SQLOLEDB " - для подключений к SQL Server (как в примере)
o " MSDAORA .1" - для подключений к серверу Oracle ;
o " ADsDSOObject " - для подключения к базе данных службы каталогов Windows .
· свойство ConnectionString - главное свойство объекта Connection . Оно определяет параметры подключения к источнику (его значение представляет из себя набор параметров, разделенных разделителем - точкой с запятой, порядок их значения не имеет). В нашем примере мы передали четыре значения параметра - User ID - идентификатор пользователя (это значит, что мы используем подключение SQL Server ), Password - пароль password и Data Source - имя сервера баз данных, Initial Catalog - имя базы данных на этом сервере. Для передачи ConnectionString можно использовать и метод Open . Если бы использовали подключение Windows к SQL Server , код для подключения мог бы выглядеть так:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=london1"
При подключении к файлу Access или Excel строка подключения могла бы выглядеть так:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Борей.mdb"
Проще всего создать строку соединения при помощи файла UDL . Для этого нужно создать обычный текстовый файл, переименовать его так, чтобы его расширение выглядело как . udl (от user data link ), щелкнуть два раза мышью и настроить параметры подключения. Потом можно открыть этот файл в блокноте и скопировать сгенерированную строку. Можно использовать в connection string и просто ссылку на созданный файл UDL :
cn.ConnectionString = "File Name = d:\mylink.udl"
Такой способ является более рекомендованным (особенно если файлы UDL находятся в скрытом сетевом каталоге, доступном только для чтения), поскольку обеспечивается возможность централизованного внесения изменений во все приложения, если источник данных переехал на другой сервер.
Для подключения по ODBC рекомендуется вначале создать источник данных ODBC (через Панель управления -> Источники данных ( ODBC ), а затем точно так же сгенерировать строку подключения при помощи файла UDL , выбрав драйвер Microsoft OLE DB Provider for ODBC Drivers .
· метод Open позволяет открыть соединение с базой данных (и проверить его работоспособность). В качестве параметра может принимать строку подключения, имя пользователя и пароль.
· метод Close позволяет закрыть соединение (объект соединения при этом из памяти не удаляется). Чтобы полностью избавиться от этого объекта, можно использовать код
Set cn = Nothing
или просто Set cn = Nothing - разрыв соединения произойдет автоматически.
Для этого объекта предусмотрено множество других свойств и методов, однако здесь они рассматриваться не будут (за дополнительной информацией можно обратиться к документации или учебным курсам Microsoft ). Единственное свойство, которое обязательно необходимо рассмотреть - это свойство Errors , которое возвращает коллекцию объектов Error - ошибок. Ошибки при установке или работе соединения встречаются очень часто (неверно введен пароль или имя пользователя, у пользователя недостаточно прав на подключение, невозможно обратиться к компьютеру по сети и т.п.), поэтому настоятельно рекомендуется реализовывать в программе обработку ошибок. Самый простой вариант реализации обработчика ошибок может выглядеть так:
Dim cn
Set cn = CreateObject("ADODB.Connection")
cn.Provider = "SQLOLEDB"
cn.ConnectionString = "User Source = LONDON1;" _
& "Initial Catalog = Northwind"
On Error GoTo CnErrorHandler
CnErrorHandler:
For Each ADOErr In cn.Errors
Debug.Print ADOErr.Number
Debug.Print ADOErr.Description
End Sub
Самые важные свойства объекта ADOError :
· Description - описание ошибки. Обычно наиболее важная информация содержится именно в описании.
· Number - номер ошибки. По номеру удобно производить поиск в базе знаний и в Интернет.
· Source - источник ошибки. Эта информация полезна только в том случае, если в коллекции Errors могут оказаться ошибки из разных источников.
· SQLState и NativeError - информация о возникшей ошибке, которая пришла с SQL -совместимого источника данных.
Читайте также: