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

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.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.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.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

Здесь мы воспользовались параметрами, которые передаются в обработчик события. Параметр 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.6.).

ActiveWorkbook.Worksheets.Add _
        Count:=Val(InputBox("Введите количество листов"))

Обычно листы в книгах 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.8.

ActiveWorkbook.ActiveSheet.Copy

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

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

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

ActiveWorkbook.Worksheets.Item(1).Delete

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

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

ActiveWorkbook.ActiveSheet.Move _ 
   Before:=Worksheets("Данные")