В начало

Приложение. Ответы

20-й час. Получение внешних данных

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

В этом часе будут рассмотрены следующие вопросы.

• Базы данных, из которых Excel может импортировать данные.
• Обзор методов импорта данных.
• Импорт данных с помощью MS Query.
• Исследование и модификация кода макроса, выполняющего запрос MS Query.

Базы данных, доступные из Excel

Вы будете удивлены, когда узнаете, что из Excel можно получить доступ практически ко всем современным базам данных. Чтобы не быть голословным, приведу небольшой список этих баз данных:

• Microsoft Access
• Microsoft SQL Server

• Microsoft FoxPro

• Oracle

• Paradox

• dBase

• текстовые файлы в кодах ASCII

• SYKL

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

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

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. Данные вставлены в рабочий лист

Код записанного макроса

Теперь перейдем в редактор Visual Basic и откроем процедуру полный код которой приведен в листинге 20.1.

Листинг 20.1. Процедура ПримерMSQuery

l:Sub ПримерMSQuery()

2:

3: With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC; _

4:  DSN=База данных MS Access;DBQ=c:\Program Fiies\ _

     Microsoft Office\Office\Samples\Борей.mdb; _

     DefaultDir=c:\Program Files\Microsoft ") , _

     Array("Office\Office\Samples;DriverId=25;FIL=MS Access; _

     MaxBufferSize=2048;PageTimeout=5;")), _

     Destination:=Range("A1")).CommandText = Array("SELECT _

     Товары.КодТовара, Товары.Марка, Товары.Цена, _

     Товары.НаСкладе" & Chr(13) & "" & Chr(10) & "FROM _

     'c:\Program Files\Microsoft Office\Office\Samples\ _

     Борей'.Товары Товары" & Chr(13) & "" & Chr(10) & WHERE _

     (Товары.Марка>='20')" & Chr(13) & "" & Chr(10) & _

     "ORDER BY Товары.Цена")

5:  .Name = "Запрос из База данных MSAccess"

6:  .FieldNames = True

7:  .RowNumbers = False

8:  .FillAdjacentFormulas = False

9:  .PreserveFormatting = True

10:  .RefreshOnFileOpen = False

11:  .BackgroundQuery = True

12:  .RefreshStyle = xlInsertDeleteCells

13:  .SavePassword = True

14:  .SaveData = True

15:  .AdjustColumnWidth = True

16:  .RefreshPeriod = 0

17:  .PreserveColumnInfo = True

18:  .Refresh BackgroundQuery:=False

19: End With

20:End Sub

В приведенной процедуре первый оператор (основной в этой процедуре) выполняет метол Add (Добавить). Этот оператор содержит всю информацию о соединении с внешней базой данных, включая ее местоположение и информацию об используемых драйверах:

ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC; _

 DSN=База данных MS Access;DBQ=c:\Program Files\ _

 Microsoft Office\Office\Samples\Борей.mdb; _

 DefaultDir=c:\Program Files\Microsoft "), _

 Array("Office\Office\Samples;DriverId=25;FIL=MS Access; _

 Destination:=Range("A1"))

После установления связи с базой данных задается значение свойства CommandText (Текст команд) объекта таблицы запроса QueryTable. Здесь основным является оператор Select (Выбор), указывающий, какие данные выбраны в базе данных, и задающий фильтр на выбранные данные и порядок их сортировки:

.CommandText = Array("SELECT _

 Товары.КодТовара, Товары.Марка, Товары.Цена, _

 Товары.НаСкладе" & Chr(13) & "" & Chr(10) &"FROM _

 'с:\Program Files\Microsoft Office\Office\Samples\ _

 Борей'.Товары Товары" & Chr(13) & "" & Chr(10) & "WHERE _

 (Товары.Марка>='20')" & Chr(13) & "" & Chr(10) & _

 "ORDER BY Товары.Цена")

Далее в процедуре задаются разнообразные свойства запроса и выбираемых данных. Здесь вы можете еще раз оценить преимущества записи макросов - на самостоятельное написание кода такой процедуры пришлось бы затратить значительное время плюс нелегкая борьба с неизбежными ошибками в коде.

Теперь несколько изменим процедуру. Как вы помните, при создании запроса в диалоговом окне Создание запроса: отбор данных (рис. 20.3) было наложено ограничение на выбираемые данные: отбирались только записи, у которых значения в столбце Цена больше или равны 20. При выполнении макроса вы не сможете задать другое ограничение. Изменим код процедуры, чтобы во время ее выполнения можно было бы задать ограничения на значения в столбце Цена. Код измененной, процедуры приведен в следующем листинге, отличия этого кода от кода листинга 20.1 выделены полужирным шрифтом.

Листинг 20.2. Измененная процедура ПримерMSQuery

1:Sub ПримерMSQuery()

2: Dim sngPrice As Single

3: Dim sMessege As String

4:

5: Worksheets.Add

6: sMessege = "Цена должна быть больше чем"

7: sngPrice = Application.InputBox(>sMessege, "Ввод значения цены", _

     Туре:=1)

8:

9: With ActiveSheet.QueryTables.Add(Connection:=Array(Array("ODBC; _

10:  DSN=База данных MS Access;DBQ=c:\Program Files\ _

      Microsoft Office\Office\Samples\Борей.mdb; _

      DefaultDir=c:\Program Files\Microsoft "), _

      Array("Office\Office\Samples;DriverId=25;FIL=MS Access; _

      Destination:=Range("A1")).CommandText = Array("SELECT _

      Товары.КодТовара, Товары.Марка, Товары.Цена, _

      Товары.НаСкладе" & Chr(13) & "" & Chr(10) & "FROM _

      'c:\Program Files\Microsoft Office\Office\Samples\ _

      Борей".Товары Товары" & Chr(13> & "" & Chr(10) & "WHERE _

      (Товары.Марка)=" & sngPrice & ")" & Chr(13) & ""

      & Chr(10) & "ORDER BY Товары.Цена")

11:  .Name = "Запрос из База данных MS Access"

12:  .FieldNames = True

13:  .RowNumbers = False

14:  .FillAdjacentFormulas = False

15:  .PreserveFormatting = True

16:  .RefreshOnFileOpen = False

17:  .BackgroundQuery = True

18:  .RefreshStyle = xlInsertDeleteCells

19:  .SavePassword = True

20:  .SaveData = True

21:  .AdjustColumnWidth = True

22:  .RefreshPeriod = 0

23:  .PreserveColumnInfo = True

24:  .Refresh BackgroundQuery:=False

25: End With

26:End Sub

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

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

Выполните процедуру. В окно ввода введите число 200 и щелкните на кнопке ОК. Вы увидите, что в рабочую книгу вставлен новый рабочий лист и в нем отображаются данные, у которых в столбце Цена значения больше или равны 200.

Средство MS Query не ограничено работой с простыми таблицами. Можно создавать запросы, основанные на нескольких связанных таблицах базы данных.

Еще один способ использования MS Query состоит в создании файла запроса, сохраняющего все условия запроса. Этот сохраненный запрос затем можно использовать в какой-либо процедуре. Для того чтобы показать работу с сохраненным запросом, сначала выделите любую ячейку с извлеченными данными (например, ячейку А1). Выведите на экран панель инструментов Внешние данные и щелкните на кнопке Изменить запрос этой панели. В последовательно открывающихся диалоговых окнах Создание запроса щелкайте на кнопках Далее, пока не дойдете до окна Создание запроса: заключительный шаг. В этом диалоговом окне щелкните на кнопке Сохранить запрос. В открывшемся диалоговом окне Сохранить как введите имя сохраняемого запроса (в нашем случае можно задать имя Цена) и щелкните на кнопке Сохранить. Затем щелкните на кнопке Готово в окне Создание запроса: заключительный шаг.

Теперь покажем, как можно использовать сохраненный запрос. Вставьте новый лист в рабочую книгу. Начните запись макроса с именем Цена. Выполните команду Данные > Внешние данные > Выполнить сохраненный запрос. Откроется диалоговое окно Выполнить запрос, в котором выберите запрос Цена, а затем щелкните на кнопке Получить данные. Откроется знакомое вам окно Возврат данных в Microsoft Excel (рис. 20.6), в котором щелкните на кнопке ОК. После того как данные будут вставлены в рабочий лист, остановите запись макроса. Перейдите в редактор Visual Basic и откройте код записанного макроса. Этот код приведен в листинге 20.3.

Листинг 20.3. Процедура Цена

1: Sub Цена()

2:

3:  With ActiveSheet.QueryTables.Add(Connection:= _

4:   "FINDER;C:\WINDOWS\Application _

     Data\Microsoft\Запросы\Цена.dqy", Destination _

     :=Range("A1"))

5:   .Name = "Цена"

6:   .FieldNames = True

7:   .RowNumbers = False

8:   .FillAdjacentFormulas = False

9:   .PreserveFormatting = True

10:   .RefreshOnFileOpen = False

11:   .BackgroundQuery = True

12:   .RefreshStyle = xlInsertDeleteCells

13:   .SavePassword = True

14:   .SaveData = True

15:   .AdjustColumnWidth = True

16:   .RefreshPeriod = 0

17:   .PreserveColumnInfo = True

18:   .Refresh BackgroundQuery:=False

19:  End With

20: End Sub

Основное отличие этой процедуры от процедуры ПримерMSQuery заключается в применяемом методе Add. В данной процедуре в этом методе указывается информация для связи с файлом запроса, а не с файлом базы данных. Кроме того, в процедуре Цена не задаются значения свойства CommandText (они хранятся в файле запроса). Как видите, эта процедура значительно проще процедуры ПримерMSQuery. Но она имеет и недостаток: здесь нельзя во время выполнения процедуры ввести ограничения на извлекаемые данные. Но если в вашем приложении надо часто выполнять стандартный запрос, не требующий вмешательства пользователя, то подход с применением сохраненного запроса, вполне себя оправдывает.

Резюме

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

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

Вопросы и ответы

Вопрос. Можно ли посредством MS Query использовать запросы, созданные в Access?

Ответ. И да, и нет. С помощью MS Query можно создать запрос, который будет основываться на запросах, созданных в Access. Но напрямую запросы Access недоступны в Excel (например, путем выполнения команды Данные > Внешние данные > Выполнить сохраненный запрос).

Вопрос. Как изменить запрос, созданный с помощью MS Query?

Ответ. В диалоговом окне Создание запроса: заключительный шаг есть переключатель Просмотр и изменение данных в MS Query. После установки этого переключателя и щелчка на кнопке Готово открывается окно MS Query с интерфейсом, очень напоминающим окно Access для создания запросов.

Практикум

С помощью тестов и упражнений вы проверите, насколько хорошо усвоили изложенный материал. Ответы на вопросы смотрите в Приложении.

Тесты

1. Назовите коллекцию, элемент которой создается методом Add в процедуре использования MS Query.
2. Назовите имя свойства, которое содержит оператор Select при создании запроса.
3. Истинно или ложно следующее утверждение: MS Query может работать только с базами данных Access?
4. Истинно или ложно следующее утверждение: MS Query может извлекать данные одновременно из нескольких таблиц базы данных?
5. Какое основное преимущество импортирования внешних данных с помощью MS Query?
6. Назовите четыре различных способа извлечения внешних данных в Excel.

Упражнение

Создайте копию процедуры ПримерMSQuery и назовите ее СортировкаДанных. Создайте экранную форму с четырьмя переключателями:

• Код продукта
• Марка
• Цена
• На складе

Измените процедуру СортировкаДанных таким образом, чтобы извлекаемые данные сортировались по тому столбцу, которому соответствует выбранный переключатель в экранной форме.

1 Стандарт ODBC постепенно уходит из компьютерной индустрии. В настоящее время Microsoft активно продвигает стандарт OLE DB, который является наследником и ODBC, и OLE. Этот стандарт лучше работает с такими "нестандартными" типами данных, как изображения и звук. Приложения пакета Office 2000 имеют встроенные драйверы OLE DB, поэтому Excel 2000 обращается к драйверам ODBC посредством OLE DB. Об OLE DB подробнее сказано в следующем 21-м часе. - Прим. ред.