This page allows you to understand the Workbook object and the Workbooks collection in VBA macro programming.
If you use ThisWorkbook property of the Application object, the object return is the workbook where the Visual Basic code is running. Most of the time, this is the same as the active workbook.
There are a lot of methods, properties and events attached to the workbook object. The workbook object is, in fact, an element of the workbooks collection.
Below, several useful codes around the workbooks collxection and workbook object. You will find the main tools for handling workbooks. Knowing the object means being able to automatically create workbooks, save them, open them, protect them, delete them.
Create a new workbook
The first thing to know about workbook is how can you create a new workbook using a VBA macro.
To create a workbook, we begin by declaring new objects as binders. Then, we can use the Add method of the Workbooks collection and finally use the Set tool to match this new workbook to our declared variable.
The following procedure will show you how to create a new workbook, and how can you copy a sheet from a workbook to another using the Copy method of the sheet object.
In order not to see the execution of your screen, do not forget the good practices by deactivating the setting of your screen.
Sub CreateNewWorkbook() 'Deactivated Application.AskToUpdateLinks = False Application.ScreenUpdating = False Application.DisplayAlerts = False Dim WbMacro, WbNew As Workbook Set WbMacro = ActiveWorkbook Workbooks.Add 'Create a new workbook Set WbNew = ActiveWorkbook 'Copy Paste the tab Weather Data to the new wbk WbMacro.Sheets("Weather Data").Copy after:=WbNew.Sheets(1) WbNew.Sheets(1).Delete 'Remove the Sheet1 'Activated Application.AskToUpdateLinks = True Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
Open an existing workbook
The main method to know about the workbook are how you can create, open and save. To open a workbook you just have to use the Open method with the path of the file as an argument.
In the example below, we write all the path of the file to open it :
Sub OpenWorkbook() Dim FilePath As String FilePath = "C:\Desktop\VBASKILLS\Startup.xls" Workbooks.Open (FilePath) End Sub
In the example below, we use the GetOpenFilename method to select a file. This reduces the risks of the bug code because the user will choose his file in the system. This procedure works for Mac Os and Windows users.
Sub OpenWorkbook() Dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub
As seen in the article Project Explorer, you can also use the Application.FileDialog(msoFileDialogOpen) object to choose a file in the system, but it will work only for windows users.
Sub OpenWorkbook() 'works only for windows Dim FilePath As String With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False ' To select only one file .Show 'Display the dialog box 'Write the path of the file selected on B2 FilePath = .SelectedItems(1) End With Workbooks.Open (FilePath) End Sub
A good practice is to let the users select the file needed before the launching of the macro. You can use for example the macro design template “Starter VBA Macro” available in the store.
Open a text file as workbook
It is also possible to open a “.txt” file using the OpenText method if your data to analyse are in a text file.
Sub OpenWorkbookTxt() Workbooks.OpenText filename:="Thesaurus.txt", dataType:=xlDelimited, tab:=True End Sub
Loop on workbooks
To loop on all workbooks open you just have to use a For Each loop on the Workbooks collection.
Sub LoopWorkbooks() 'Loop on all workbooks Dim Wbki As Workbook 'The counter For Each Wbki In Workbooks '... your instructions ... Next Wbki End Sub
Save a workbook for the first time
After your macro is created a workbook, retrieved data reworked this data for your analyzes and reports, your need will be to save your work for the first time. For this, use the SaveAs method.
In the arguments of the method, you can even protect your workbook with a password.
Below the list of arguments available : FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local.
You can use all Excel type of file format such as xlAddIn (*.xla), xlCSV (*.csv), xlCSVMac (*.csv), xlOpenXMLWorkbook (*.xlsx), xlOpenXMLWorkbookMacroEnabled (*.xlsm), xlTextMac (*.txt),xlTextMSDOS (*.txt), xlXMLSpreadsheet (*.xml)…
Sub SaveWorkbook() 'Save a workbook Dim WbkAnalyis As Workbook 'The counter Set WbkAnalyis = ActiveWorkbook WbkAnalyis.SaveAs Filename:="Finance.xlsm" FileFormat:=xlOpenXMLWorkbookMacroEnabled End Sub
Save changes of a workbook
To save the change of workbook already in the computer you can just use the Save method.
Sub SaveChangesWorkbook() 'Save the changes of a workbook Dim WbkAnalyis As Workbook 'The counter Set WbkAnalyis = ActiveWorkbook WbkAnalyis.Save End Sub
Save all changes of workbooks
To save the change of several workbooks already open in the computer use the Save method with a For Each loop.
Sub SaveAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks wb.Save Next wb End Sub
Save all workbooks and check the ReadOnly status
The code below uses a loop For Each….Next on open Excel workbooks and checks the status of read-only. If the file is not read-only, then it saves the workbook. When the workbook is read-only, you can’t do any changes. To override the read-only, simply re-save the workbook in a different location, thus creating a copy.
Sub SaveWorkbookNotReadOnly() 'Save all workbooks which are not in read-only Dim Wbk As Workbook 'Loop on all workbooks For Each Wbk In Application.Workbooks If Not Wbk.ReadOnly Then ' Condition on Read Only Wbk.Save 'Save the workbook End If Next Wbk End Sub
Close the workbook and choose to save or not
To close a workbook, already save in a path location of your compute, nothing easier, use the Close method. As argument, the close method can save the workbook if needed using the boolean value True on the parameter SaveChanges. If you are working on a VBA code which needs to open other Excel workbooks to retrieve data, if you do not change anything in the Excel workbook opened previously, then it is better to close this workbook without saving it, so you will save execution time especially if the latter is a big file.
As seen in good practices, it is also necessary to deactivate Excel alerts, doing that your code is not interrupted by a message indicating if you are sure that you do not want to save your workbook.
Sub CloseWorkbookWithoutSave() 'Close all workbooks without save Application.DisplayAlerts = False Dim WbNew As Workbook Workbooks.Add Set WbNew = ActiveWorkbook WbNew.Close SaveChanges:=False 'The False cancelled the saving Application.DisplayAlerts = True End Sub
Protect & unprotect the workbook
The read-only file restricts changes being made, but this can easily be counteracted by saving it again. The best security method is to use a password on a workbook with the VBA Protect method. As the Excel sheet, you can use a password to protect all the workbook.
Sub ProtectWorkbook() 'Lock the workbook with a password Dim WbkMacro As Workbook Set WbkMacro = ActiveWorkbook WbkMacro.Protect Password:="azerty123", _ Structure:=True, Windows:=True End Sub
To unprotect the file, use the method Unprotect and the password.
Sub UnprotectWorkbook() 'UnLock the workbook with a password Dim WbkMacro As Workbook Set WbkMacro = ActiveWorkbook WbkMacro.UnProtect Password:="azerty123" End Sub
Protect all workbooks
To protect all workbooks, we just need to add a For Each… Next loop in the procedure above.
Sub ProtectAllWorkbooks() 'Lock all workbooks with a password Dim Wbki As Workbook For Each Wbki In Workbooks Wbki.Protect Password:="azerty123", _ Structure:=True, Windows:=True Next Wbki End Sub
Test if the workbook has a password
When you work on a workbook, it can be useful to test if the workbook is protected or not by a password using the property HasPassword of the object.
Sub CheckIfPasswordOnWorkbook() 'Check if the workbook has a password Dim WbkMacro As Workbook Set WbkMacro = ActiveWorkbook If WbkMacro.HasPassword = True Then MsgBox "The Workbook is protected by a password." End If End Sub
Print the workbook
To print the entire workbook, use the PrintOut method. This method take has arguments the following values : From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFilename, IgnorePrintAreas.
Sub PrintWorkbook() 'Print all the workbook Dim Wbki As Workbook 'The counter Set Wbki = ActiveWorkbook Wbki.PrintOut End Sub
Divided each sheet in a new workbook
Based on filters, sometimes you need to divide your work with one sheet by workbook for the analysis required. It can be due to a perimeter, a location, an activity sector or other criteria. So after having made your analysis, you can split the file into several files.
Sub CreateOneWorkbookforOneSheet() Dim Sh As Sheet Dim Wbk As Workbook 'Loop on all sheets in your workbook using ThiWorkbook property For Each Sh In ThisWorkbook.Worksheets Set Wbk = Workbooks.Add '... others instructions ... Sh.Copy Before:= Wbk.Sheets(1) 'Copy your data Wbk.Sheets(1).Delete 'Delete a sheet Wbk.SaveAs "C:\Users\Desktop\Accounting\" & Sh.Name & ".xlsx" 'Save Wbk.Close 'Close the workbook already saved Next Sh End Sub
Launch automatically a procedure with workbook action
Some specific macros can launch automatically depending on the actions. These are called events. For example, the instructions written in a macro having the name App_WorkbookOpen will automatically launch the instructions of the procedure when the file is opened. For more details, see the Automatic Sub article in the Tips category.