RSS

Определяем количество открытых книг, страниц и их имена. Конструкция 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