Как проверить открыта ли книга?
Собственно суть темы отражена в названии. Как при выполнении кода из VBA узнать перед обращением к книге открыта она или нет? Ведь если книга закрыта, то обращение к ней вызовет ошибку, а если открывать без проверки - то это может повлечь за собой утерю данных, если предварительно эта книга не была сохранена. Ни один ни второй вариант, естественно, не устраивают. Я покажу два способа проверки через функции. Если функция вернет True - книга открыта, если False - закрыта. Для проверки функций используем проверочную процедуру Check_Open_Book:
Sub Check_Open_Book() If IsBookOpen("Книга1.xls") Then MsgBox "Книга открыта", vbInformation, "Сообщение" Else MsgBox "Книга закрыта", vbInformation, "Сообщение" 'открываем книгу Workbooks.Open "C:\Книга1.xls" End If End Sub |
Данная процедура вызывает функцию IsBookOpen, передавая ей в качестве параметра имя книги, "открытость" которой мы хотим проверить. Я приведу несколько вариантов самой функции IsBookOpen. Во всех вариантах действует один и тот же принцип: код любого из вариантов функции IsBookOpen необходимо скопировать и вставить в стандартный модуль. Модуль должен быть внутри той книги, в кодах которой планируется проверять открыта ли книга. Только тогда IsBookOpen будет доступна для вызова из любого кода этой же книги.
Если вдруг в момент выполнения на строке
Function IsBookOpen(wbName As String) As Boolean Dim wbBook As Workbook For Each wbBook In Workbooks If wbBook.Name <> ThisWorkbook.Name Then If Windows(wbBook.Name).Visible Then If wbBook.Name = wbName Then IsBookOpen = True: Exit For End If End If Next wbBook End Function |
Функция просматривает все открытые книги и если находит среди них книгу с указанным именем, то функция возвращает True. Есть небольшая особенность - функция исключает скрытые книги(это либо надстройки, либо PERSONAL.XLS). Так же из просмотра исключена та книга, в которой расположен сам код. Если Вам нужно проверить наличие книги независимо от её видимости, то необходимо просто заменить блок
If Windows(wbBook.Name).Visible Then If wbBook.Name = wbName Then IsBookOpen = True: Exit For End If |
на одну строку(просто убрать лишнее условие проверки)
If wbBook.Name = wbName Then IsBookOpen = True: Exit For |
Либо можно использовать
Function IsBookOpen(wbName As String) As Boolean Dim wbBook As Workbook: On Error Resume Next Set wbBook = Workbooks(wbName) IsBookOpen = Not wbBook Is Nothing End Function |
Данный способ обращается к любой открытой книге, даже если она скрыта как PERSONAL.XLS или надстройка. Однако у данной функции есть недостаток - используется оператор On Error и если в настройках VBA(Tools -Options -вкладка General) установлено Break on All Errors - то этот код не сработает, если книга не открыта - получим ошибку. В то время как Вариант1 с циклом по всем открытым книгам сработает без ошибок.
По просьбам читателей решил добавить код, который проверяет открыта ли книга независимо от её месторасположения и используемого приложения Excel. Книга может быть открыта другим пользователем (если книга на сервере), в другом экземпляре Excel или в этом же экземпляре Excel.
Function IsBookOpen(wbFullName As String) As Boolean Dim iFF As Integer, retval As Boolean iFF = FreeFile On Error Resume Next Open wbFullName For Random Access Read Write Lock Read Write As #iFF retval = (Err.Number <> 0) Close #iFF IsBookOpen = retval End Function |
Функция несколько отличается от приведенных выше - передается в неё не только имя книги, а полный путь к книге, включая имя и расширение:
Sub Test() MsgBox "Файл 'Книга1'" & IIf(IsBookOpen("C:\Книга1.xls"), " уже открыт", " не занят") End Sub |
Или более близкий к жизненной ситуации вариант: надо открыть книгу, внести в книгу изменения, сохранить и закрыть. Если книга кем-то уже открыта - получим ошибку на этапе сохранения или запрос на этапе открытия. Поэтому сначала проверяем доступность книги и если она доступна - вносим изменения и сохраняем.
Sub Test() Dim sWBFullName As String Dim wb As Workbook 'полный путь к проверяемой книге sWBFullName = "C:\Documents\Книга1.xls" 'если книга кем-то открыта - пропускаем обработку этой книги 'книга закрыта - вносим изменения, сохраняем, закрываем If IsBookOpen(sWBFullName) = False Then Set wb = Application.Workbooks.Open(sWBFullName) 'изменяем значение ячейки "A1" на первом листе книги wb.Sheets(1).Range("A1").Value = "www.excel-vba.ru" ws.Close True End If End Sub |
При использовании функции
Также см.:
Как получить данные из закрытой книги?
Как узнать существует ли лист в книге?
Как узнать существует ли модуль в книге
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
А как проверить открыта ли книга, если книга находится на сервере и при этом открыта не мной?
поддерживаю вопрос! Даже если книга открыта вторым Экселем на моем же ПК, то проверить открыта ли она нет возможности .....
Alexei, Anatoly - добавил код, который проверяет независимо ни от чего.
При открытом Workbooks.Open FileName:="C:\Книга1.xls", ReadOnly:=True - в первом экземпляре Excel и запуска 3-его варината Function IsBookOpen - из второго экземпляра Excel, Function IsBookOpen возвращает False ибо файл "C:\Книга1.xls" открыт в режиме ReadOnly.
Аналогичный результат при работе в одном эксземпляре Excelя.
Полагаю надо добавить комент к третьему варианту с его ограничением и переобозвать функцию CheckBookWriteOpen
san-andrew, сначала прочитайте начало статьи. Цель - перед открытием узнать, если ли право вносить изменения или открыть не испортив данные имеющиеся. И Ваш комментарий никак этого не отрицает, а подтверждает. Если книга открыта в режиме чтения - то её можно открыть в обычном режиме и вносить в неё изменения и никаких конфликтов не возникнет. Так что добавлять какие-то комментарии не имеет смысла, т.к. по факту в режиме чтения открыта не книга, а её временный экземпляр, который не может внести изменения в сам исходный файл.
Добрый день, Дмитрий!
Спасибо за код. Отлично работает.
Не могли бы Вы подсказать как по известному из Вашего кода пути обратиться к книге и сделать ее активной, если она открыта во втором экземпляре Excel?
Александр, почитайте тему с форума:http://www.excel-vba.ru/forum/index.php?topic=2576.0
Спасибо.
Возможно я слоупок, что пишу только сейчас, но 3-й вариант в 2010 Excel выдаёт True, если книга на сетевой шаре открыта другим пользователем.
George, а какой должен быть по-Вашему результат? Ведь даже аннотация к третему варианту такая:
"По просьбам читателей решил добавить код, который проверяет открыта ли книга независимо от её месторасположения и используемого приложения Excel. Книга может быть открыта другим пользователем (если книга на сервере), в другом экземпляре Excel или в этом же экземпляре Excel."
т.е. изначально этот вариант определяем открыта ли книга вообще кем-либо где-либо.
Вот это скорость ответа! :)
В моём случае, нужно было проверить открыта ли сетевая книга на локальном компьютере. Соответственно, результат функции меня и удивил)) По моей логике, при открытии книги "для чтения", нам не важно открыл ли её кто-то другой или нет. Но даже если посмотреть со стороны открытия сетевого файла для записи, функция всё равно не даст чёткого ответа, на локальном компьютере открыта книга или кем-то другим, поэтому мне и не понятно, для чего возвращать True, если потом всё равно не будет уверенности открывать книгу или просто делать активной.
Свою проблему решил другим решением, а именно проверкой по полному пути (может кому пригодится):
Function isWorkbookOpenByFullPath(FullPath As String) As Boolean
Dim WB As Workbook
isWorkbookOpenByFullPath = False
For Each WB In Application.Workbooks
If WB.FullName = FullPath Then
isWorkbookOpenByFullPath = True
Exit For
End If
Next
End Function
А существует ли возможность запихнуть функцию IsBookOpen в личную книгу макросов или надстройку? Дело в том, что функция полезная, используется часто и копировать её из книги в книгу как-то не рационально. Хочется хранить её в одном месте, а из разных книг только вызывать.
Денис, не вижу причин, которые бы мешали. Запишите код в надстройку, из других книг вызывайте через метод Run:
Application.Run "'имя надстройки.xla'!IsBookOpen", Имя книги на проверку