VBA в Excel Объект Excel.Workbook и программная работа с книгами Excel из VBA
Следующий по иерархии после 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.
For Each Item In ThisWorkbook.Names
Это свойство удобно использовать для предварительных проверок для устранения потенциальных ошибок времени выполнения.
Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны и действуют аналогично одноименным методам объекта Document в Word.
Свойства и методы ActiveWorkbook
Эта статья содержит полное руководство по использованию рабочей книги VBA.
Если вы хотите использовать VBA для открытия рабочей книги, тогда откройте «Открыть рабочую книгу»
Если вы хотите использовать VBA для создания новой рабочей книги, перейдите к разделу «Создание новой рабочей книги».
Для всех других задач VBA Workbook, ознакомьтесь с кратким руководством ниже.
Краткое руководство по книге VBA
В следующей таблице приведено краткое руководство по основным задачам книги VBA.
Задача Исполнение Доступ к открытой книге с
использованием имениWorkbooks(«Пример.xlsx») Доступ к открытой рабочей
книге (открывшейся первой)Workbooks(1) Доступ к открытой рабочей
книге (открывшейся последней)Workbooks(Workbooks.Count) Доступ к активной книге ActiveWorkbook Доступ к книге, содержащей
код VBAThisWorkbook Объявите переменную книги Dim wk As Workbook Назначьте переменную книги Set wk = Workbooks(«Пример.xlsx»)
Set wk = ThisWorkbook
Set wk = Workbooks(1)Активировать книгу wk.Activate Закрыть книгу без сохранения wk.Close SaveChanges:=False Закройте книгу и сохраните wk.Close SaveChanges:=True Создать новую рабочую книгу Set wk = Workbooks.Add Открыть рабочую книгу Set wk =Workbooks.Open («C:\Документы\Пример.xlsx») Открыть книгу только для
чтенияSet wk = Workbooks.Open («C:\Документы\Пример.xlsx», ReadOnly:=True) Проверьте существование книги If Dir(«C:\Документы\Книга1.xlsx») = «» Then
MsgBox «File does not exist.»
EndIfПроверьте открыта ли книга Смотрите раздел «Проверить
открыта ли книга»Перечислите все открытые
рабочие книгиFor Each wk In Application.Workbooks
Debug.Print wk.FullName
Next wkОткрыть книгу с помощью
диалогового окна «Файл»Смотрите раздел «Использование диалогового окна «Файл» Сохранить книгу wk.Save Сохранить копию книги wk.SaveCopyAs «C:\Копия.xlsm» Скопируйте книгу, если она
закрытаFileCopy «C:\file1.xlsx»,»C:\Копия.xlsx» Сохранить как Рабочая книга wk.SaveAs «Резервная копия.xlsx» Начало работы с книгой VBA
Мы можем получить доступ к любой открытой книге, используя код Workbooks («Пример.xlsm»). Просто замените Пример.xlsm именем книги, которую вы хотите использовать.
В следующем примере показано, как записать в ячейку на листе. Вы заметите, что мы должны были указать рабочую книгу, рабочий лист и диапазон ячеек.
Этот пример может показаться немного запутанным для новичка, но на самом деле он довольно прост.
Первая часть до десятичной запятой представляет собой рабочую книгу, вторая часть представляет собой рабочую таблицу, а третья — диапазон. Вот еще несколько примеров записи в ячейку
Это простой шаблон. Вы можете написать в любую ячейку любого листа из любой книги. Это просто вопрос изменения названия книги, имени листа и диапазона в соответствии с вашими потребностями.
Взгляните на часть книги
Ключевое слово Workbooks относится к совокупности всех открытых рабочих книг. Предоставление имени книги в коллекцию дает нам доступ к этой книге. Когда у нас есть объект, мы можем использовать его для выполнения задач с книгой.
Устранение неполадок в коллекции книг
Когда вы используете коллекцию Workbooks для доступа к книге, вы можете получить сообщение об ошибке:
Run-time Error 9: Subscript out of Range.
Это означает, что VBA не может найти книгу, которую вы передали в качестве параметра.
Это может произойти по следующим причинам:
- Рабочая книга в настоящее время закрыта.
- Вы написали имя неправильно.
- Вы создали новую рабочую книгу (например, «Книга1») и попытались получить к ней доступ, используя Workbooks («Книга1.xlsx»). Это имя не Книга1.xlsx, пока оно не будет сохранено в первый раз.
- (Только для Excel 2007/2010) Если вы используете два экземпляра Excel, то Workbooks () относится только к рабочим книгам, открытым в текущем экземпляре Excel.
- Вы передали число в качестве индекса, и оно больше, чем количество открытых книг, например Вы использовали
Workbooks (3), и только две рабочие книги открыты.
Если вы не можете устранить ошибку, воспользуйтесь любой из функций в разделе Поиск всех открытых рабочих книг. Они будут печатать имена всех открытых рабочих книг в «Immediate Window » (Ctrl + G).
Примеры использования рабочей книги VBA
Следующие примеры показывают, что вы можете сделать с книгой.
Примечание. Чтобы попробовать этот пример, создайте две открытые книги с именами Тест1.xlsx и Тест2.xlsx.
Примечание: в примерах кода я часто использую Debug.Print. Эта функция печатает значения в Immediate Window. Для просмотра этого окна выберите View-> Immediate Window из меню (сочетание клавиш Ctrl + G)
Доступ к рабочей книге VBA по индексу
Вы также можете использовать индексный номер с книгами (). Индекс относится к порядку, в котором книга была открыта или создана.
Workbooks (1) относится к книге, которая была открыта первой. Workbooks (2) относится к рабочей книге, которая была открыта второй и так далее.
В этом примере мы использовали Workbooks.Count. Это количество рабочих книг, которые в настоящее время находятся в коллекции рабочих книг. То есть количество рабочих книг, открытых на данный момент. Таким образом, использование его в качестве индекса дает нам последнюю книгу, которая была открыта
Использование индекса не очень полезно, если вам не нужно знать порядок. По этой причине вам следует избегать его использования. Вместо этого вы должны использовать имя рабочей книги вместе с Workbooks ().
Поиск всех открытых рабочих книг
Иногда вы можете получить доступ ко всем рабочим книгам, которые открыты. Другими словами, все элементы в коллекции Workbooks ().
Вы можете сделать это, используя цикл For Each.
Вы также можете использовать стандартный цикл For для доступа ко всем открытым рабочим книгам.
Для доступа к книгам подходит любой из этих циклов. Стандартный цикл For полезен, если вы хотите использовать другой порядок или вам нужно использовать счетчик.
Примечание. Оба примера читаются в порядке с первого открытого до последнего открытого. Если вы хотите читать в обратном порядке (с последнего на первое), вы можете сделать это
Открыть рабочую книгу
До сих пор мы имели дело с рабочими книгами, которые уже открыты. Конечно, необходимость вручную открывать рабочую книгу перед запуском макроса не позволяет автоматизировать задачи. Задание «Открыть рабочую книгу» должно выполняться VBA.
Следующий код VBA открывает книгу «Книга1.xlsm» в папке «C: \ Документы»
Рекомендуется проверить, действительно ли существует книга, прежде чем открывать ее. Это предотвратит ваши ошибки. Функция Dir позволяет вам легко это сделать.
Проверить открыта ли книга
Если вы открываете книгу только для чтения, то не имеет значения, открыта ли она уже. Тем не менее, если вы собираетесь обновить данные в рабочей книге, рекомендуется проверить, открыты ли они.
Приведенную ниже функцию можно использовать для проверки, открыта ли книга в данный момент. Если нет, то откроется рабочая книга. В любом случае вы получите открытую рабочую книгу.
Вы можете использовать эту функцию так:
Этот код хорош в большинстве ситуаций. Однако, если рабочая книга может быть открыта в режиме только для чтения или может быть открыта в данный момент другим пользователем, возможно, вы захотите использовать немного другой подход.
Простой способ справиться с этим в этом сценарии состоит в том, чтобы настаивать на том, что файл должен быть закрыт для успешного запуска приложения. Вы можете использовать функцию ниже, чтобы просто проверить, открыт ли уже файл, и если это так, сообщить пользователю, что он должен быть закрыт в первую очередь.
Пример использования этой функции показан ниже. В этом случае, если рабочая книга уже открыта, вы сообщаете пользователю, что она должна быть закрыта для продолжения макроса.
Если вам нужно проверить, открыта ли книга в другом экземпляре Excel, вы можете использовать атрибут ReadOnly книги. Будет установлено значение true, если оно открыто в другом экземпляре.
Закрыть книгу
Закрыть книгу в Excel VBA очень просто. Вы просто вызываете метод Close рабочей книги.
Обычно, когда вы закрываете книгу в VBA, вы не хотите видеть сообщения из Excel, спрашивающие, хотите ли вы сохранить файл.
Вы можете указать, сохранять ли книгу или нет, и тогда сообщения Excel не будут появляться.
Очевидно, что вы не можете сохранить изменения в книге, которая в данный момент открыта только для чтения.
Сохранить книгу
Мы только что видели, что вы можете сохранить книгу, когда закроете ее. Если вы хотите сохранить его на любом другом этапе, вы можете просто использовать метод Save.
Вы также можете использовать метод SaveAs
Метод WorkAs SaveAs поставляется с двенадцатью параметрами, которые позволяют вам добавить пароль, установить файл только для чтения и так далее. Вы можете увидеть детали здесь.
Вы также можете использовать VBA для сохранения книги в виде копии с помощью SaveCopyAs.
Копировать книгу
Если рабочая книга открыта, вы можете использовать два метода в приведенном выше разделе для создания копии, т.е. SaveAs и SaveCopyAs.
Если вы хотите скопировать книгу, не открывая ее, вы можете использовать FileCopy, как показано в следующем примере:
Использование диалогового окна «Файл» для открытия рабочей книги
В предыдущем разделе показано, как открыть книгу с заданным именем. Иногда вам может понадобиться, чтобы пользователь выбрал рабочую книгу. Вы можете легко использовать Windows File Dialog.
FileDialog настраивается, и вы можете использовать его так:
- Выберите файл.
- Выберите папку.
- Откройте файл.
- «Сохранить как» файл.
Если вы просто хотите, чтобы пользователь выбрал файл, вы можете использовать функцию GetOpenFilename.
Следующая функция открывает книгу с помощью диалога файлов. Функция возвращает полное имя файла, если файл был выбран. Если пользователь отменяет, он отображает сообщение и возвращает пустую строку.
Когда вы вызываете эту функцию, вы должны проверить, отменяет ли пользователь диалог.
В следующем примере показано, как легко вызвать функцию UserSelectWorkbook и обработать случай отмены пользователя.
Вы можете настроить диалог, изменив Title, Filters и AllowMultiSelect в функции UserSelectWorkbook.
Использование ThisWorkbook
Существует более простой способ доступа к текущей книге, чем использование Workbooks() . Вы можете использовать ключевое слово ThisWorkbook. Это относится к текущей книге, то есть к книге, содержащей код VBA.
Если наш код находится в книге, называемой МойVBA.xlsm, то ThisWorkbook и Workbooks («МойVBA.xlsm») ссылаются на одну и ту же книгу.
Использование ThisWorkbook более полезно, чем использование Workbooks (). С ThisWorkbook нам не нужно беспокоиться об имени файла. Это дает нам два преимущества:
- Изменение имени файла не повлияет на код
- Копирование кода в другую рабочую книгу не требует изменения кода
Это может показаться очень маленьким преимуществом. Реальность такова, что имена будут меняться все время. Использование ThisWorkbook означает, что ваш код будет работать нормально.
В следующем примере показаны две строки кода. Один с помощью ThisWorkbook, другой с помощью Workbooks (). Тот, который использует Workbooks, больше не будет работать, если имя МойVBA.xlsm изменится.
Использование ActiveWorkbook
ActiveWorkbook относится к книге, которая в данный момент активна. Это тот, который пользователь последний раз щелкнул.
Поначалу это может показаться полезным. Проблема в том, что любая рабочая книга может стать активной простым щелчком мыши. Это означает, что вы можете легко записать данные не в ту книгу.
Использование ActiveWorkbook также затрудняет чтение кода. Из кода может быть не очевидно, какая книга должна быть активной.
Надеюсь, я дал понять, что вам следует избегать использования ActiveWorkbook, если в этом нет необходимости. Если вы должны быть очень осторожны.
Примеры доступа к книге
Мы рассмотрели все способы доступа к книге. Следующий код показывает примеры этих способов.
Объявление переменной VBA Workbook
Причина объявления переменной книги состоит в том, чтобы сделать ваш код более легким для чтения и понимания. Проще увидеть преимущество на примере:
Вы можете установить переменную книги с помощью любого из методов доступа, которые мы видели.
Ниже показан тот же код без переменной рабочей книги.
В этих примерах разница несущественная. Однако, когда у вас много кода, использование переменной полезно, в частности, для рабочего листа и диапазонов, где имена имеют тенденцию быть длинными, например thisWorkbook.Worksheets («Лист1»). Range («A1»).
Вы можете назвать переменную книги как wrkRead или wrkWrite. Затем вы можете сразу увидеть, для чего используется эта книга.
Создать новую книгу
Для создания новой рабочей книги вы используете функцию добавления рабочих книг. Эта функция создает новую пустую книгу. Это то же самое, что выбрать «Новая книга» в меню «Файл Excel».
Когда вы создаете новую книгу, вы, как правило, хотите сохранить ее. Следующий код показывает вам, как это сделать.
Когда вы создаете новую книгу, она обычно содержит три листа. Это определяется свойством Application.SheetsInNewWorkbook.
Если вы хотите, чтобы в новой книге было другое количество листов, измените это свойство перед созданием новой книги. В следующем примере показано, как создать новую книгу с семью листами.
With и Workbook
Ключевое слово With облегчает чтение и написание кода VBA. Использование с означает, что вам нужно упомянуть только один раз. С используется с объектами. Это такие элементы, как рабочие книги, рабочие таблицы и диапазоны.
В следующем примере есть два Subs. Первый похож на код, который мы видели до сих пор. Второй использует ключевое слово With. Вы можете увидеть код гораздо понятнее во втором Sub. Ключевые слова End With обозначают конец кода раздела с помощью With.
Резюме
Ниже приводится краткое изложение основных моментов этой статьи.
- Чтобы получить рабочую книгу, содержащую код, используйте ThisWorkbook.
- Чтобы получить любую открытую книгу, используйте Workbooks («Пример.xlsx»).
- Чтобы открыть книгу, используйте Set Wrk = Workbooks.Open («C: \ Папка\ Пример.xlsx»).
- Разрешить пользователю выбирать файл с помощью функции UserSelectWorkbook, представленной выше.
- Чтобы создать копию открытой книги, используйте свойство SaveAs с именем файла.
- Чтобы создать копию рабочей книги без открытия, используйте функцию FileCopy.
- Чтобы ваш код было легче читать и писать, используйте ключевое слово With.
- Другой способ прояснить ваш код — использовать переменные Workbook.
- Чтобы просмотреть все открытые рабочие книги, используйте For Every wk в Workbooks, где wk — это переменная рабочей книги.
- Старайтесь избегать использования ActiveWorkbook и Workbooks (Index), поскольку их ссылка на рабочую книгу носит временный характер.
Вы можете увидеть краткое руководство по теме в верхней части этой статьи
Заключение
Это был подробная статья об очень важном элементе VBA — Рабочей книги. Я надеюсь, что вы нашли ее полезной. Excel отлично справляется со многими способами выполнения подобных действий, но недостатком является то, что иногда он может привести к путанице.
Чтобы получить максимальную пользу от этой статьи, я рекомендую вам попробовать примеры. Создайте несколько книг и поиграйтесь с кодом. Внесите изменения в код и посмотрите, как эти изменения влияют на результат. Практика — лучший способ выучить VBA.
VBA Excel. Рабочая книга (открыть, создать новую, закрыть)
Существующая книга открывается из кода VBA Excel с помощью метода Open:
В кавычках указывается полный путь к открываемому файлу Excel. Если такой файл не существует, произойдет ошибка.
Проверка существования файла
Проверить существование файла можно с помощью функции Dir. Проверка существования книги Excel:
Или, если файл (книга Excel) существует, можно сразу его открыть:
Создание новой книги
Новая рабочая книга Excel создается в VBA с помощью метода Add:
Созданную книгу, если она не будет использоваться как временная, лучше сразу сохранить:
В кавычках указывается полный путь сохраняемого файла Excel, включая присваиваемое имя, в примере — это «test2.xls».
Обращение к открытой книге
Обращение к активной книге:
Обращение к книге с выполняемым кодом:
Обращение к книге по имени:
Обратиться по имени можно только к уже открытой книге, а чтобы из кода VBA Excel книгу открыть, необходимо указать полный путь к файлу.
Как закрыть книгу Excel из кода VBA
Открытая рабочая книга закрывается из кода VBA Excel с помощью метода Close:
Если закрываемая книга редактировалась, а внесенные изменения не были сохранены, тогда при ее закрытии Excel отобразит диалоговое окно с вопросом: Вы хотите сохранить изменения в файле test1.xlsx? Чтобы файл был закрыт без сохранения изменений и вывода диалогового окна, можно воспользоваться параметром метода Close — SaveChanges:
Закрыть книгу Excel из кода VBA с сохранением внесенных изменений можно также с помощью параметра SaveChanges:
Фразы для контекстного поиска: открыть книгу, открытие книги, создать книгу, создание книги, закрыть книгу, закрытие книги, открыть файл Excel, открытие файла Excel, существование книги, обратиться к открытой книге.
37 комментариев для “VBA Excel. Рабочая книга (открыть, создать новую, закрыть)”
А что делать если открываемая книга спрашивает, стоит ли ей обновляться и выполнение кода приостанавливается, пока пользователь не нажмёт кнопку? Как скрывать это сообщение? Пытался писать
Если речь идет об обновлении связей между книгами, то попробуйте так:
т.к. в других местах обновление, возможно, нужно.
Жаль, спасибо.
Кстати, если вдруг кому-то нужно, изменить название excel в шапке документа (но при сохранении всё равно будет предлагаться «Книга n»), то можно сделать так:Здравствуйте.
А как поступить, если имя книги заранее неизвестно. Есть-ли в VBA что-нибудь вроде диалогового окна «Открыть книгу».
Или, допустим, поиск всех книг в определённой папке.Здравствуйте, Вячеслав!
Для выбора книги в определённой папке используйте Стандартный диалог выбора файлов Application.GetOpenFilename.Добрый!
Из Аутлука открываю эксель файл. Имя его каждый раз — разное. Как закрыть его так, чтобы при этом закрывалось само приложение Эксель? Сохранять изменения не нужно.
Код:закрывает саму книгу, но при этом сама прога остаётся висеть со своим интерфейсом. Можно нажать Ctlr+O и открыть какой-нибудь xls-файл.
При этом на компе могут быть открыты другие файлы, поэтому командане допустима.
Что делать?Сергей, используйте глобальные переменные, если файл Excel открывается и закрывается разными процедурами (переменные позволят закрыть тот самый файл и тот самый экземпляр приложения, в котором открыт файл):
А как открывать книгу, если имя файла совпадает с названием из массива.
То есть есть массив переменных которые записаны в столбец начиная с столбца А строки 2. Названия всегда разные.но совпадают с названием файла. Можно ли поочередно открыть их для редактирования через VBAПривет, YAN!
Используйте следующий код для открытия по очереди файлов Excel, имена которых записаны в первый столбец со второй ячейки:Объявление глобальной переменной n размещено в разделе Declarations программного модуля. Число 15 соответствует номеру строки последней ячейки диапазона с именами рабочих книг.
имеется 2 книги (обе открытые)
Книга 1 Лист1 ячейка D4 формула ссылается на вторую книгу
='[Книга 2.xls]Лист1′!$F$5Используя ссылку (без ручного ввода) надо обратиться Книга 2, скопировать 5 строк ниже ссылки (строка6:строка10) и вставить в рабочую книгу(Книга 1)
0mega, а ‘[Книга 2.xls]Лист1’!$F$5 — это постоянное значение? И в какое место какого листа Книги 1 строки должны быть вставлены?
«… а ‘[Книга 2.xls]Лист1’!$F$5 – это постоянное значение? …»
Нет !
Сценарий такой.
В бухгалтерии есть такой термин «Инвентаризация»
В связи с пандемией — движение по складу было минимальное. Большинство позиций остались нетронутыми.
Значит нет необходимости «набивать мозоли» на повторном вводе.
Я вижу такое решение
Есть Книга1.
Юзер открывает прошлогодний файл и (напр. в ячейке F5) визуально сравнивает итоговые значения с настоящими.
если показания совпадают — тогда проверяется Лист2, Лист3…потом будет другая книга
Если значение F5 (это тоже не постоянный адрес ) не совпадает с реальным, тогда в Книга1 юзер ставит «=» и кликает на F5 другой книги
В Книге1 автоматически формируется адрес неправильного значения.
Теперь топчем кнопочку макроса.
макрос берет адрес из ссылки и обращается ко второй Книге, копирует нужный мне массив и загружает в Первую книгу.
Юзер лечит (меняет, добавляет, редактирует ) больной массив и отправляет обратно.
Возможно есть какой-то более простой способ, но «самая короткая дорога — это та которую ты знаешь»Этот код VBA копирует пять строк под указанной ячейкой из открытой книги Excel по адресу из ячейки D4 Листа1 текущей книги и вставляет их в текущую книгу в пять строк под ячейкой D4 Листа1:
Using Workbook Object in Excel VBA (Open, Close, Save, Set)
In this tutorial, I will cover the how to work with workbooks in Excel using VBA.
In Excel, a ‘Workbook’ is an object that is a part of the ‘Workbooks’ collection. Within a workbook, you have different objects such as worksheets, chart sheets, cells and ranges, chart objects, shapes, etc.
With VBA, you can do a lot of stuff with a workbook object – such as open a specific workbook, save and close workbooks, create new workbooks, change the workbook properties, etc.
So let’s get started.
If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training.
This Tutorial Covers:
Referencing a Workbook using VBA
There are different ways to refer to a Workbook object in VBA.
The method you choose would depend on what you want to get done.
In this section, I will cover the different ways to refer to a workbook along with some example codes.
Using Workbook Names
If you have the exact name of the workbook that you want to refer to, you can use the name in the code.
Let’s begin with a simple example.
If you have two workbooks open, and you want to activate the workbook with the name – Examples.xlsx, you can use the below code:
Note that you need to use the file name along with the extension if the file has been saved. If it hasn’t been saved, then you can use the name without the file extension.
If you’re not sure what name to use, take help from the Project Explorer.
If you want to activate a workbook and select a specific cell in a worksheet in that workbook, you need to give the entire address of the cell (including the Workbook and the Worksheet name).
The above code first activates Sheet1 in the Examples.xlsx workbook and then selects cell A1 in the sheet.
You will often see a code where a reference to a worksheet or a cell/range is made without referring to the workbook. This happens when you’re referring to the worksheet/ranges in the same workbook that has the code in it and is also the active workbook. However, in some cases, you do need to specify the workbook to make sure the code works (more on this in the ThisWorkbook section).
Using Index Numbers
You can also refer to the workbooks based on their index number.
For example, if you have three workbooks open, the following code would show you the names of the three workbooks in a message box (one at a time).
The above code uses MsgBox – which is a function that shows a message box with the specified text/value (which is the workbook name in this case).
One of the troubles I often have with using index numbers with Workbooks is that you never know which one is the first workbook and which one is the second and so on. To be sure, you would have to run the code as shown above or something similar to loop through the open workbooks and know their index number.
Excel treats the workbook opened first to have the index number as 1, and the next one as 2 and so on.
Despite this drawback, using index numbers can come in handy.
For example, if you want to loop through all the open workbooks and save all, you can use the index numbers.
In this case, since you want this to happen to all the workbooks, you’re not concerned about their individual index numbers.
The below code would loop through all the open workbooks and close all except the workbook that has this VBA code.
The above code counts the number of open workbooks and then goes through all the workbooks using the For Each loop.
It uses the IF condition to check if the name of the workbook is the same as that of the workbook where the code is being run.
If it’s not a match, it closes the workbook and moves to the next one.
Note that we have run the loop from WbCount to 1 with a Step of -1. This is done as with each loop, the number of open workbooks is decreasing.
ThisWorkbook is covered in detail in the later section.
Using ActiveWorkbook
ActiveWorkbook, as the name suggests, refers to the workbook that is active.
The below code would show you the name of the active workbook.
When you use VBA to activate another workbook, the ActiveWorkbook part in the VBA after that would start referring to the activated workbook.
Here is an example of this.
If you have a workbook active and you insert the following code into it and run it, it would first show the name of the workbook that has the code and then the name of Examples.xlsx (which gets activated by the code).
Note that when you create a new workbook using VBA, that newly created workbook automatically becomes the active workbook.
Using ThisWorkbook
ThisWorkbook refers to the workbook where the code is being executed.
Every workbook would have a ThisWorkbook object as a part of it (visible in the Project Explorer).
‘ThisWorkbook’ can store regular macros (similar to the ones that we add-in modules) as well as event procedures. An event procedure is something that is triggered based on an event – such as double-clicking on a cell, or saving a workbook or activating a worksheet.
Any event procedure that you save in this ‘ThisWorkbook’ would be available in the entire workbook, as compared to the sheet level events which are restricted to the specific sheets only.
For example, if you double-click on the ThisWorkbook object in the Project Explorer and copy-paste the below code in it, it will show the cell address whenever you double-click on any of the cells in the entire workbook.
While ThisWorkbook’s main role is to store event procedure, you can also use it to refer to the workbook where the code is being executed.
The below code would return the name of the workbook in which the code is being executed.
The benefit of using ThisWorkbook (over ActiveWorkbook) is that it would refer to the same workbook (the one that has the code in it) in all the cases. So if you use a VBA code to add a new workbook, the ActiveWorkbook would change, but ThisWorkbook would still refer to the one that has the code.
Creating a New Workbook Object
The following code will create a new workbook.
When you add a new workbook, it becomes the active workbook.
The following code will add a new workbook and then show you the name of that workbook (which would be the default Book1 type name).
Open a Workbook using VBA
You can use VBA to open a specific workbook when you know the file path of the workbook.
The below code will open the workbook – Examples.xlsx which is in the Documents folder on my system.
In case the file exists in the default folder, which is the folder where VBA saves new files by default, then you can just specify the workbook name – without the entire path.
In case the workbook that you’re trying to open doesn’t exist, you’ll see an error.
To avoid this error, you can add a few lines to your code to first check whether the file exists or not and if it exists then try to open it.
The below code would check the file location and if it doesn’t exist, it will show a custom message (not the error message):
You can also use the Open dialog box to select the file that you want to open.
The above code opens the Open dialog box. When you select a file that you want to open, it assigns the file path to the FilePath variable. Workbooks.Open then uses the file path to open the file.
In case the user doesn’t open a file and clicks on Cancel button, FilePath becomes False. To avoid getting an error in this case, we have used the ‘On Error Resume Next’ statement.
Saving a Workbook
To save the active workbook, use the code below:
This code works for the workbooks that have already been saved earlier. Also, since the workbook contains the above macro, if it hasn’t been saved as a .xlsm (or .xls) file, you will lose the macro when you open it next.
If you’re saving the workbook for the first time, it will show you a prompt as shown below:
When saving for the first time, it’s better to use the ‘Saveas’ option.
The below code would save the active workbook as a .xlsm file in the default location (which is the document folder in my system).
If you want the file to be saved in a specific location, you need to mention that in the Filename value. The below code saves the file on my desktop.
If you want the user to get the option to select the location to save the file, you can use call the Saveas dialog box. The below code shows the Saveas dialog box and allows the user to select the location where the file should be saved.
Note that instead of using FileFormat:=xlOpenXMLWorkbookMacroEnabled, you can also use FileFormat:=52, where 52 is the code xlOpenXMLWorkbookMacroEnabled.
Saving All Open Workbooks
If you have more than one workbook open and you want to save all the workbooks, you can use the code below:
The above saves all the workbooks, including the ones that have never been saved. The workbooks that have not been saved previously would get saved in the default location.
If you only want to save those workbooks that have previously been saved, you can use the below code:
Saving and Closing All Workbooks
If you want to close all the workbooks, except the workbook that has the current code in it, you can use the code below:
The above code would close all the workbooks (except the workbook that has the code – ThisWorkbook). In case there are changes in these workbooks, the changes would be saved. In case there is a workbook that has never been saved, it will show the save as dialog box.
Save a Copy of the Workbook (with Timestamp)
When I am working with complex data and dashboard in Excel workbooks, I often create different versions of my workbooks. This is helpful in case something goes wrong with my current workbook. I would at least have a copy of it saved with a different name (and I would only lose the work I did after creating a copy).
Here is the VBA code that will create a copy of your workbook and save it in the specified location.
The above code would save a copy of your workbook every time you run this macro.
While this works great, I would feel more comfortable if I had different copies saved whenever I run this code. The reason this is important is that if I make an inadvertent mistake and run this macro, it will save the work with the mistakes. And I wouldn’t have access to the work before I made the mistake.
To handle such situations, you can use the below code that saves a new copy of the work each time you save it. And it also adds a date and timestamp as a part of the workbook name. This can help you track any mistake you did as you never lose any of the previously created backups.
The above code would create a copy every time you run this macro and add a date/time stamp to the workbook name.
Create a New Workbook for Each Worksheet
In some cases, you may have a workbook that has multiple worksheets, and you want to create a workbook for each worksheet.
This could be the case when you have monthly/quarterly reports in a single workbook and you want to split these into one workbook for each worksheet.
Or, if you have department wise reports and you want to split these into individual workbooks so that you can send these individual workbooks to the department heads.
Here is the code that will create a workbook for each worksheet, give it the same name as that of the worksheet, and save it in the specified folder.
In the above code, we have used two variable ‘ws’ and ‘wb’.
The code goes through each worksheet (using the For Each Next loop) and creates a workbook for it. It also uses the copy method of the worksheet object to create a copy of the worksheet in the new workbook.
Note that I have used the SET statement to assign the ‘wb’ variable to any new workbook that is created by the code.
You can use this technique to assign a workbook object to a variable. This is covered in the next section.
Assign Workbook Object to a Variable
In VBA, you can assign an object to a variable, and then use the variable to refer to that object.
For example, in the below code, I use VBA to add a new workbook and then assign that workbook to the variable wb. To do this, I need to use the SET statement.
Once I have assigned the workbook to the variable, all the properties of the workbook are made available to the variable as well.
Note that the first step in the code is to declare ‘wb’ as a workbook type variable. This tells VBA that this variable can hold the workbook object.
The next statement uses SET to assign the variable to the new workbook that we are adding. Once this assignment is done, we can use the wb variable to save the workbook (or do anything else with it).
Looping through Open Workbooks
We have already seen a few examples codes above that used looping in the code.
In this section, I will explain different ways to loop through open workbooks using VBA.
Suppose you want to save and close all the open workbooks, except the one with the code in it, then you can use the below code:
The above code uses the For Each loop to go through each workbook in the Workbooks collection. To do this, we first need to declare ‘wb’ as the workbook type variable.
In every loop cycle, each workbook name is analyzed and if it doesn’t match the name of the workbook that has the code, it’s closed after saving its content.
The same can also be achieved with a different loop as shown below:
The above code uses the For Next loop to close all the workbooks except the one that has the code in it. In this case, we don’t need to declare a workbook variable, but instead, we need to count the total number of open workbooks. When we have the count, we use the For Next loop to go through each workbook. Also, we use the index number to refer to the workbooks in this case.
Note that in the above code, we are looping from WbCount to 1 with Step -1. This is needed as with each loop, the workbook gets closed and the number of workbooks gets decreased by 1.
Error while Working with the Workbook Object (Run-time error ‘9’)
One of the most common error you may encounter when working with workbooks is – Run-time Error ‘9’ – Subscript out of range.
Generally, VBA errors are not very informative and often leave it to you to figure out what went wrong.
Here are some of the possible reasons that may lead to this error:
- The workbook that you’re trying to access does not exist. For example, if I am trying to access the fifth workbook using Workbooks(5), and there are only 4 workbooks open, then I will get this error.
- If you’re using a wrong name to refer to the workbook. For example, if your workbook name is Examples.xlsx and you use Example.xlsx. then it will show you this error.
- If you haven’t saved a workbook, and you use the extension, then you get this error. For example, if your workbook name is Book1, and you use the name Book1.xlsx without saving it, you will get this error.
- The workbook you’re trying to access is closed.
Get a List of All Open Workbooks
If you want to get a list of all the open workbooks in the current workbook (the workbook where you’re running the code), you can use the below code:
The above code adds a new worksheet and then lists the name of all the open workbooks.
If you want to get their file path as well, you can use the below code:
Open the Specified Workbook by Double-clicking on the Cell
If you have a list of file paths for Excel workbooks, you can use the below code to simply double-click on the cell with the file path and it will open that workbook.
This code would be placed in the ThisWorkbook code window.
- Double click on the ThisWorkbook object in the project explorer. Note that the ThisWorkbook object should be in the workbook where you want this functionality.
- Copy and paste the above code.
Now, if you have the exact path of the files that you want to open, you can do that by simply double-clicking on the file path and VBA would instantly open that workbook.
Where to Put the VBA Code
Wondering where the VBA code goes in your Excel workbook?
Excel has a VBA backend called the VBA editor. You need to copy and paste the code into the VB Editor module code window.
Here are the steps to do this:
- Go to the Developer tab.
- Click on the Visual Basic option. This will open the VB editor in the backend.
- In the Project Explorer pane in the VB Editor, right-click on any object for the workbook in which you want to insert the code. If you don’t see the Project Explorer go to the View tab and click on Project Explorer.
- Go to Insert and click on Module. This will insert a module object for your workbook.
- Copy and paste the code in the module window.