В приведенной процедуре первый оператор (основной в этой процедуре) выполняет метол Add (Добавить). Этот оператор содержит всю информацию о соединении с внешней базой данных, включая ее местоположение и информацию об используемых драйверах:
После установления связи с базой данных задается значение свойства CommandText (Текст команд) объекта таблицы запроса QueryTable. Здесь основным является оператор Select (Выбор), указывающий, какие данные выбраны в базе данных, и задающий фильтр на выбранные данные и порядок их сортировки:
Далее в процедуре задаются разнообразные свойства запроса и выбираемых данных. Здесь вы можете еще раз оценить преимущества записи макросов - на самостоятельное написание кода такой процедуры пришлось бы затратить значительное время плюс нелегкая борьба с неизбежными ошибками в коде.
Теперь несколько изменим процедуру. Как вы помните, при создании запроса в диалоговом окне Создание запроса: отбор данных (рис. 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\ _
В начале этой процедуры объявляются две переменные. Переменная sngPrice хранит значение, введенное пользователем. Вторая переменная, sMessege, используется для отображения текста в окне ввода.
Выполнение процедуры начинается с создания нового рабочего листа, таким образом, результат каждого выполнения процедуры будет представлен на отдельном листе. Далее отображается окно ввода, где пользователь должен ввести число, которое затем будет помещено в оператор Select.
Выполните процедуру. В окно ввода введите число 200 и щелкните на кнопке ОК. Вы увидите, что в рабочую книгу вставлен новый рабочий лист и в нем отображаются данные, у которых в столбце Цена значения больше или равны 200.
Средство MS Query не ограничено работой с простыми таблицами. Можно создавать запросы, основанные на нескольких связанных таблицах базы данных.
Еще один способ использования MS Query состоит в создании файла запроса, сохраняющего все условия запроса. Этот сохраненный запрос затем можно использовать в какой-либо процедуре. Для того чтобы показать работу с сохраненным запросом, сначала выделите любую ячейку с извлеченными данными (например, ячейку А1). Выведите на экран панель инструментов Внешние данные и щелкните на кнопке Изменить запрос этой панели. В последовательно открывающихся диалоговых окнах Создание запроса щелкайте на кнопках Далее, пока не дойдете до окна Создание запроса: заключительный шаг. В этом диалоговом окне щелкните на кнопке Сохранить запрос. В открывшемся диалоговом окне Сохранить как введите имя сохраняемого запроса (в нашем случае можно задать имя Цена) и щелкните на кнопке Сохранить. Затем щелкните на кнопке Готово в окне Создание запроса: заключительный шаг.
Теперь покажем, как можно использовать сохраненный запрос. Вставьте новый лист в рабочую книгу. Начните запись макроса с именем Цена. Выполните команду Данные > Внешние данные > Выполнить сохраненный запрос. Откроется диалоговое окно Выполнить запрос, в котором выберите запрос Цена, а затем щелкните на кнопке Получить данные. Откроется знакомое вам окно Возврат данных в Microsoft Excel (рис. 20.6), в котором щелкните на кнопке ОК. После того как данные будут вставлены в рабочий лист, остановите запись макроса. Перейдите в редактор Visual Basic и откройте код записанного макроса. Этот код приведен в листинге 20.3.
