Операции с объектами Microsoft Excel

Создание рабочего листа "Товары.xls"

Сначала рассмотрим, как можно выполнять операции с рабочим листом Microsoft Excel из программы Access. Для этого нам потребуется файл Товары.хls, содержащий рабочую книгу Excel с единственным рабочим листом "Товары", который представляет собой список товаров из демонстрационной базы данных "Борей". Вы можете использовать готовый файл, находящийся на сопровождающем книгу компакт-диске, либо создать его самим, экспортировав в Excel таблицу "Товары" с помощью команды меню Сервис, Связи с Office, Анализ в MS Excel (Tools, Office Links, Analyze It with Microsoft Excel) (см. разд. "Быстрый экспорт данных в другие приложения Microsoft Office"гл. 3).

Для того чтобы подготовить файл Товары.xls к дальнейшим экспериментам, выполните следующие действия:

1. Откройте файл Товары.xls, запустив Microsoft Excel.
   
2. Выделите ячейки с А4 по D12 в рабочем листе. Выберите команду меню Вставка, Имя, Присвоить (Insert, Name, Define). В диалоговом окне Присвоение имени (Define Name) введите в текстовое поле Имя (Names in workbook) имя диапазона: workRange (рис. 15.25) и щелкните по кнопке ОК. Тем самым создается именованный диапазон, который будет использоваться в последующих примерах.
   

Рис. 15.25. Создание именованного диапазона в таблице "Товары.xls"

3. Выберите в меню Сервис (Tools) команду Надстройки (Adds-Ins) и снимите все флажки в списке Список надстроек (Add-Ins Available) диалогового окна Надстройки (Add-Ins). Удаление надстроек уменьшает время, требующееся для запуска приложения Excel. Чтобы закрыть диалоговое окно, нажмите на кнопку ОК.
   
4. Сохраните изменения, выполнив команду Файл, Сохранить (File, Save) или Файл, Сохранить как (File, Save As).
   
5. Закройте Microsoft Excel.
   

Иерархия объектов VBA приложения Microsoft Excel

Для того чтобы программно работать с объектами Excel, нужно иметь представление об объектной модели Microsoft Excel. Мы не будем здесь подробно описывать эту модель, поскольку она достаточно сложна, представим только ее основные объекты.

• Объект Application является экземпляром собственно Microsoft Excel. Применяя к этому объекту различные методы, можно воспользоваться практически любыми командами меню Excel. Объект Application обладает свойствами ActiveWorkbook и
  

  ActiveSheet, которые указывают на текущие объекты Workbook (рабочая книга) и Worksheet (рабочий лист). Можно указать Excel.Application в качестве значения аргумента <класс> функций CreateObject () и GetObjectO, а также в операторе Dim objPlMH As New <класс>.
  

• Основной объект Excel — Workbook (рабочая книга). С помощью этого объекта осуществляется доступ к свойствам и структуре рабочей книги Excel. Объекты workbook — это файлы, которые состоят из нескольких объектов, создаваемых Microsoft Excel: Worksheet (рабочий лист) и Chart (диаграмма). Объекты Worksheet и Chart содержатся В семействах Worksheets и Charts.
  
• Объекты Worksheet являются подчиненными по отношению к объекту Workbook и обеспечивают доступ к свойствам и структуре рабочих листов книги Excel. Основным объектом взаимодействия приложений Microsoft Access 2002 и Microsoft Excel 2002 является именно объект Worksheet. Имеется возможность перенести информацию из строк и столбцов объекта Recordset приложения Microsoft Access в ячейки объекта Worksheet приложения Microsoft Excel и наоборот. Если в качестве значения аргумента <класс> функции GetObject() либо в операторе Dim оbj Имя As New <класс> указать Excel.Sheet, то по умолчанию откроется первый объект семейства Worksheets — первый рабочий лист (ActiveSheet) объекта Workbook.
  
• Объекты sheet аналогичны объектам Worksheet, но они входят в семейство Sheets, которое включает в себя как объекты Worksheet, так и объекты Chart, представляющие листы диаграмм в рабочей книге.
  
• Объект Range — диапазон ячеек рабочего листа Excel. Это могут быть отдельная ячейка, строка, столбец, прямоугольная область рабочего листа или несвязанный (произвольный набор ячеек) или трехмерный диапазон ячеек, включающий ячейки на нескольких рабочих листах. Можно получить либо установить значения диапазона ячеек, определив объект Range. Таких объектов, как ячейка, строка, столбец просто не существует. Именно объект Range со своими свойствами и методами обеспечивает работу как с отдельной ячейкой, так и с их диапазоном. Для ссылки на необходимую группу ячеек можно использовать имя диапазона, состоящего из этих ячеек. Если именованные диапазоны отсутствуют на рабочем листе, можно использовать метод Cells объекта worksheet, чтобы задать координаты ячейки или группы ячеек. Ниже приводятся два способа изменения значения ячейки А1:
  

  ActiveSheet.Range ("Al").Value = 7
  

  ActiveSheet.Cells (1,1).Value = 7
  

Microsoft Excel предоставляет также многие другие объекты для применения их в качестве объектов приложения сервера, но описанные выше типы являются наиболее часто используемыми в технологии автоматизации с помощью Access VBA.

Открытие существующего рабочего листа Excel и работа с ним

Прежде чем работать с автоматизированными объектами Microsoft Excel, установим ссылку на библиотеку объектов Microsoft Excel. Для этого:

1. Запустите Access и, если необходимо, откройте базу данных "Борей". Вызовите редактор VBA, открыв любой модуль.
   
2. Выберите в окне редактора VBA команду меню Сервис, Ссылки (Tools, .References), чтобы открыть диалоговое окно References (Ссылки).
   
3. Установите флажок у элемента Microsoft Excel 10.0 Object Library (рис. 15.26), затем нажмите на кнопку ОК, чтобы закрыть окно References.
   

Рис. 15.26. Добавление ссылки в редакторе VBA на объектную библиотеку Microsoft Excel 2002

Команды автоматизации удобно изучать при помощи окна отладки Immediate. Поэтому, выведите данное окно на экран, если оно не отображается. Для этого достаточно нажать соответствующую кнопку на панели инструментов или комбинацию клавиш <Ctrl>+<G>.

Чтобы программно открыть рабочий лист рабочей книги "Товары":

1. Закройте Excel, если он запущен.
   
2. Создайте новый модуль, выполнив команду меню Insert, Module (Вставка, Модуль).
   
3. Добавьте в раздел описаний следующие описания переменных:
   

   Private xlaProd As Excel.Application

   Private xlwProd As Excel.Workbook

   Private xlsProd As Excel.Worksheet
   

4. Введите в окне отладки следующий оператор (рис. 15.27):
   

   Set xlwProd = GetObject(CurDir & "\Товары.хls","Excel.Sheet")
   

При нажатии затем на клавишу <Enter> приложение Microsoft Excel запускается в режиме /automation. Функция CurDir возвращает полное имя текущей папки. Если файл Товары.хls был сохранен где-нибудь в другом месте, измените в предыдущем операторе путь к этому файлу. В зависимости от скорости функционирования компьютера, запуск Excel может продолжаться достаточно долю. Загрузка приложения Excel завершена, когда в строке состояния окна отладки надпись Выполнение (Running) исчезает и появляется надпись Готово (Ready). В результате будет создан экземпляр класса Application Microsoft Excel и переменной xlwProd будет присвоена ссылка на объект Workbook. Обратите внимание, что функция Getobject () открывает скрытый экземпляр приложения Excel, значок Excel не появляется на панели задач и интерактивно обратиться к рабочей книге Excel нельзя.

Замечание

В данном операторе аргумент Excel. Sheet является необязательным. Если его не указать, то тип создаваемого объекта будет определен автоматически по расширению файла, указанного в первом аргументе.

Рис. 15.27. Команды для автоматического запуска приложения Microsoft Excel

5. Чтобы убедиться, что рабочая книга открыта и мы можем к ней обратиться, введите в окне отладки следующую команду ?xlwProd.Name
   

Свойство Name созданного объекта workbook содержит имя файла Excel: Това-pbi.xls (рис. 15.28).

Рис. 15.28. Команды, позволяющие читать и устанавливать значения отдельных ячеек в рабочем листе "Товары"

6. Файл Toвapы.xls содержит только один объект Worksheet, поэтому рабочий лист "Товары" является активным рабочим листом — объектом ActiveSheet. Чтобы убедиться в этом, введите в окно отладки команду:
   

   ?xlwProd. ActiveSheet.Name
   

   Свойство Name этого объекта содержит имя рабочего листа: Товары.
   

7. Теперь попробуйте обратиться к первой ячейке рабочего листа. Введите ?xlwProd.ActiveSheet.Celled, 1). После короткой паузы появится ожидаемый результат — строка "Код товара". Это заголовок первого столбца таблицы.
   
8. Метод Cells позволяет обратиться к любой ячейке рабочего листа. Введите ?xlwProd. ActiveSheet. Cells (R, С), где R — номер строки, а С — номер столбца заданной ячейки, т. е. ее координаты (рис. 15.28).
   
9. Можно изменить содержимое ячейки, если ввести выражение вида: xlwProd.ActiveSheet.Cells(2,2).Value = "brown rice". Подобно тому как многие объекты Access при введении имени объекта возвращают его значение, метод Cells не требует явного указания свойства Value по умолчанию. Чтобы убедиться в том, что содержимое ячейки изменилось, введите ?xlwProd.ActiveSheet.Cells (3,2), не дописывая выражения .Value.
   

Для установления значения ячейки можно также использовать свойство formula. Преимущество использования свойства formula состоит в возможности его применения с целью введения формул с использованием "родного" синтаксиса Microsoft Excel, т. е. в виде ссылок на конкретные ячейки, например "=А2+С6".

Использование именованных диапазонов ячеек

Если в рабочем листе Excel создан именованный диапазон ячеек, то можно получить значения ячеек, содержащихся в этом диапазоне, если сослаться на свойство Range объекта Worksheet. Сначала посмотрим, какие именованные диапазоны присутствуют в открытом нами объекте. Введите в окно отладки команду (рис. 15.29)

?xlwProd.Names(1).Name

Семейство Names представляет все имена, определенные в рабочей книге. В данном случае первый элемент этого семейства содержит имя диапазона: WorkRange.

Можно посмотреть не только имя, но и что собой представляет этот диапазон. Введите команду

?xlwProd.Names(1).Value

Результат будет: =Товары!$А$4 :$D$12,

т. е. прямоугольная область А4—D12 на рабочем листе "Товары".

На рис. 15.29 приведены выражения для управления объектом Range.

Рис. 15.29. Примеры использования именованного диапазона

Пусть переменная xlsProd ссылается на рабочий лист "Товары". Для этого введите команду:

Set xlsProd = xlwProd.ActiveSheet.

Для указания конкретной ячейки внутри именованного объекта Range можно использовать следующий оператор:

?xlsProd.Range("WorkRange").Cells(1,1)

Здесь используется свойство Range объекта Worksheet для доступа к именованному диапазону, а затем свойство Cells объекта Range — для указания конкретной ячейки в диапазоне. Первая цифра указывает строку, а вторая — столбец.

Для того чтобы обратиться к объекту, который находится на уровень выше в иерархии объектов модели, можно воспользоваться свойством Parent. На рис. 15.29 представлено, как обратиться к рабочей книге Excel, содержащей текущий рабочий лист, и как установить объектную переменную xlaProd, которая должна ссылаться на объект Application Microsoft Excel:

Set хlwРабочаяКнига = хlsРабочийЛист.Parent.

Закрытие объектов Workbook и Application

Объект Microsoft Worksheet закрыть нельзя. Для закрытия объекта Excel Workbook может быть использован метод Close, а для выхода из приложения — метод Quit. Следующие операторы закрывают объект Workbook и затем осуществляют выход из приложения сервера автоматизации, освобождая системные ресурсы:

xlwProd.Close

xlaProd.Quit

Set xlsProd = Nothing

Set xlwProd = Nothing

Set xlaProd = Nothing

Если программно были внесены изменения в рабочем листе, то при закрытии объекта Workbook будет выдан вопрос о необходимости сохранения изменений. Если вы не хотите, чтобы пользователь получил такой вопрос, введите аргумент False для метода Close. Чтобы гарантировать освобождение всех ресурсов, необходимо освободить все использованные объектные переменные.

Замечание

После присвоения переменной, указывающей на объект Application значения Nothing, соответствующее приложение не закрывается, хотя память, занятая переменной, освобождается. Поэтому необходимо закрывать приложение с помощью метода Quit перед освобождением соответствующей объектной переменной.

Создание рабочего листа Excel с помощью кода автоматизации

Те же действия, что происходят при нажатии кнопки Анализ в MS Excel, можно осуществить при помощи кода автоматизации VBA. Преимуществом такого способа является возможность форматировать созданный объект специально под нужды конкретного приложения. Рассмотрим функцию CreateCustomSheet (), создающую новый объект Worksheet и заполняющую его данными из таблицы "Товары" базы данных Microsoft Access:

Function CreateCustomSheet() As Integer

'Создание рабочего листа MS Excel из таблицы "Товары"

'Описание локальных переменных

'(Объектные переменные описаны на уровне модуля)

Dim сйэБорей As Database 'Текущая база данных

Dim rstProd As Recordset 'Объект Recordset

Dim intRow As Integer 'Счетчик строк

Dim intCol As Integer 'Счетчик столбцов

'Открытие таблицы в текущей базе данных

Set dbБорей = CurrentDb()

Set rstProd = dbBopeu.OpenRecordset("Товары", dbdpenTable)

DoCmd.Hourglass True 'Создание нового объекта Excel Workbook

Set xlwProd = CreateObject("Excel.Sheet") ''Создание объекта

Application для применения метода Quit

Set xlaProd = xlwProd.Parent

intRow = 1

intCol = 1

rstProd.MoveFirst 'Переход к первой записи

Do Until rstProd.EOF

'Цикл с шагом в одну запись

for intCol = 1 То rstProd.Count

'Цикл с шагом в одно поле

If (Not IsNull(rstProd(intCol -1))) Then

xlwProd.ActiveSheet.Cells(intRow, intCol).Value =

CStr(rstProd(intCol -I}}

End If

Next intCol

rs t Prod.MoveNext intRow = intRow + 1 Loop

for intCol = 1 To xlwProd.ActiveSheet.Columns.Count

'Форматирование каждого столбца рабочего листа

xlwProd.ActiveSheet.Columns(intCol).Font.Size = 8

xlsCust.ActiveSheet.Columns(intCol).AutoFit If intCol = 8 Then

'Выравнивание по левому краю числовых и

'смешанных почтовых кодов

xlwProd.ActiveSheet.Columns(intCol).HorizontalAlignment = _

xlLeft

End If

Next

intCol DoCmd.Hourglass False

xlwProd.SaveAs (CurDir & "\Товары_2.xls")

xlaProd.Quit End Function

Тип данных, возвращаемых выражением rstProd(intCol-l), следует специально изменить с variant на string при помощи функции cstr(), иначе Microsoft Excel вместо нужной величины отобразит в соответствующем столбце #н/д (#N/A#). Если объект Recordset содержит поля, типы которых отличны от Text, то для определения типа данных в столбце используйте соответствующую функцию СТуре().

Константа xlLeft, присвоенная в качестве значения свойству HorizontalAlignment восьмого столбца, представляет собой встроенную константу Excel, определяемую в тот момент, когда устанавливается ссылка на объектную библиотеку Microsoft Excel 10.0 Object Library. Выделение элемента Constants (Константы) в списке Модули/ Классы окна просмотра объектов при подключенной библиотеке Excel отображает константы xlConst. На рис. 15.30 приведено числовое значение константы xlLeft, которая является одной из констант для задания значения свойства HorizontalAlignment.

Рис. 15.30. Значения встроенных констант xlConst в окне Object Browser Access

Ввод оператора ? CreateCustomSheet () в окне отладки запускает функцию, которую мы рассматривали выше. На рис. 15.31 приведена рабочая книга "ToBapы_2.xls" с рабочим листом, созданным при помощи функции CreateCustomSheet () и открытым в Microsoft Excel.

Рис. 15.31. Часть рабочего листа Excel, созданного из таблицы "Товары"