Институт управления, бизнеса и права
Опубликован: 25.03.2009 | Доступ: свободный | Студентов: 7087 / 1448 | Оценка: 4.41 / 4.15 | Длительность: 14:33:00
Лекция 14:

Работа с книгами и листами

< Лекция 13 || Лекция 14: 12 || Лекция 15 >
Аннотация: Лекция посвящена описанию частей объектной модели MS Excel, относящихся к книгам - коллекция Workbooks, объект Workbook, и к рабочим листам - коллекция Worksheets, объект Worksheet.

14.1. Коллекция Workbooks - работа с книгами

Основные операции, которые приходится выполнять при работе с книгами - это их автоматическое создание, выбор одной из открытых книг для обработки, открытие книги. Все открытые книги принадлежат коллекции Workbooks.

Практически все часто используемые методы и свойства коллекции Workbooks очень похожи на соответствующие методы и свойства коллекции Documents из объектной модели MS Word.

14.2. Методы Коллекции Workbooks

14.2.1. Add, Close, Open - стандартная работа с книгами

14-01-Excel Workbooks Add.xlsm - пример к п. 14.2.1.

  • Add - создает новую книгу. Новая книга становится активной.
  • Close - закрывает все открытые книги.
  • Open - открывает указанную книгу. Основной параметр метода - имя открываемой книги.

Например, такой код (листинг 14.1.) позволяет создать новую книгу, присвоив ссылку на нее объектной переменной.

Dim obj_Workbook As Workbook
    Set obj_Workbook = Workbooks.Add
Листинг 14.1. Создаем новую книгу

14.2.2. OpenText, OpenXML - открытие различных файлов

Эти методы предназначены для открытия текстовых файлов ( OpenText ) и XML-файлов ( OpenXML ) в виде книг MS Excel.

14.3. Свойства коллекции Workbooks

14-02-Excel Workbooks Properties.xlsm - пример к п. 14.3.

14.3.1. Count

Возвращает количество объектов в коллекции. В данном случае - количество открытых книг.

14.3.2. Item

Позволяет обращаться к выбранному элементу (отдельной книге ) в коллекции. В качестве идентификатора книги может использоваться ее индекс или имя.

В листинге 14.2. мы сначала выводим информацию о количестве книг, а потом - отображаем имя каждой открытой книги.

MsgBox "Открыто" + Str(Workbooks.Count) + _
        " книг"
    For i = 1 To Workbooks.Count
        MsgBox Workbooks.Item(i).Name + _
            " имя открытой книги №" + Str(i)
    Next i
Листинг 14.2. Вывод информации об открытых книгах

14.4. Объект Workbook

Workbook - книга - это аналог документа ( Document ) в Microsoft Word. Многие свойства и методы Workbook действуют аналогично свойствам объекта Document. Однако книга в объектной модели состоит из листов. Значит, для обращения к данным, хранящимся в книге, нужно сначала обратиться к листу.

14.5. Методы объекта Workbook

Методы объекта Workbook очень похожи на методы объекта Document в MS Word. Поэтому здесь мы лишь перечислим их.

14.5.1. Activate, Close, Save, SaveAs, PrintOut

  • Activate - активирует книгу
  • Close - закрывает книгу
  • Save - сохраняет книгу
  • SaveAs - сохраняет книгу с новым именем, в новом формате и т.д.
  • PrintOut - выводит книгу на печать

14.6. Свойства объекта Workbook

14.6.1. Свойства ActiveChart, ActiveSheet, Charts, Sheets, Worksheets, Styles, Names

Эти свойства возвращают различные объекты и коллекции объектов. В частности, ActiveChart возвращает активную диаграмму, ActiveSheet - активный лист книги, а Charts и Sheets возвращают, соответсвенно, коллекции листов, содержащих диаграммы, и всех листов книги. В объект Workbook входят и другие подобные коллекции - они очень похожи на коллекции, входящие в Application.

Чаще всего, работая с объектом Workbook, обращаются к коллекциям Sheets и Worksheets - ведь именно они дают доступ к рабочим листам.

14.6.2. Свойства CodeName, FullName, Name

14-03-Excel Workbook Name.xlsm - пример к п. 14.6.2.

Эти свойства возвращают различные имена книги. Так, CodeName - это имя, под которым книга видна в окне Project Explorer (обычно ЭтаКнига ). FullName - имя файла книги вместе с путем. Name - имя файла книги.

В листинге 14.3. вы можете найти пример вывода различных имен книги.

MsgBox "Кодовое имя текущей книги: " + _
        ActiveWorkbook.CodeName + Chr(13) + _
        "Имя текущей книги: " + _
        ActiveWorkbook.Name + Chr(13) + _
        "Полное имя книги: " + _
        ActiveWorkbook.FullName
Листинг 14.3. Вывод различных имен книги

14.7. События объекта Workbook

14-04-Excel Workbook Events.xlsm - пример к п. 14.7.

В VBA-проекте, который прикреплен к книге, есть объект ЭтаКнига. Если сделать по нему двойной щелчок - будет открыто окно редактора кода, в котором, по обычной схеме, можно выбрать объект - в данном случае это Workbook, а также события, для которых автоматически будет создан обработчик.

Книги в Microsoft Excel могут реагировать на множество событий, среди них следующие, которые происходят при таких обстоятельствах:

  • Activate - при активации книги
  • BeforeClose - перед закрытием книги, если книга изменялась и должен появиться запрос на ее сохранение - происходит перед сохранением.
  • BeforePrint - перед печатью.
  • BeforeSave - перед сохранением.
  • Deactivate - при деактивации.
  • NewSheet - при добавлении нового листа в книгу.
  • Open - при открытии книги.
  • SheetActivate - при активации любого листа
  • SheetBeforeDoubleClick - после двойного щелчка в книге, до стандартной процедуры обработки.
  • SheetBeforeRightClick - перед щелчком правой кнопки мыши.
  • SheetCalculate - при пересчете листа или изменении данных, которые отображаются на диаграмме.
  • SheetChange - при изменении содержимого ячейки.
  • SheetDeactivate - при деактивации листа.
  • SheetFollowHyperlink - при щелчке по гиперссылке.
  • SheetSelectionChange - при изменении выделения.

Например, листинг 14.4. позволяет вывести в окне сообщения информацию об имени измененной ячейки, имени листа, на котором она расположена, и новом содержимом ячейки.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
    ByVal Target As Range)
    MsgBox "Вы отредактировали даные на листе: " & _
        Sh.Name & ", в ячейке " & Target.Address & _
        ". Теперь ячейка содержит следующие данные: " & _
        Target.Value
End Sub
Листинг 14.4. Вывод информации об измененной ячейке

Здесь мы воспользовались параметрами, которые передаются в обработчик события. Параметр Sh содержит ссылку на лист, на котором произошли изменения, а Target представляет собой ссылку на измененную ячейку или диапазон.

14.8. Коллекция Worksheets

Коллекция Worksheets содержит листы открытой книги. Ее свойства и методы служат для работы с ними.

14.9. Методы коллекции Worksheets

14.9.1. Add - добавляем листы в книгу

14-05-Excel Worksheets Add.xlsm - пример к п. 14.9.1.

Позволяет добавить в книгу новый лист. Полный вызов метода выглядит так.

Add(Before, After, Count, Type)

Before (До) и After (После) позволяют задавать позицию листа относительно других листов книги (можно использовать лишь один из них), Count задает количество добавляемых листов, а Type - тип листа. Чаще всего этот метод используют без параметров.

Например, листинг 14.5. добавляет новый лист в книгу, в которой хранится макрос, содержащий этот код:

ThisWorkbook.Worksheets.Add
Листинг 14.5. Добавляем новый лист

А так можно добавить в книгу столько листов, сколько указал пользователь в ответ на вопрос программы (листинг 14.6.).

ActiveWorkbook.Worksheets.Add _
        Count:=Val(InputBox("Введите количество листов"))
Листинг 14.6. Добавляем в книгу указанное количество листов

Обычно листы в книгах MS Excel имеют осмысленные имена. Однако, как вы могли заметить, метод Add не предусматривает задание имени вставляемому листу, оставляя его в значении по умолчанию. Чтобы переименовать добавленный лист, вам придется пользоваться свойством Name объекта Worksheet.

14.9.2. Copy - копируем листы

14-06-Excel Worksheets Copy.xlsm - пример к п. 14.9.2.

Позволяет скопировать лист в новое место книги.

Принимает два параметра - Before и After, которые, соответственно, позволяют задать положение перемещаемого листа до или после какого-то другого листа. Например, листинг 14.7. копирует активный лист в позицию после листа с именем "Данные".

ActiveWorkbook.ActiveSheet.Copy _ 
   After:=Worksheets("Данные")
Листинг 14.7. Создаем копию листа в пределах книги

Если метод вызвать без параметров, то будет создана новая книга, в которую будет помещена копия листа, для которого вызывался этот метод. Например, так, как в листинге 14.8.

ActiveWorkbook.ActiveSheet.Copy
Листинг 14.8. Копируем лист в новую книгу

14.9.3. Delete, Move - удаляем и перемещаем листы

14-07-Excel Worksheets Del Mov.xlsm - пример к п. 14.9.3.

Метод Delete удаляет лист. Например, так мы удаляем первый лист в коллекции Workbooks (листинг. 14.9.)

ActiveWorkbook.Worksheets.Item(1).Delete
Листинг 14.9. Удаляем лист

Обратите внимание на то, что в этом примере мы обращаемся к листу с помощью свойства Item коллекции Worksheets, которое позволяет работать с листом, используя индекс или имя.

Move позволяет переместить лист на новое место в книге. В качестве параметров принимает параметры Before и After, которые задают его положение до или после заданного в них листа. Например, так (листинг 14.10.) мы перемещаем активный лист в книге в позицию, предшествующую листу с именем Данные.

ActiveWorkbook.ActiveSheet.Move _ 
   Before:=Worksheets("Данные")
Листинг 14.10. Перемещаем лист
< Лекция 13 || Лекция 14: 12 || Лекция 15 >
Евгений Ушаков
Евгений Ушаков

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

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

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

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

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

Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Михаил Алексеев
Михаил Алексеев
Россия, Уфа, УГАТУ, 2002