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

Теперь перейдем в редактор 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. Эта технология самая эффективная и гибкая среди всех аналогичных технологий, рассматриваемых в этой книге.