Как сделать активной книгу excel vba?

Управление рабочими книгами и листами в VBA

Настоящая заметка продолжает знакомство с VBA, в ней приводятся примеры управления рабочими книгами и листами Excel с помощью VBA.[1]

Рис. 1. Столбцы и строки скрыты за исключением рабочего диапазона

Скачать заметку в формате Word или pdf, примеры в архиве (политика безопасности провайдера не позволяет загружать файлы Excel с поддержкой макросов)

Сохранение всех рабочих книг

Следующая процедура циклически просматривает все рабочие книги в коллекции Workbooks и сохраняет каждый файл, который сохранялся ранее.

Public Sub SaveAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Path <> » » Then Book.Save
Next Book
End Sub

Обратите внимание на то, как используется свойство Path. Если для какой-либо рабочей книги свойство Path не задано, значит, файл еще не сохранялся (это новая рабочая книга). Данная процедура игнорирует такие рабочие книги и сохраняет только те из них, свойство Path которых имеет ненулевое значение.

Сохранение и закрытие всех рабочих книг

Следующая процедура циклически просматривает коллекцию Workbooks. Программа сохраняет и закрывает все рабочие книги.

Sub CloseAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name <> ThisWorkbook.Name Then
Book.Close savechanges:=True
End If
Next Book
ThisWorkbook.Close savechanges:=True
End Sub

Обратите внимание, что процедура использует оператор If, чтобы определить, содержит ли данная рабочая книга текущий выполняемый код. Это необходимо, так как при закрытии рабочей книги, содержащей процедуру, программа автоматически завершает свое выполнение, причем остальные рабочие книги не будут сохранены и закрыты.

Частичное сокрытие элементов рабочего листа

В примере из этого раздела скрываются все строки и столбцы рабочего листа за исключением тех из них, которые находятся в текущем выделенном диапазоне (рис. 1).

Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) <> » Range » Then Exit Sub
‘ Если последняя строка либо последний столбец скрыты,
‘ отобразить все и выйти
If Rows(Rows.Count).EntireRow.Hidden Or _
Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If
row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count — 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count — 1
Application.ScreenUpdating = False
On Error Resume Next
‘ Скрыть строки
Range(Cells(1, 1), Cells(row1 — 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, _
1)).EntireRow.Hidden = True
‘ Скрыть столбцы
Range(Cells(1, 1), Cells(1, col1 — 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, _
Columns.Count)).EntireColumn.Hidden = True
End Sub

Чтобы показать все строки и столбцы, встаньте на перекрестие строк и столбцов (выделено красным на рис. 1), и пройдите по меню: Главная –> Ячейки –> Формат –> Скрыть или отобразить –> Отобразить строки. Повторите этот путь, чтобы отобразить столбцы (рис. 2).

Рис. 2. Показать все строки и столбцы

Если выделенный диапазон включает несвязанные диапазоны, видимым останется только первый из них.

Синхронизация выделенного диапазона на листах рабочей книги

Если вы работаете с рабочими книгами, состоящими из нескольких листов, то, вероятно, знаете, что Excel не может «синхронизировать» листы в рабочей книге. Другими словами, не существует автоматического способа сделать так, чтобы все листы имели одинаковые выделенные диапазоны и верхние левые ячейки. Макрос VBA, показанный ниже, берет за основу активный рабочий лист и выполняет следующие действия со всеми остальными рабочими листами в книге:

  • выделяет тот же диапазон, что и в активном листе;
  • задает ту же левую верхнюю ячейку, что и на активном листе (рис. 3).

Рис. 3. Синхронизация выделенного диапазона на листах рабочей книги

VBA в Excel Объект Excel.Worksheet и программная работа с листами Excel средствами VBA

10.5 Коллекция Sheets и объект Worksheet, их свойства и методы

Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.

Читать еще:  Как сделать плюс в excel?

Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application ‘Запускаем Excel

oExcel.Visible = True ‘Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () ‘Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ‘ Создаем новый лист

oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем «Лист1» и переименовываем его в «Новый лист»:

Dim oExcel As New Excel.Application ‘Запускаем Excel

oExcel.Visible = True ‘Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() ‘Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item(«Лист 1») ‘ Находим Лист1

oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»

Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются «Sheet1», «Sheet2» и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств и методов:

  • Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
  • EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
  • EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
  • Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
  • PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл ->Параметры страницы.
  • свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
  • QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
  • Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
  • Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
  • UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
  • Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

  • методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
  • метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
  • метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
  • SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — «водяной знак», иначе на его фоне будет трудно читать текст в ячейках).
  • ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

Читать еще:  Автозамена в excel 2007 как сделать

У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.

Канал в Telegram

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

Пример 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)

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

Sub RangeUpCase()
Dim Cell As Range

For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next Cell

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

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

Sub CloseBooks()
Dim WBook As Workbook

For Each WBook In Workbooks
If WBook.Name <> ActiveWorkbook.Name Then WBook.Close False
Next WBook

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

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

11.4 Коллекция Workbooks и объект Workbook, их свойства и методы

Объект Excel.Workbook, программная работа с книгами Excel из VBA, создание и открытие книг Excel

Следующий по иерархии после Application объект в объектной модели Excel — это объект Workbook, который представляет книгу Excel. Можно сказать, что объект Workbook занимает в Excel примерно то же место, что и объект Document в Word — он нужен для получения ссылки на нужную нам книгу в наборе открытых книг Excel, а также для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:

  • первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна. Найти нужную книгу в этой коллекции можно по ее имени или номеру в коллекции:
  • второй способ — использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге:
  • третий способ — использовать свойство Application.ThisWorkbook. При этом мы обращаемся к той книге, которой принадлежит данный программный модуль:
Читать еще:  Как сделать сумму ячеек в excel через строку?

На практике чаще всего нам нужно либо создать в Excel новую книгу, либо открыть существующую книгу (или другой файл в формате, который понимает Excel, например, DBF). Для этой цели используются методы Add() и Open() соответственно. Например, создать новую книгу в Excel можно так:

Dim oWbk As Workbook

Set oWbk = Workbooks.Add()

Единственный необязательный параметр, который принимает этот метод — имя шаблона, на основе которого создается новая рабочая книга.

Открытие существующей книги выглядит так:

Dim oWbk As Workbook

Set oWbk = WorkBooks.Open(«C:mybook1.xls»)

Помимо стандартных, в коллекции Workbooks предусмотрено также три специальных метода:

  • OpenDatabase() — открыть базу данных, выполнить к ней запрос (или открыть таблицу/представление напрямую), а результаты запроса поместить как импортированные внешние данные в новую автоматически созданную рабочую книгу Excel;
  • OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл. Дополнительные параметры позволяют определять его формат.
  • OpenXML() — в качестве источника данных будет выступать файл в формате XML.

Как и метод InsertDatabase() в Word, эти методы следует использовать только в самых простых случаях. Рекомендуется по возможности использовать более мощные и стандартные средства объектной модели ADO.

Теперь о самых важных свойствах объекта Workbook — самой рабочей книги:

  • Name, CodeName, FullName — разные имена этой книги. Самое простое имя — Name, это имя совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга будет называться в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства книги в окне Properties, кодовое имя книги будет представлено в строке (Name). Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или прямо в окне Properties).

Определенное отношение к именам имеет также свойство Path (путь к файлу книги) .

  • Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions возвращают одноименные коллекции соответствующих объектов. Некоторые из этих объектов будут рассматриваться ниже.
  • ConflictResolution — как будут разрешаться конфликты изменения данных, если книга открыта несколькими пользователями сразу (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:
    • запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
    • по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
    • получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.
  • FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.
  • свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге. Получить информацию о всех именованных диапазонах в данной книге можно, например, так:

For Each Item In ThisWorkbook.Names

Это свойство удобно использовать для предварительных проверок для устранения потенциальных ошибок времени выполнения.

Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны и действуют аналогично одноименным методам объекта Document в Word.

Ссылка на основную публикацию
Adblock
detector