Excel application workbooks open

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, например, 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.

Объекты Excel

Термин Объекты Excel (понимаемый в широком смысле, как объектная модель Excel) включает в себя элементы, из которых состоит любая рабочая книга Excel. Это, например, рабочие листы (Worksheets), строки (Rows), столбцы (Columns), диапазоны ячеек (Ranges) и сама рабочая книга Excel (Workbook) в том числе. Каждый объект Excel имеет набор свойств, которые являются его неотъемлемой частью.

Например, объект Worksheet (рабочий лист) имеет свойства Name (имя), Protection (защита), Visible (видимость), Scroll Area (область прокрутки) и так далее. Таким образом, если в процессе выполнения макроса требуется скрыть рабочий лист, то достаточно изменить свойство Visible этого листа.

В Excel VBA существует особый тип объектов – коллекция. Как можно догадаться из названия, коллекция ссылается на группу (или коллекцию) объектов Excel. Например, коллекция Rows – это объект, содержащий все строки рабочего листа.

Доступ ко всем основным объектам Excel может быть осуществлён (прямо или косвенно) через объект Workbooks, который является коллекцией всех открытых в данный момент рабочих книг. Каждая рабочая книга содержит объект Sheets – коллекция, которая включает в себя все рабочие листы и листы с диаграммами рабочей книги. Каждый объект Worksheet состоит из коллекции Rows – в неё входят все строки рабочего листа, и коллекции Columns – все столбцы рабочего листа, и так далее.

В следующей таблице перечислены некоторые наиболее часто используемые объекты Excel. Полный перечень объектов Excel VBA можно найти на сайте Microsoft Office Developer (на английском).

Из объекта Workbook можно получить доступ к объекту Sheets, который является коллекцией всех листов рабочей книги (рабочие листы и диаграммы), а также к объекту Worksheets, который представляет из себя коллекцию всех рабочих листов книги Excel.

Кроме этого Вы можете использовать ActiveSheet для доступа к активному в данный момент рабочему листу. Из объекта Worksheet можно получить доступ к объектам Rows и Columns, которые являются коллекцией объектов Range, ссылающихся на строки и столбцы рабочего листа. А также можно получить доступ к отдельной ячейке или к любому диапазону смежных ячеек на рабочем листе.

Доступ к диапазону, состоящему из единственной ячейки, может быть осуществлён через объект Worksheet при помощи свойства Cells, например, Worksheet.Cells(1,1).

По-другому ссылку на диапазон можно записать, указав адреса начальной и конечной ячеек. Их можно записать через двоеточие или через запятую. Например, Worksheet.Range(“A1:B10”) или Worksheet.Range(“A1”, “B10”) или Worksheet.Range(Cells(1,1), Cells(10,2)).

Обратите внимание, если в адресе Range вторая ячейка не указана (например, Worksheet.Range(“A1”) или Worksheet.Range(Cells(1,1)), то будет выбран диапазон, состоящий из единственной ячейки.

Приведённая выше таблица показывает, как выполняется доступ к объектам Excel через родительские объекты. Например, ссылку на диапазон ячеек можно записать вот так:

Присваивание объекта переменной

В Excel VBA объект может быть присвоен переменной при помощи ключевого слова Set:

Активный объект

В любой момент времени в Excel есть активный объект Workbook – это рабочая книга, открытая в этот момент. Точно так же существует активный объект Worksheet, активный объект Range и так далее.

Сослаться на активный объект Workbook или Sheet в коде VBA можно как на ActiveWorkbook или ActiveSheet, а на активный объект Range – как на Selection.

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

Таким образом, чтобы сослаться на диапазон A1:B10 на активном рабочем листе активной книги, можно записать просто:

Смена активного объекта

Если в процессе выполнения программы требуется сделать активной другую рабочую книгу, другой рабочий лист, диапазон и так далее, то для этого нужно использовать методы Activate или Select вот таким образом:

Методы объектов, в том числе использованные только что методы Activate или Select, далее будут рассмотрены более подробно.

Свойства объектов

Каждый объект VBA имеет заданные для него свойства. Например, объект Workbook имеет свойства Name (имя), RevisionNumber (количество сохранений), Sheets (листы) и множество других. Чтобы получить доступ к свойствам объекта, нужно записать имя объекта, затем точку и далее имя свойства. Например, имя активной рабочей книги может быть доступно вот так: ActiveWorkbook.Name. Таким образом, чтобы присвоить переменной wbName имя активной рабочей книги, можно использовать вот такой код:

Ранее мы показали, как объект Workbook может быть использован для доступа к объекту Worksheet при помощи такой команды:

Это возможно потому, что коллекция Worksheets является свойством объекта Workbook.

Некоторые свойства объекта доступны только для чтения, то есть их значения пользователь изменять не может. В то же время существуют свойства, которым можно присваивать различные значения. Например, чтобы изменить название активного листа на “Мой рабочий лист“, достаточно присвоить это имя свойству Name активного листа, вот так:

Методы объектов

Объекты VBA имеют методы для выполнения определённых действий. Методы объекта – это процедуры, привязанные к объектам определённого типа. Например, объект Workbook имеет методы Activate, Close, Save и ещё множество других.

Для того, чтобы вызвать метод объекта, нужно записать имя объекта, точку и имя метода. Например, чтобы сохранить активную рабочую книгу, можно использовать вот такую строку кода:

Как и другие процедуры, методы могут иметь аргументы, которые передаются методу при его вызове. Например, метод Close объекта Workbook имеет три необязательных аргумента, которые определяют, должна ли быть сохранена рабочая книга перед закрытием и тому подобное.

Чтобы передать методу аргументы, необходимо записать после вызова метода значения этих аргументов через запятую. Например, если нужно сохранить активную рабочую книгу как файл .csv с именем “Книга2”, то нужно вызвать метод SaveAs объекта Workbook и передать аргументу Filename значение Книга2, а аргументу FileFormat – значение xlCSV:

Чтобы сделать код более читаемым, при вызове метода можно использовать именованные аргументы. В этом случае сначала записывают имя аргумента, затем оператор присваивания “:=” и после него указывают значение. Таким образом, приведённый выше пример вызова метода SaveAs объекта Workbook можно записать по-другому:

В окне Object Browser редактора Visual Basic показан список всех доступных объектов, их свойств и методов. Чтобы открыть этот список, запустите редактор Visual Basic и нажмите F2.

Рассмотрим несколько примеров

Пример 1

Этот отрывок кода VBA может служить иллюстрацией использования цикла For Each. В данном случае мы обратимся к нему, чтобы продемонстрировать ссылки на объект Worksheets (который по умолчанию берётся из активной рабочей книги) и ссылки на каждый объект Worksheet отдельно. Обратите внимание, что для вывода на экран имени каждого рабочего листа использовано свойство Name объекта Worksheet.

Пример 2

В этом примере кода VBA показано, как можно получать доступ к рабочим листам и диапазонам ячеек из других рабочих книг. Кроме этого, Вы убедитесь, что если не указана ссылка на какой-то определённый объект, то по умолчанию используются активные объекты Excel. Данный пример демонстрирует использование ключевого слова Set для присваивания объекта переменной.

В коде, приведённом ниже, для объекта Range вызывается метод PasteSpecial. Этот метод передаёт аргументу Paste значение xlPasteValues.

Пример 3

Следующий отрывок кода VBA показывает пример объекта (коллекции) Columns и демонстрирует, как доступ к нему осуществляется из объекта Worksheet. Кроме этого, Вы увидите, что, ссылаясь на ячейку или диапазон ячеек на активном рабочем листе, можно не указывать этот лист в ссылке. Вновь встречаем ключевое слово Set, при помощи которого объект Range присваивается переменной Col.

Данный код VBA показывает также пример доступа к свойству Value объекта Range и изменение его значения.

VBA Workbook Open

Excel VBA Workbooks.Open Method

VBA Workbooks.Open Method is used to open an excel workbook from another workbook.

As far as VBA is concerned I hope you have seen the magic and wonders VBA can do at your workplace. One of the substantial thing about VBA is it doesn’t limit its work in the same workbook rather we can access the workbook which is already not opened. One of such a technique is we can open another workbook from a different workbook. In this special article, we will show you how to open excel workbook using Workbooks.Open method in detail.

Open a Workbook from Current Workbook

Just imagine a situation where you are working with two workbooks which are already opened. The workbook that you are working is named as “Book 1” and another workbook which is open but not active is named as “Book 2”.

Since we are working in “Book 1” in order to activate another workbook “Book 2” we need to specify the workbook name with its file extension by using WORKBOOKS object.

Inside this, we need to enter the workbook name and its extension.

Then we need to insist on what we need to do with this workbook. Since we need to activate this workbook select “Activate” method.

So, this code will activate the workbook “Book 2.xlsx” if it is opened.

What if the workbook “Book 2.xlsx” is not opened. How do you open it or activate it.

This is where our Workbooks.Open method comes into the picture.

Syntax

• File Name: The first argument in this method is we need to specify the workbook name that we are trying to open. Workbook name alone cannot do the job here, because excel doesn’t know in which folder your workbook is saved. So we need to supply full folder path followed by an exact file name with its saved extension.
• Update Links: When we try to open the workbook it may have contained some external links from other workbooks. We can supply yes or no here.
• Read Mode: How do you want to open the workbook?? If you want the file only to be read then TRUE is the argument.
• Password: If the targeted or opening workbook contains any password then in this argument we need to specify the password that was used while protecting the workbook.

These four arguments are good enough in this method. Do not break your head to understand each and every argument because the need for those remaining arguments may never arise.

VBA-Урок 11.1. События рабочей книги (Workbook Events)

Мы можем иметь события рабочей книги (например, открытие, закрытие и т.д.), которые могут быть триггерами (переключателями) для VBA кода.

Workbook_Open (Открытие книги)

Чтобы выполнить инструкции, когда открывается рабочая книга, идем к ThisWorkbook и выбираем Workbook :

Событие Workbook_Open будет добавлено по умолчанию и будет действовать во время открытия книги:

Например, если мы добавим следующую инструкцию, тогда диалоговое окно будет отображено при открытии книги:

Workbook_BeforeClose (Событие перед закрытием книги)

Чтобы выполнить инструкции перед самым закрытием книги, выберите BeforeClose

Закрытие книги может быть отменено, если присвоить значение True к переменной «Cancel».

Ниже приведен пример, в котором пользователя спрашивают подтверждение на закрытие рабочей книги:

Workbook_BeforeSave (Событие перед сохранением книги)

Это событие возникает сразу перед самым сохранением рабочей книги:

Сохранение файла может быть отменено присвоением значения True к переменной «Cancel».

Workbook_BeforePrint (Событие перед печатью книги)

Это событие возникает перед самой печатью рабочей книги:

Печать файла может быть отменена присвоением значения True к переменной «Cancel».

Workbook_AfterSave (Событие после сохранения книги)

Это событие возникает сразу после сохранением рабочей книги:

Workbook_SheetActivate (Событие переключения рабочего листа)

Это событие возникает каждый раз, когда вы переключаетесь между листами рабочей книги:

В этом примере, название активированного листа отображается в диалоговом окне

Workbook_SheetBeforeDoubleClick (Событие двойного щелчка по ячейке)

Это событие возникает при двойном щелчке на ячейке рабочего листа

Например, мы можем использовать это событие, чтобы добавить цвет заливки ячейки, в зависимости от выбранного листа:

Workbook_SheetBeforeRightClick (Событие перед правым кликом)

Это событие возникает перед самым кликом правой кнопки мыши

Workbook_SheetChange (Событие изменения содержания листа)

Это событие возникает каждый раз, когда меняется содержание рабочего листа

Workbook_SheetCalculate (Событие пересчете листа)

Это событие возникает каждый раз, когда рассчитываются или пересчитываются данные на рабочем листе:

Workbook_SheetSelectionChange (Событие изменения выбранного диапазона ячеек)

Это событие возникает каждый раз, когда меняется содержание выбранного диапазона ячеек на расчетном листе

В этом примере, изменяется цвет заливки, если ячейка А1 пуста

Workbook_NewSheet (Событие добавления нового листа)

Это событие возникает каждый раз, когда добавляется новый лист в рабочую книгу:

Workbook_SheetFollowHyperlink (Событие нажатия на ссылку)

Это событие возникает при нажатии на ссылку (гипертекст):

Excel application workbooks open

Вопрос

I’ve this Workbook, which I want to update.

All I want is — powershell to update this already opened workbook.

So I am looking for option to activate sheet on an already open Excel workbook.

$xl=New-Object -ComObject «Excel.Application»
$wb=$xl.Workbooks.open(«C:TEMPExcel.xls»)
$xlWorkSheet=$wb.ActiveSheet
$cells = $xlWorkSheet.Cells
$cells=$ws.Cells
$cells.item(1,1)=»Server NAme»

I had this code to be used. But this is opening a new file. And if I try to open the same file, it opens in readonly mode. I waish to APPEND already open workbook.

Ответы

That’s weird, I would swear I tried $xl.workbooks and received only one object, but you’re right, I get two. And I forgot about the com instance I created. So using 

should attach to the target excel spreadsheet.

I hope this post has helped!

• Помечено в качестве ответа Itsvivekg 29 мая 2014 г. 7:40

Все ответы

Thanks for reply.

But wouldnt it pick all the active sessions? I will be running this script on a jump server, where we have multiple users logged in and using EXCEL. Further, how to activate that particular excel Workbook.

Basically I am looking foe PS equivalent of VBA —

The environments are not reproducible.

I hope this post has helped!

Excel has only one instances

with multiple documents.

I don’t think that’s entirely accurate. I have three documents open:

I hope this post has helped!

• Изменено Rhys W Edwards 23 мая 2014 г. 18:25

I hope this post has helped!

Excel runs single instance by default except if you launch it using COM. It will then opena new instance with each COM instantiation. This is and has been the Excel behavior since the beginning.

If you open two excel documents directoly you will be able to get both in the Excel instance. I opened two separate workbooks and both are available.

PS C:scripts> $xl=[Runtime.InteropServices.Marshal]::GetActiveObject(«Excel.Application»)
PS C:scripts> $xl.Workbooks.count
2

There can be only one Active document at a time but both documents are available.

PS C:scripts> $xl.Workbooks.Item(1).Name
exjunk.csv
PS C:scripts> $xl.Workbooks.Item(2).Name
test.xlsx
PS C:scripts> $xl.ActiveWorkbook.Name
test.xlsx
PS C:scripts> $xl.Workbooks.Item(2).Activate()
PS C:scripts> $xl.ActiveWorkbook.Name
test.xlsx
PS C:scripts> $xl.Workbooks.Item(1).Activate
PS C:scripts> $xl.ActiveWorkbook.Name
exjunk.csv

We use this quite often with no issues.

I clicked on the Excel icon on the start menu and it opened Excel with a blank «Book1» and title of «Book1 — Excel»

PS C:scripts> $xl.Workbooks.count
3
PS C:scripts> $xl.ActiveWorkbook.Name
Book1

Notice the count is now three and the new active workbook is the newest one opened.

Now I selected another WB by clicking on it on the task bar. Here is what PowerShell reports.

PS C:scripts> $xl.ActiveWorkbook.Name
exjunk.csv

I know non-programmers have a bit of an issue handling how this works. It is by design and is critical to how Office products are able to work together and communicate seamlessly so well. It is what made MS Office the defacto Office Suite. All efforts to unseat it have failed.

It is also possible to disable this behavior in Excel.