Определяем количество открытых книг, страниц и их имена. Конструкция For Each … Next

В статье «Иерархия(структура) рабочей книги Excel» была рассмотрена структура рабочей книги, листа и приложения в целом. Из этого возникает вопрос, каким образом можно получить, например кол-во страниц в книге Excel, или их имена. С количеством все просто, к примеру, для того чтобы получить кол-во открытых книг, достаточно воспользоваться следующей командой:
Application.Workbooks.Count

Чтобы получить кол-во страниц в активной книге:
ActiveWorkbook.Worksheets.Count

Если же требуется получить кол-во страниц в определенной книге для этого воспользуйтесь:
Workbooks(1).Worksheets.Count
или
Workbooks(«имя книги.xls»).Worksheets.Count

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

Теперь перейдем к рассмотрению работы с конструкцией For Each … Next.

Синтаксис конструкции For Each … Next

For Each Элемент In Коллекция(Группа)
       [команды]
[Exit For]
       [команды]
Next [Элемент]

Данная конструкция позволяет поочередно обратится к каждому элементу группы или коллекции. Коллекция это группа одинаковых (однотипных) объектов. Например, массивы, или коллекции WorkBooks, Worksheets .
For Each … Next по принципу работы похож на цикл For…Next, который был рассмотрен здесь. Схожесть в том что, перебирается каждый элемент коллекции или группы по порядку. Не буду томить скучной теорией перейдем к рассмотрению примеров.

Пример 1. Необходимо получить имена всех открытых книг Excel. Имена всех рабочих книг доступны в коллекции Workbooks.

Sub GetWorkBooksName() Dim WBooks As Workbook Dim Msg As String    For Each WBooks In Workbooks       Msg = Msg & WBooks.Name & Chr(13)    Next WBooks   MsgBox Msg End Sub

Пример 2. Необходимо получить имена всех страниц в активной книге. Имена страниц содержатся в коллекции Worksheets.

Sub GetWorkSheetsName() Dim Item As Worksheet    For Each Item In ActiveWorkbook.Worksheets      MsgBox «Отображаемое имя листа » & CStr(Item.Index) & » — » & Item.Name    Next Item     End Sub

Пример 3. Необходимо посчитать кол-во страниц во всех открытых книгах Excel.

Sub GetAllCountSheets() Dim WBooks As Workbook Dim kolSheet As Long Dim Msg As String kolSheet = 0    For Each WBooks In Workbooks       kolSheet = kolSheet + Workbooks(WBooks.Name).Worksheets.Count    Next WBooks MsgBox «Всего страниц в открытых книгах: » & CStr(kolSheet)     End Sub

Пример 4. Необходимо преобразовать весь текст в выделенном диапазоне ячеек в верхний регистр т.е. сделаем буквы большими. Для этого воспользуемся коллекцией Selection (тип Range — хранит область выделенных ячеек) и функцией перевода символов в верхний регистр UCase (если необходим перевод в нижний регистр, то воспользуйтесь функцией LCase).

Sub RangeUpCase() Dim Cell As Range      For Each Cell In Selection     Cell.Value = UCase(Cell.Value)    Next Cell End Sub

Выделяем нужные ячейки или диапазон ячеек с текстом и запускаем макрос.

Пример 5. И заключительный пример, закроем все рабочие книги Excel, кроме активной, без сохранения.

Sub CloseBooks() Dim WBook As Workbook       For Each WBook In Workbooks      If WBook.Name <> ActiveWorkbook.Name Then WBook.Close False    Next WBook     End Sub

За закрытие книги Excel отвечает команда WBook.Close. Параметр False указывает, что закрываем все книги без сохранения. Если этот параметр изменить на True, то все книги будут закрываться предварительно сохранившись. Если же этот параметр убрать, то при закрытии книг с внесенными изменениями выскочит диалоговое окно с вопросом о сохранении.

Все примеры, рассмотренные в статье, Вы можете скачать ниже. Все.

 

Прикрепленный файл: Пример работы For Each.zip