Използване на обект на работна книга в Excel VBA (отваряне, затваряне, запазване, задаване)

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

В Excel „Работна книга“ е обект, който е част от колекцията „Работни книги“. В рамките на работна книга имате различни обекти като работни листове, диаграми, клетки и диапазони, обекти на диаграми, форми и т.н.

С VBA можете да правите много неща с обект на работна книга - например да отваряте конкретна работна книга, да запазвате и затваряте работни книги, да създавате нови работни книги, да променяте свойствата на работната книга и т.н.

Така че нека започнем.

Всички кодове, които споменавам в този урок, трябва да бъдат поставени в редактора на Visual Basic. Отидете в раздела „Къде да поставите VBA кода“, за да знаете как работи.

Ако се интересувате да научите VBA по лесния начин, вижте моя Онлайн обучение по Excel VBA.

Позоваване на работна книга с помощта на VBA

Има различни начини да се обърнете към обект на работна книга във VBA. Избраният от вас метод ще зависи от това какво искате да направите. В този раздел ще разгледам различните начини за позоваване на работна книга заедно с някои примерни кодове.

Използване на имена на работни книги

Ако имате точното име на работната книга, към което искате да се обърнете, можете да използвате името в кода.

Нека започнем с прост пример.

Ако имате две отворени работни книги и искате да активирате работната книга с името - Examples.xlsx, можете да използвате кода по -долу:

Sub ActivateWorkbook () Workbooks ("Examples.xlsx"). Активирайте End Sub

Имайте предвид, че трябва да използвате името на файла заедно с разширението, ако файлът е записан. Ако не е запазен, можете да използвате името без разширението на файла.

Ако не сте сигурни какво име да използвате, вземете помощ от Project Explorer.

Ако искате да активирате работна книга и да изберете конкретна клетка в работен лист в тази работна книга, трябва да дадете целия адрес на клетката (включително работната книга и името на работния лист).

Sub ActivateWorkbook () Работни книги ("Примери.xlsx"). Работни листове ("Sheet1"). Активирайте диапазон ("A1"). Изберете End Sub

Горният код първо активира Sheet1 в работната книга Examples.xlsx и след това избира клетка A1 в листа.

Често ще виждате код, в който се прави препратка към работен лист или клетка/диапазон, без да се позовава на работната книга. Това се случва, когато се позовавате на работния лист/диапазони в същата работна книга, която има кода в нея и също е активната работна книга. В някои случаи обаче трябва да посочите работната книга, за да се уверите, че кодът работи (повече за това в раздела ThisWorkbook).

Използване на индексни номера

Можете също да се обърнете към работните книги въз основа на техния индекс номер.

Например, ако имате отворени три работни книги, следният код ще ви покаже имената на трите работни книги в поле за съобщение (едно по едно).

Sub WorkbookName () MsgBox Workbooks (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 до 1 Стъпка -1 Ако Workbooks (i). Име на ThisWorkbook.Name Тогава Workbooks (i). Затваряне на End If Next i End Sub

Горният код отчита броя на отворените работни книги и след това преминава през всички работни книги, използвайки цикъла За всеки.

Той използва условието IF, за да провери дали името на работната книга е същото като това на работната книга, където се изпълнява кодът.

Ако не съвпада, той затваря работната книга и преминава към следващата.

Обърнете внимание, че сме изпълнили цикъла от WbCount до 1 със стъпка от -1. Това се прави както при всеки цикъл броят на отворените работни книги намалява.

Тази работна книга е разгледана подробно в по -късния раздел.

Използване на ActiveWorkbook

ActiveWorkbook, както подсказва името, се отнася до работната книга, която е активна.

Кодът по -долу ще ви покаже името на активната работна книга.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name End Sub

Когато използвате VBA за активиране на друга работна книга, частта ActiveWorkbook във VBA след това ще започне да се позовава на активираната работна книга.

Ето един пример за това.

Ако имате активна работна книга и вмъкнете следния код в нея и я стартирате, тя първо ще покаже името на работната книга, която има кода, а след това и името на Examples.xlsx (което се активира от кода).

Под ActiveWorkbookName () MsgBox ActiveWorkbook.Name Работни книги ("Примери.xlsx"). Активирайте MsgBox ActiveWorkbook.Name End Sub

Обърнете внимание, че когато създавате нова работна книга с помощта на VBA, тази новосъздадена работна книга автоматично се превръща в активна работна книга.

Използване на тази работна книга

Тази работна книга се отнася до работната книга, където се изпълнява кодът.

Всяка работна книга ще има обект ThisWorkbook като част от него (видим в Project Explorer).

„ThisWorkbook“ може да съхранява обикновени макроси (подобни на тези, които добавяме модули), както и процедури за събития. Процедура за събитие е нещо, което се задейства въз основа на събитие - например двукратно щракване върху клетка, или запазване на работна книга или активиране на работен лист.

Всяка процедура за събития, която запазвате в „Тази работна книга“, би била налична в цялата работна книга, в сравнение със събитията на ниво лист, които са ограничени само до конкретните листове.

Например, ако щракнете двукратно върху обекта ThisWorkbook в Project Explorer и копирате и поставите кода по-долу в него, той ще показва адреса на клетката всеки път, когато щракнете двукратно върху някоя от клетките в цялата работна книга.

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 Създаване на работни книги на NewWorkbook (). Добавяне на 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 () If Dir ("C: \ Users \ sumit \ Documents \ Examples.xlsx") "" Тогава Workbooks.Open ("C: \ Users \ sumit \ Documents \ Examples.xlsx") Друг MsgBox "Файлът не не съществува "End If End Sub

Можете също да използвате диалоговия прозорец Отваряне, за да изберете файла, който искате да отворите.

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

Горният код отваря диалоговия прозорец Отваряне. Когато изберете файл, който искате да отворите, той присвоява пътя на файла към променливата FilePath. Workbooks.Open след това използва пътя на файла, за да отвори файла.

В случай, че потребителят не отвори файл и кликне върху бутона Отказ, FilePath става False. За да избегнем грешка в този случай, използвахме израза „On Error Resume Next“.

Свързани: Научете всичко за обработката на грешки в Excel VBA

Запазване на работна книга

За да запазите активната работна книга, използвайте кода по -долу:

Под Save SaveWorkbook () ActiveWorkbook.Save End Sub

Този код работи за работните книги, които вече са записани по -рано. Освен това, тъй като работната книга съдържа горния макрос, ако не е запазен като .xlsm (или .xls) файл, ще загубите макроса, когато го отворите следващия път.

Ако запазвате работната книга за първи път, тя ще ви покаже подкана, както е показано по -долу:

Когато записвате за първи път, по -добре е да използвате опцията „Saveas“.

Кодът по -долу ще запише активната работна книга като .xlsm файл в местоположението по подразбиране (което е папката с документи в моята система).

Под SaveWorkbook () ActiveWorkbook.SaveAs Име на файла: = "Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Ако искате файлът да бъде записан на определено място, трябва да го споменете в стойността Filename. Кодът по -долу записва файла на работния ми плот.

Sub SaveWorkbook () ActiveWorkbook.SaveAs Име на файла: = "C: \ Users \ sumit \ Desktop \ Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Ако искате потребителят да получи опцията да избере местоположението за запазване на файла, можете да използвате извикване на диалоговия прозорец Saveas. Кодът по -долу показва диалоговия прозорец Saveas и позволява на потребителя да избере мястото, където файлът трябва да бъде записан.

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.Save Next wb End Sub

Горното записва всички работни книги, включително тези, които никога не са били запазвани. Работните книги, които не са били запазени по -рано, ще бъдат запазени на местоположението по подразбиране.

Ако искате да запазите само тези работни книги, които вече са били записани, можете да използвате кода по -долу:

Sub SaveAllWorkbooks () Dim wb като работна книга за всяка wb в работни книги If wb.Path "" Тогава wb.Save End If Next wb End Sub

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

Ако искате да затворите всички работни книги, с изключение на работната книга, която има текущия код в нея, можете да използвате кода по -долу:

Sub CloseandSaveWorkbooks () Dim wb като работна книга за всяка wb в работни книги If wb.Name ThisWorkbook.Name Тогава wb.Close SaveChanges: = True End If Next 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, Cancel As Boolean) ThisWorkbook.SaveCopyAs Име на файла: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-yy-hh-mm-ss -AMPM ") &" .xlsm "End Sub

Горният код ще създаде копие всеки път, когато стартирате този макрос и ще добавите печат за дата/час към името на работната книга.

Създайте нова работна книга за всеки работен лист

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

Това може да се случи, когато имате месечни/тримесечни отчети в една работна книга и искате да ги разделите в една работна книга за всеки работен лист.

Или, ако имате разумни отчети на отделите и искате да ги разделите на отделни работни книги, за да можете да изпратите тези индивидуални работни книги на ръководителите на отдели.

Ето кода, който ще създаде работна книга за всеки работен лист, ще му даде същото име като това на работния лист и ще го запише в определената папка.

Sub CreateWorkbookforWorksheets () Dim ws като работен лист Dim wb като работна книга за всеки ws в ThisWorkbook.Worksheets Set wb = Workbooks.Add ws.Copy Before: = wb.Sheets (1) Application.DisplayAlerts = False wb.Sheets (2). 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 Като работна книга Set wb = Workbooks.Add wb.SaveAs Filename: = "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 If Next wb End Sub

Горният код използва цикъла За всеки, за да премине през всяка работна книга в колекцията Работни книги. За да направим това, първо трябва да декларираме „wb“ като променлива тип работна книга.

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

Същото може да се постигне и с различен цикъл, както е показано по -долу:

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 Ако Workbooks (i). Назовете ThisWorkbook.Name Тогава 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, можете да използвате кода по-долу, за да просто щракнете двукратно върху клетката с пътя на файла и тя ще отвори тази работна книга.

Private Sub Workbook_SheetBeforeDoubleClick (ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Workbooks.Open Target.Value End Sub

Този код ще бъде поставен в прозореца с кодове на тази работна книга.

Да го направя:

• Щракнете двукратно върху обекта ThisWorkbook в изследователя на проекта. Обърнете внимание, че обектът ThisWorkbook трябва да бъде в работната книга, където искате тази функционалност.
• Копирайте и поставете горния код.

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

Къде да поставите VBA кода

Чудите се къде отива кодът VBA във вашата работна книга на Excel?

Excel има VBA бекенд, наречен VBA редактор. Трябва да копирате и поставите кода в кодовия прозорец на модула VB Editor.

Ето стъпките за това:

1. Отидете в раздела Разработчик.
2. Щракнете върху опцията Visual Basic. Това ще отвори VB редактора в задната част.
3. В прозореца Project Explorer в редактора на VB щракнете с десния бутон върху всеки обект за работната книга, в който искате да вмъкнете кода. Ако не виждате Project Explorer, отидете в раздела View и щракнете върху Project Explorer.
4. Отидете на Вмъкване и щракнете върху Модул. Това ще вмъкне обект на модул за вашата работна книга.
5. Копирайте и поставете кода в прозореца на модула.

Може да харесате и следните уроци по Excel VBA:

• Как да запишете макрос в Excel.
• Създаване на потребителска функция в Excel.
• Как да създадете и използвате добавка в Excel.
• Как да възобновите макросите, като ги поставите в личната работна книга за макроси.
• Вземете списъка с имена на файлове от папка в Excel (със и без VBA).
• Как да използвате функцията Excel VBA InStr (с практически ПРИМЕРИ).
• Как да сортирате данни в Excel с помощта на VBA (Ръководство стъпка по стъпка).