НОУ ІНТУІТ, Лекція, Додаткові відомості про програмування для MS Excel

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

У прикладах цього розділу використовується файл Database.accdb, який має бути розташований у кореневому каталозі диска C.

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

16.3.1. OpenDatabase та QueryTable

Найпростіший і доступніший спосіб імпортувати інформацію з бази даних у Microsoft Excel, це скористатися спеціальним методом робочого листа. Йдеться про метод OpenDatabase. Він призначений для створення нової книги, яка містить аркуш з інформацією, одержаною з бази даних. Отримання інформації з бази даних Excel може бути корисним, наприклад, для аналізу цієї інформації засобами Excel.

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

Розглянемо параметри методу.

  • 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)

Після натискання на кнопку буде створено нову книгу, аркуш якої, названий на ім'я бази даних, міститиме імпортовані дані (рис. 16.1.).

інтуіт

Щоб імпортувати дані як PivotTable, нам знадобиться такий код (листинг 16.6.) – його ми додамо в обробник події Click іншої кнопки на робочому аркуші книги-прикладу.

На рис. 16.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.RecordSet – він дозволяє отримувати з відкритої бази даних певні порції інформації.

Для отримання даних використовується метод об'єкта Open, якому передається запит на отримання даних, а також відкрите з'єднання.

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

Якщо ви бажаєте ефективно працювати з базами даних - вам доведеться навчитися будувати SQL-запити, вивчити особливості взаємодії з різними видами БД і таке інше.

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

Для роботи з діаграмами використовують об'єкт Chart. Щоб додати діаграму на лист можна застосувати методом AddChart колекції Shapes.

Такий код (листинг 16.8) додає діаграму на активний лист :

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

Також після додавання діаграми зазвичай налаштовують її тип - це робиться за допомогою властивості CharType. Воно може приймати одне з більш ніж 70 значень типу xlChartType. Наприклад,xlConeCol – це тривимірна конічна діаграма, xlPie – кругова діаграма, xlLineMarkers – графік з маркерами.

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

16.5. Висновки

У цій лекції ми розглянули деякі додаткові можливості програмування для MS Excel. Наше наступне заняття присвячене практичним прикладам програмування для MS Excel.