Средства доступа к внешним данным

Excel имеет много средств доступа к внешним источникам данных (в частности, к ним можно отнести и сводные таблицы, с которыми мы работали в предыдущем часе). Среди всего многообразия таких средств выделим следующие:

• MS Query (Запрос Microsoft)

• ADO (ActiveX Data Objects - Объекты данных ActiveX)

• DAO (Data Access Objects - Объекты доступа к данным)

• ODBC¹ (Open Database Connectivity - Взаимодействие открытых баз данных)

Средства ADO предпочтительны для организации быстрого и эффективного доступа к базе данных Microsoft Access и ей подобных. DAO в основном применяется для поддержки обратной совместимости со старыми версиями баз данных. Технология ODBC используется для соединения с широким спектром самых разнообразных баз данных, в том числе с Microsoft SQL Server и Oracle, В этом часе мы уделим основное внимание возможностям MS Query по импортированию данных в Excel.

Использование ms Query

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

Если принято решение использовать механизм MS Query как часть разрабатываемого приложения, то, прежде всего, необходимо инсталлировать MS Query на компьютере разработчика. MS Query не инсталлируется при типовой установке Excel 2000 или Office 2000, но инсталлируется как часть полной установки. После инсталляции Excel 2000 MS Query можно установить отдельно.

В этом часе в качестве "подопытной" базы данных будем использовать базу данных Борей, которая входит в поставку Microsoft Access. Для создания макроса, извлекающего данные из базы данных Борей, выполните следующие действия.

1. Закройте все открытые рабочие книги и отройте новую.

2. Начните запись макроса с именем ПримерMSQuery и сохраните его в текущей книге.

3. Выполните команду Данные > Внешние данные > Создать запрос. Откроется диалоговое окно Выбор источника данных (рис. 20.1). Здесь можно выбрать существующий источник данных или создать новый.

4. В диалоговом окне Выбор источника данных на вкладке Базы данных выберите пункт База данных MS Access и щелкните на кнопке ОК. В открывшемся диалоговом окне Выбор базы данных укажите путь к базе данных Борей. Если Excel 2000 или Office 2000 инсталлированы на диске С:, то, скорее всего, путь к базе данных Борей будет следующий: C:\Program Files\Microsoft Office\Office\Samples\Борей.mdb. Выберите Борей и щелкните на кнопке ОК. Откроется диалоговое окно Создание запроса: выбор столбцов (рис. 20.2).

Рис. 20.1. Диалоговое окно для выбора источника данных

Рис. 20.2. Следующий шаг после установки связи с базой данных - выбор данных

5. В диалоговом окне Создание запроса: выбор столбцов показаны все таблицы и запросы, имеющиеся в открытой базе данных. Чтобы увидеть столбцы таблиц и запросов, щелкните на значке "+" рядом с именем таблицы и запроса в списке Имеющиеся таблицы и столбцы. Щелкните на значке "+" рядом с таблицей Товары и выберите столбец КодТовара. Щелкните на кнопке со значком ">", чтобы перенести столбец в окно Столбцы запроса. Таким же образом выберите и перенесите столбцы Марка, Цена и НаСкладе. Щелкните на кнопке Далее. Откроется диалоговое окно Создание запроса: отбор данных (рис. 20.3).

6. В списке Столбцы для отбора выберите столбец Цена. В первом раскрывающемся списке выберите оператор сравнения больше или равно, а в соседнее поле введите число 20 (рис. 20.3). Таким образом мы создали фильтр, отбраковывающий все записи, у которых значение в поле Цена меньше 20. Щелкните на кнопке Далее. Откроется диалоговое окно Создание запроса: порядок сортировки (рис. 20.4).

Рис. 20.3. Диалоговое окно, позволяющее выполнять фильтрацию импортируемых данных

Рис. 20.4. После задания фильтра на извлекаемые данные можно установить порядок сортировки записей

7. Выберите цена в раскрывающемся списке Сортировать по и щелкните на кнопке Далее. Откроется последнее диалоговое окно Создание запроса: заключительный шаг (рис. 20.5).

8. Для возврата в Excel щелкните на кнопке Готово. Появится диалоговое окно Возврат данных в Microsoft Excel, спрашивающее, куда поместить данные (рис. 20.6).

9. Поместите данные в текущий рабочий лист, начиная с ячейки AI. Щелкните на кнопке ОК. Через несколько мгновений выбранные данные появятся врабочем листе (рис. 20.7).

10. Остановите запись макроса.

Рис. 20.5. Последний вопрос: что вы хотите делать с данными?

Рис. 20.6. И еще один вопрос: куда поместить данные?

Рис. 20.7. Данные вставлены в рабочий лист