Опубликован: 25.03.2009 | Уровень: для всех | Доступ: платный | ВУЗ: Институт управления, бизнеса и права
Лекция 16:

Дополнительные сведения о программировании для MS Excel

< Лекция 15 || Лекция 16: 12 || Лекция 17 >

16.3. Работа с базами данных

В примерах этого раздела используется файл Database.accdb, который должен быть расположен в корневом каталоге диска C.

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

16.3.1. OpenDatabase и QueryTable

16-04-Excel OpenDatabase.xlsm - пример к п. 16.3.1.

Самый простой и доступный способ импортировать информацию из базы данных в Microsoft Excel, это - воспользоваться специальным методом рабочего листа. Речь идет о методе OpenDatabase. Он предназначен для создания новой книги, которая содержит лист с информацией, полученной из базы данных. Получение информации из базы данных в Excel может быть полезным, например, для анализа этой информации средствами Excel.

Полный вызов метода выглядит так:

OpenDatabase(Filename, CommandText, CommandType, BackgroundQuery, ImportDataAs)

Рассмотрим параметры метода.

  • Filename - имя и расположение базы данных.
  • CommandText - Текст запроса к базе данных. Здесь можно указать имя таблицы базы данных, которая должна быть открыта.
  • CommandType - тип запроса - xlCmdCube (куб), xlCmdList (список), xlCmdSql (SQL), xlCmdTable (таблица).
  • BackgroundQuery - если установлен в True - обработка данных ведется в фоновом режиме, если в False - в обычном.
  • ImportDataAs - способ импорта данных. Может принимать два значения - первое - xlPivotTableReport (данные будут импортированы в виде сводной таблицы - Pivot Table ), второе - xlQueryTable (данные будут импортированы с помощью QueryTable - в виде обычной таблицы).

Чтобы рассмотреть пример использования этой команды, создадим простую базу данных, состоящую из двух таблиц. Первая таблица представляет собой список клиентов, вторая - список их покупок, где учитывается лишь сумма покупки на определенную дату. Таблица клиентов имеет имя Клиенты, таблица покупок - имя Покупки. Импортируем с помощью метода OpenDatabase таблицу Покупки в документ MS Excel. Предположим, что база данных хранится на диске C:, ее имя - Database.accdb. Добавим на лист MS Excel кнопку, содержащую такой код (листинг 16.5.)

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlQueryTable
Листинг 16.5. Импорт данных из БД с использованием QueryTable

После нажатия на кнопку будет создана новая книга, лист которой, названный по имени базы данных, будет содержать импортированные данные (рис. 16.1.).

QueryTable в документе

Рис. 16.1. QueryTable в документе

Чтобы импортировать данные как PivotTable, нам понадобится такой код (листинг 16.6.) - его мы добавим в обработчик события Click другой кнопки на рабочем листе книги-примера.

Workbooks.OpenDatabase _
        Filename:="C:\Database.accdb", _
        CommandText:="Покупки", _
        CommandType:=xlCmdTable, _
        BackgroundQuery:=True, _
        ImportDataAs:=xlPivotTableReport
Листинг 16.6. Импорт данных из БД с использованием PivotTable

На рис. 16.2. вы можете видеть результат выполнения команды - сводную таблицу, с которой можно продолжать дальнейшую работу.

Сводная таблица в документе MS Excel

увеличить изображение
Рис. 16.2. Сводная таблица в документе MS Excel

Теперь рассмотрим еще один метод получения информации из БД.

16.3.2. ADO

16-05-Excel ADODB Query.xlsm - пример к п. 16.3.2.

QueryTable можно добавить на рабочий лист, предварительно настроив ее параметры.

Объекты QueryTable объединены в коллекцию QueryTables. Важнейший метод этой коллекции - Add - он добавляет новую таблицу в указанную позицию на листе. Вызов метода Add выглядит так:

WorkBook.QueryTables.Add(Connection, Destination)

В качестве параметра Connection обычно используют объект ADODB.Recordset, о котором ниже, а Destination - это объект Range, который указывает на диапазон (или ячейку), куда будет добавлена QueryTable. Если в Destination задана ячейка, левая верхняя ячейка вставляемой таблицы таблицы совпадет с ячейкой.

Для работы с базами данных используется объектная модель ADO. Чтобы подключить ее к проекту, выберите в окне References пункт Microsoft ActiveX Data Object 2.8 Library - обращаться к ней можно, используя имя объекта ADODB.

ADO - это очень мощный механизм для доступа к источникам данных. Здесь мы рассмотрим методику получения информации из БД с использованием ADO. Нас будут интересовать несколько ключевых объектов ADO.

Во-первых - это объект ADODB.Connection, который позволяет установить соединение с базой данных и работать с ней. У объекта Connection есть свойство ConnectionString - оно представляет собой строку, содержащие параметры подключения к базе данных, в частности - адрес файла базы данных и имя драйвера. Метод Open объекта Connection используется для открытия соединения, заданного свойством ConnectionString.

Во-вторых - объект ADODB.RecordSet - он позволяет получать из открытой базы данных определенные порции информации.

Для получения данных используется метод объекта Open, которому передается запрос на получение данных, а так же - открытое соединение.

Давайте рассмотрим пример. Здесь мы подключаемся к базе данных и создаем Query Table на основе объекта RecordSet, в котором хранится информация, полученная из базы (листинг 16.7.).

'Для хранения ссылки на
    'Query Table
    Dim obj_Query As QueryTable
    'Для ссылки на соединение с
    'базой данных
    Dim obj_ADOConn As ADODB.Connection
    'Для ссылки на набор записей,
    'полученный из БД
    Dim obj_ADORec As ADODB.Recordset
    'Если в ячейке A5 есть данные     'значит мы уже вставляли сюда Query Table
    'если данных нет - начинаем работу с БД
    If ActiveSheet.Range("A5") <> "" Then
        MsgBox "Уже есть Query Table в этом диапазоне"
    Else
    'Создаем новое соединение
    Set obj_ADOConn = New ADODB.Connection
    'Вносим в ConnectionString параметры
    'соединения. В Provider - имя драйвера,
    'который используется для доступа к
    'БД, а так же - параметр Data Source
    'который отвечает за адрес источника данных
    obj_ADOConn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=C:\Database.accdb"
    'Подключаемся к базе данных
    obj_ADOConn.Open
    'Создаем новый объект RecordSet     'Он хранит результат запроса к БД
    Set obj_ADORec = New ADODB.Recordset
    'Выполняем запрос
    'В параметре Source хранится строка
    'SQL-запроса
    'В ActiveConnnection - открытое соединение
    obj_ADORec.Open _
        Source:="SELECT * FROM Покупки", _
        ActiveConnection:=obj_ADOConn
    'Создаем новую Query Table, в качестве
    'источника данных передаем заполненный
    'данными RecordSe
    Set obj_Query = _
        ActiveSheet.QueryTables.Add _
        (obj_ADORec, Range("A5"))
    'Используем метод Refresh для того
    'чтобы таблица, заполненная данными
    'была отображена на листе
    obj_Query.Refresh
    End If
Листинг 16.7. Импорт данных из БД с использованием ADO

Если вы хотите эффективно работать с базами данных - вам придется научиться строить SQL-запросы, изучить особенности взаимодействия с различным видами БД и так далее.

16.4. Работа с диаграммами

16-06-Excel Chart.xlsm - пример к п. 16.4.

Для работы с диаграммами используют объект Chart. Чтобы добавить диаграмму на лист можно применить методом AddChart коллекции Shapes..

Такой код (листинг 16.8.) добавляет диаграмму на активный лист:

ActiveSheet.Shapes.AddChart
Листинг 16.8. Добавление диаграммы на активный лист

Когда диаграмма добавлена, можно настроить ее свойства, в частности, при помощи метода SetSourceData задать диапазон (объект типа Range ), содержащий информацию, которая должна быть визуализирована. Этот метод принимает два параметра. Первый - Source - отвечает за источник данных, второй - PlotBy - определяет, как берутся данные для диаграммы - по столбцам ( xlColumns ) или по строкам ( xlRows ).

Так же после добавления диаграммы обычно настраивают ее тип - это делается с помощью свойства CharType. Оно может принимать одно из более чем 70 значений типа xlChartType. Например, xlConeCol - это трехмерная коническая диаграмма, xlPie - круговая диаграмма, xlLineMarkers - график с маркерами.

Рассмотрим пример (листинг 16.9.). Добавим на рабочий лист обычную линейную диаграмму, используя диапазон значений, выделенных пользователем.

'Для хранения ссылки
    'на диаграмму
    Dim obj_Chart As Chart
    'Для хранения ссылки на
    'диапазон входных значений
    Dim obj_Range As Range
    Set obj_Range = Selection
    'Добавляем новую диаграмму и
    'тут же выделяем ее
    ActiveSheet.Shapes.AddChart.Select
    Set obj_Chart = ActiveChart
    'Настраиваем исходные данные
    'для диаграммы
    obj_Chart.SetSourceData _
        Source:=obj_Range, _
        PlotBy:=xlRows
    'Устанавливаем тип для диаграммы
    obj_Chart.ChartType = xlLine
Листинг 16.9. Добавление диаграммы на лист Excel

16.5. Выводы

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

< Лекция 15 || Лекция 16: 12 || Лекция 17 >
Евгений Ушаков
Евгений Ушаков

Добрый день! Поясните, пожалуйста, 3000 р. стоит документ об окончании курса. Если он не нужен, то можно учиться бесплатно?

Александр Новиков
Александр Новиков

Добрый день! Очень нужен справочник по объектам VBA c описание их методов и свойств.

Кто-нибудь знает, где можно приобрести?

(Он конечно есть в VBA - Object browser, но не очень удобный)

Евгений Кленов
Евгений Кленов
Россия
Пизда Хуй
Пизда Хуй
Россия, Зимбабве