Використання об’єкта робочої книги в Excel VBA (відкрити, закрити, зберегти, встановити)

У цьому підручнику я розгляну, як працювати з робочими книгами в Excel за допомогою VBA.

В Excel «Робоча книга» - це об’єкт, який є частиною колекції «Робочі зошити». У книзі є різні об’єкти, такі як аркуші, аркуші діаграм, клітинки та діапазони, об’єкти діаграм, фігури тощо.

За допомогою VBA ви можете багато чого робити з об’єктом книги - наприклад, відкривати певну книгу, зберігати та закривати книги, створювати нові книги, змінювати властивості книги тощо.

Тож почнемо.

Усі коди, про які я згадую у цьому посібнику, потрібно розмістити у редакторі Visual Basic. Перейдіть до розділу «Де розмістити код VBA», щоб дізнатися, як він працює.

Якщо ви зацікавлені у вивченні VBA простим способом, ознайомтеся з моїм Онлайн навчання Excel VBA.

Посилання на робочу книгу за допомогою VBA

Існують різні способи посилання на об’єкт книги у VBA. Вибраний вами метод буде залежати від того, що ви хочете зробити. У цьому розділі я розгляну різні способи звернення до робочої книги разом із деякими прикладами кодів.

Використання імен робочих книг

Якщо у вас є точна назва робочої книги, на яку ви хочете звернутись, ви можете використовувати її у коді.

Почнемо з простого прикладу.

Якщо у вас є дві відкриті книги, і ви хочете активувати книгу з назвою - Examples.xlsx, ви можете скористатися наведеним нижче кодом:

Sub ActivateWorkbook () Робочі книги ("Приклади.xlsx"). Активуйте End Sub

Зауважте, що якщо файл було збережено, вам потрібно використовувати назву файлу разом із розширенням. Якщо він не був збережений, ви можете використовувати ім’я без розширення файлу.

Якщо ви не впевнені, яку назву використовувати, зверніться за допомогою до Провідника проектів.

Якщо ви хочете активувати книгу та вибрати певну клітинку на робочому аркуші в цій книзі, вам потрібно вказати повну адресу комірки (включаючи книгу та назву робочого аркуша).

Sub ActivateWorkbook () Робочі книги ("Приклади.xlsx"). Робочі аркуші ("Sheet1"). Активувати діапазон ("A1"). Виберіть End Sub

Наведений вище код спочатку активує Sheet1 у книзі Examples.xlsx, а потім вибирає комірку A1 на аркуші.

Часто ви побачите код, у якому робиться посилання на аркуш або клітинку/діапазон без посилання на книгу. Це відбувається, коли ви посилаєтесь на аркуш/діапазони в тій самій книзі, у якій є код, а також є активною книгою. Однак у деяких випадках вам потрібно вказати книгу, щоб переконатися, що код працює (докладніше про це у розділі ThisWorkbook).

Використання номерів індексів

Ви також можете звернутися до робочих книг на основі їх індексного номера.

Наприклад, якщо у вас відкрито три робочі книги, наступний код покаже вам імена трьох книг у вікні повідомлень (по одному за раз).

Sub WorkbookName () Книги MsgBox (1) .Name MsgBox Workbooks (2) .Name MsgBox Workbooks (3) .Name End Sub

У наведеному вище коді використовується MsgBox - це функція, яка показує вікно повідомлення із зазначеним текстом/значенням (яке в цьому випадку є назвою книги).

Одна з проблем, які у мене часто виникають при використанні індексних номерів у робочих книгах, полягає в тому, що ви ніколи не знаєте, яка з них є першою, а друга - другою тощо. Напевно, вам доведеться запустити код, як показано вище, або щось подібне, щоб прокрутити відкриті книги та знати їх номер індексу.

Excel вважає, що книга, відкрита спочатку, має номер індексу як 1, а наступна - як 2 тощо.

Незважаючи на цей недолік, використання індексних номерів може стати в нагоді. Наприклад, якщо ви хочете переглянути всі відкриті книги та зберегти всі, ви можете використовувати номери індексів. У цьому випадку, оскільки ви хочете, щоб це сталося з усіма робочими книжками, вас не хвилює їх індивідуальний номер індексу.

Код, наведений нижче, буде перебирати всі відкриті книги та закривати всі, крім книги, у якій є цей код VBA.

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name then Workbooks (i) .Close End If Next i End Sub

Наведений вище код підраховує кількість відкритих книг, а потім проходить усі робочі книги, використовуючи цикл «Для кожного».

Він використовує умову IF, щоб перевірити, чи ім'я книги збігається з назвою книги, де виконується код.

Якщо це не збіг, він закриває книгу і переходить до наступної.

Зауважте, що ми запустили цикл від WbCount до 1 з кроком -1. Це робиться так, як з кожним циклом кількість відкритих книг зменшується.

Цей робочий зошит детально розглянуто у наступному розділі.

Використання ActiveWorkbook

ActiveWorkbook, як випливає з назви, відноситься до активної книги.

Код нижче покаже вам ім’я активної книги.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name End Sub

Коли ви використовуєте VBA для активації іншої книги, частина ActiveWorkbook у VBA після цього почне посилатися на активовану книгу.

Ось приклад цього.

Якщо у вас є активна книга, і ви вставляєте в неї наступний код і запускаєте її, вона спочатку відображатиме назву книги, що містить код, а потім ім’я Examples.xlsx (яке активується кодом).

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Workbooks ("Examples.xlsx"). Активуйте MsgBox ActiveWorkbook.Name End Sub

Зауважте, що під час створення нової книги за допомогою VBA ця новостворена книга автоматично стає активною книгою.

Використання цієї робочої книги

Ця робоча книга відноситься до робочої книги, де виконується код.

Кожна робоча книга матиме об’єкт ThisWorkbook як її частину (видно в Провіднику проектів).

"Ця робоча книга" може зберігати звичайні макроси (подібні до тих, які ми додаємо до модулів), а також процедури подій. Процедура події - це те, що запускається на основі події - наприклад, подвійне клацання по клітинці або збереження книги або активація робочого аркуша.

Будь -яка процедура події, яку ви зберігаєте в цій "цій робочій книзі", буде доступна у всій книзі порівняно з подіями на рівні аркуша, які обмежені лише певними аркушами.

Наприклад, якщо двічі клацнути об’єкт ThisWorkbook у Провіднику проектів і скопіювати в нього наведений нижче код, він відображатиме адресу комірки кожного разу, коли ви двічі клацнете на будь-якій клітинці у всій книзі.

Private Sub Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target as Range, Cancel As Boolean) MsgBox Target.Address End Sub

Хоча основна роль ThisWorkbook - зберігати процедуру події, ви також можете використовувати її для посилання на книгу, де виконується код.

Нижче наведений код повертає назву книги, у якій виконується код.

Sub ThisWorkbookName () MsgBox ThisWorkbook.Name End Sub

Перевага використання ThisWorkbook (над ActiveWorkbook) полягає в тому, що він буде посилатися на одну і ту ж книгу (ту, у якій є код) у всіх випадках. Отже, якщо ви використовуєте код VBA для додавання нової книги, ActiveWorkbook зміниться, але ThisWorkbook все одно буде посилатися на ту, що містить код.

Створення нового об’єкта робочої книги

Наступний код створить нову книгу.

Sub CreateNewWorkbook () Робочі книги. Додати End Sub

Коли ви додаєте нову книгу, вона стає активною книгою.

Наступний код додасть нову книгу, а потім покаже вам назву цієї книги (яка буде типовою назвою типу Book1).

Sub CreateNewWorkbook () Workbooks.Add MsgBox ActiveWorkbook.Name End Sub

Відкрийте книгу за допомогою VBA

Ви можете використовувати VBA, щоб відкрити певну книгу, коли вам відомий шлях до файлу у книзі.

Нижче наведений код відкриє книгу - Examples.xlsx, яка знаходиться в папці Documents у моїй системі.

Sub OpenWorkbook () Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") End Sub

Якщо файл існує у папці за замовчуванням, тобто в папці, де VBA зберігає нові файли за замовчуванням, то ви можете просто вказати назву книги - без усього шляху.

Sub OpenWorkbook () Workbooks.Open ("Examples.xlsx") End Sub

Якщо книга, яку ви намагаєтесь відкрити, не існує, ви побачите помилку.

Щоб уникнути цієї помилки, ви можете додати кілька рядків до свого коду, щоб спочатку перевірити, чи існує файл чи ні, а якщо він існує, то спробуйте його відкрити.

Код, наведений нижче, перевірить розташування файлу, і якщо він не існує, він покаже спеціальне повідомлення (а не повідомлення про помилку):

Sub OpenWorkbook () Якщо Dir ("C: \ Users \ sumit \ Documents \ Examples.xlsx") "" Тоді Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") Інший MsgBox "Файл не 't exist "End If End Sub

Ви також можете скористатися діалоговим вікном «Відкрити», щоб вибрати файл, який потрібно відкрити.

Sub OpenWorkbook () On Error Resume Next Dim Dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub

Наведений вище код відкриває діалогове вікно Відкрити. Коли ви вибираєте файл, який потрібно відкрити, він призначає шлях до змінної FilePath. Workbooks.Open потім використовує шлях до файлу, щоб відкрити файл.

Якщо користувач не відкриває файл і натискає кнопку Скасувати, FilePath стає False. Щоб уникнути помилки в цьому випадку, ми використали оператор "On Error Resume Next".

Пов'язані: Дізнайтеся все про обробку помилок у Excel VBA

Збереження робочої книжки

Щоб зберегти активну книгу, скористайтеся наведеним нижче кодом.

Sub SaveWorkbook () ActiveWorkbook.Save End Sub

Цей код працює для книг, які вже були збережені раніше. Крім того, оскільки книга містить вищезазначений макрос, якщо вона не була збережена як файл .xlsm (або .xls), ви втратите макрос, коли відкриєте його наступного разу.

Якщо ви зберігаєте книгу вперше, вона покаже вам підказку, як показано нижче:

Під час першого збереження краще скористатися опцією «Зберегти».

Наведений нижче код збереже активну книгу як файл .xlsm у розташуванні за замовчуванням (це папка документів у моїй системі).

Sub SaveWorkbook () ActiveWorkbook.SaveAs Ім'я файлу: = "Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Якщо ви хочете, щоб файл був збережений у певному місці, вам потрібно вказати це у значенні Filename. Код нижче зберігає файл на моєму робочому столі.

Sub SaveWorkbook () ActiveWorkbook.SaveAs Ім'я файлу: = "C: \ Users \ sumit \ Desktop \ Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Якщо ви хочете, щоб користувач отримав можливість вибрати місце для збереження файлу, можна скористатися викликом діалогового вікна Зберегти. Нижче наведений код показує діалогове вікно Зберегти та дозволяє користувачеві вибрати місце, де слід зберегти файл.

Sub SaveWorkbook () Dim FilePath As String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Ім'я файлу: = FilePath & ".xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Зауважте, що замість використання FileFormat: = xlOpenXMLWorkbookMacroEnabled, ви також можете використовувати FileFormat: = 52, де 52 - це код xlOpenXMLWorkbookMacroEnabled.

Збереження всіх відкритих книг

Якщо у вас відкрито кілька робочих книг і ви хочете зберегти всі книги, скористайтеся наведеним нижче кодом.

Sub SaveAllWorkbooks () Dim wb як робоча книга для кожної wb у робочих книгах wb. Зберегти наступну wb End Sub

Вищезазначене зберігає всі робочі зошити, включаючи ті, які ніколи не зберігалися. Робочі книги, які не були збережені раніше, будуть збережені у розташуванні за умовчанням.

Якщо ви хочете зберегти лише ті книги, які були раніше збережені, ви можете скористатися наведеним нижче кодом:

Sub SaveAllWorkbooks () Dim wb як робоча книга для кожної wb у робочих книгах If wb.Path "" Тоді wb.Save End, якщо наступний wb End Sub

Збереження та закриття всіх робочих книг

Якщо ви хочете закрити всі книги, крім книги, у якій є поточний код, ви можете скористатися наведеним нижче кодом.

Sub CloseandSaveWorkbooks () Dim wb як робоча книга для кожної wb у робочих книгах If wb.Name ThisWorkbook.Name Тоді wb.Close SaveChanges: = True End Якщо наступний wb End Sub

Наведений вище код закриває всі робочі книги (крім книги, у якій є код - ThisWorkbook). Якщо у цих книгах є зміни, їх буде збережено. Якщо є робоча книга, яка ніколи не зберігалася, вона покаже діалогове вікно зберегти як.

Збережіть копію робочої книги (з міткою часу)

Коли я працюю зі складними даними та інформаційною панеллю в робочих книгах Excel, я часто створюю різні версії своїх книг. Це корисно на випадок, якщо з моєю поточною книгою щось піде не так. Я б принаймні мав зберегти його копію з іншим ім’ям (і я втратив би роботу, яку я зробив після створення копії).

Ось код VBA, який створить копію вашої книги та збереже її у вказаному місці.

Sub CreateaCopyofWorkbook () ThisWorkbook.SaveCopyAs Ім'я файлу: = "C: \ Users \ sumit \ Desktop \ BackupCopy.xlsm" End Sub

Наведений вище код зберігатиме копію вашої книги щоразу, коли ви запускатимете цей макрос.

Хоча це чудово працює, мені було б комфортніше, якби під час запуску цього коду зберігалися різні копії. Причина, чому це важливо, полягає в тому, що якщо я помилково помиляюся і запускаю цей макрос, це збереже роботу з помилками. І я не мав би доступу до твору, поки не зробив помилки.

Для вирішення таких ситуацій можна використовувати наведений нижче код, який зберігає нову копію твору щоразу, коли ви її зберігаєте. Він також додає дату та позначку часу як частину назви книги. Це може допомогти вам відстежити будь -яку помилку, яку ви зробили, оскільки ви ніколи не втратите жодної з раніше створених резервних копій.

Private Sub Workbook_BeforeSave (ByVal SaveAsUI Як Boolean, Скасувати як Boolean) ThisWorkbook.SaveCopyAs Ім'я файлу: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-yy-hh-mm-ss -AMPM ") &" .xlsm "Закінчити суб

Наведений вище код створить копію кожного разу, коли ви запустите цей макрос, і додасть позначку дати/часу до назви книги.

Створіть новий робочий зошит для кожного аркуша

У деяких випадках у вас може бути книга з кількома аркушами, і ви хочете створити книгу для кожного аркуша.

Це може мати місце, якщо у вас є щомісячні/квартальні звіти в одній робочій книзі, і ви хочете розділити їх на одну робочу книгу для кожного аркуша.

Або, якщо у вас є звіти про відділи, і ви хочете розділити їх на окремі робочі зошити, щоб ви могли надіслати ці окремі книжки керівникам відділів.

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

Sub CreateWorkbookforWorksheets () Dim ws As Worksheet Dim wb As Workbook for each ws in ThisWorkbook.Worksheets Set wb = Workbooks.Add ws.Copy Before: = wb.Sheets (1) Application.DisplayAlerts = False wb.Sheets (2) .Delete Application.DisplayAlerts = True wb.SaveAs "C: \ Users \ sumit \ Desktop \ Test \" & ws.Name & ".xlsx" wb.Закрити Далі ws Завершити суб

У наведеному вище коді ми використовували дві змінні «ws» і «wb».

Код проходить кожен аркуш (за допомогою циклу For Every Next) і створює для нього книгу. Він також використовує метод копіювання об'єкта аркуша для створення копії аркуша в новій книзі.

Зауважте, що я використав оператор SET для призначення змінної "wb" будь -якій новій книзі, створеній кодом.

Ви можете використовувати цей прийом, щоб призначити об’єкт робочої книги змінній. Про це йдеться у наступному розділі.

Призначте об’єкт робочої книги змінній

У VBA можна призначити об’єкт змінній, а потім використати змінну для посилання на цей об’єкт.

Наприклад, у наведеному нижче коді я використовую VBA, щоб додати нову книгу, а потім призначити цю книгу змінній wb. Для цього мені потрібно використовувати оператор SET.

Після того, як я призначив книгу змінної, всі властивості книги також стають доступними для змінної.

Sub AssigntoVariable () Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Ім'я файлу: = "C: \ Users \ sumit \ Desktop \ Examples.xlsx" End Sub

Зауважте, що першим кроком у коді є оголошення "wb" як змінної типу книги. Це повідомляє VBA, що ця змінна може містити об'єкт книги.

Наступний вираз використовує SET для призначення змінної новій книзі, яку ми додаємо. Як тільки це призначення буде виконано, ми можемо використовувати змінну wb, щоб зберегти книгу (або зробити що -небудь ще з нею).

Перегляд відкритих робочих книг

Ми вже бачили кілька прикладів кодів вище, які використовували цикл у коді.

У цьому розділі я поясню різні способи перегляду відкритих книг за допомогою VBA.

Припустимо, ви хочете зберегти та закрити всі відкриті книги, крім тієї, у якій є код, тоді ви можете скористатися наведеним нижче кодом:

Sub CloseandSaveWorkbooks () Dim wb як робоча книга для кожної wb у робочих книгах If wb.Name ThisWorkbook.Name Тоді wb.Close SaveChanges: = True End Якщо наступний wb End Sub

У наведеному вище коді використовується цикл "Для кожного" для перегляду кожної книги у колекції "Робочі книги". Для цього спочатку нам потрібно оголосити "wb" як змінну типу книги.

У кожному циклі циклу кожна назва книги аналізується, і якщо вона не збігається з назвою книги з кодом, її закривають після збереження її вмісту.

Те ж саме можна досягти за допомогою іншого циклу, як показано нижче:

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i). Назвіть ThisWorkbook.Name Then Workbooks (i). Закрийте SaveChanges: = True End If Next i End Sub

Наведений вище код використовує цикл For Next для закриття всіх книг, крім тієї, у якій є код. У цьому випадку нам не потрібно оголошувати змінну книги, а натомість нам потрібно порахувати загальну кількість відкритих книг. Коли у нас є підрахунок, ми використовуємо цикл For Next для перегляду кожної книги. Також у цьому випадку ми використовуємо номер індексу для посилання на робочі книги.

Зауважте, що у наведеному вище коді ми виконуємо цикл від WbCount до 1 з кроком -1. Це потрібно, оскільки з кожним циклом книга закривається, а кількість книг зменшується на 1.

Помилка під час роботи з об’єктом книги (помилка під час виконання «9»)

Однією з найпоширеніших помилок, з якими ви можете зіткнутися під час роботи з книгами, є - Помилка під час виконання «9» - Індекс виходить за межі діапазону.

Як правило, помилки VBA не дуже інформативні і часто залишають за вами, щоб з'ясувати, що пішло не так.

Ось деякі з можливих причин, які можуть призвести до цієї помилки:

• Книга, до якої ви намагаєтесь отримати доступ, не існує. Наприклад, якщо я намагаюся отримати доступ до п’ятої книги за допомогою робочих книг (5), а відкрито лише 4 робочі книги, я отримаю цю помилку.
• Якщо ви використовуєте неправильне ім’я для посилання на книгу. Наприклад, якщо ім’я вашої книги - Examples.xlsx, і ви використовуєте Example.xlsx. тоді він покаже вам цю помилку.
• Якщо ви не зберегли книгу та використовуєте розширення, то ви отримаєте цю помилку. Наприклад, якщо ім’я вашої книги - Book1, і ви використовуєте ім’я Book1.xlsx, не зберігаючи його, ви отримаєте цю помилку.
• Робоча книга, до якої ви намагаєтесь отримати доступ, закрита.

Отримайте список усіх відкритих книг

Якщо ви хочете отримати список усіх відкритих книг у поточній книзі (книзі, де ви запускаєте код), ви можете скористатися наведеним нижче кодом:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Значення = Робочі книжки (i). Ім'я Далі i Закінчення підп

Наведений вище код додає новий аркуш, а потім перераховує ім’я всіх відкритих книг.

Якщо ви також хочете отримати шлях до їх файлу, ви можете скористатися наведеним нижче кодом:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate For i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Значення = Робочі книги (i). Шлях & "\" & Робочі книги (i). Ім'я Далі i Закінчити підрозділ

Відкрийте зазначену книгу, двічі клацнувши по клітинці

Якщо у вас є список шляхів до файлів для книг Excel, ви можете скористатися наведеним нижче кодом, щоб просто двічі клацнути по клітинці з шляхом до файлу, і вона відкриє цю книгу.

Приватна підручникова робоча книга_СхемаBeforeDoubleClick (ByVal Sh як об’єкт, ціль ByVal як діапазон, скасувати як логічне значення) Робочі книги. Відкрийте Target.Value End Sub

Цей код буде розміщено у вікні коду ThisWorkbook.

Зробити це:

• Двічі клацніть об’єкт ThisWorkbook у провіднику проектів. Зауважте, що об’єкт ThisWorkbook повинен бути у книзі, де ви хочете отримати цю функціональність.
• Скопіюйте та вставте наведений вище код.

Тепер, якщо у вас є точний шлях до файлів, які ви хочете відкрити, ви можете зробити це, просто двічі клацнувши по шляху до файлу, і VBA миттєво відкриє цю книгу.

Де розмістити код VBA

Цікаво, де код VBA міститься у вашій книзі Excel?

Excel має бекенд VBA, який називається редактором VBA. Вам потрібно скопіювати та вставити код у вікно коду модуля VB Editor.

Нижче наведено кроки для цього.

1. Перейдіть на вкладку Розробник.
2. Натисніть на опцію Visual Basic. Це відкриє редактор VB у бекенді.
3. На панелі «Провідник проектів» у редакторі VB клацніть правою кнопкою миші будь-який об’єкт книги, до якого потрібно вставити код. Якщо ви не бачите Провідник проектів, перейдіть на вкладку Вигляд і натисніть Провідник проектів.
4. Перейдіть до пункту Вставити та натисніть Модуль. Це додасть об’єкт модуля до вашої книги.
5. Скопіюйте та вставте код у вікно модуля.

Вам також можуть сподобатися такі підручники Excel VBA:

• Як записати макрос в Excel.
• Створення користувальницької функції в Excel.
• Як створити та використовувати надбудову в Excel.
• Як відновити макроси, розмістивши їх у книзі особистих макросів.
• Отримайте список імен файлів з папки в Excel (з VBA та без неї).
• Як користуватися функцією Excel VBA InStr (з практичними прикладами).
• Як сортувати дані в Excel за допомогою VBA (Покроковий посібник).