Excel Macro Class: Workbook Object

The Workbook object represents a single Excel workbook within the Workbooks collection, which comprises all open Workbook objects. Refer to the Workbook object to open/save/close existing Excel files, create new files, delete, etc. Refer to “ThisWorkbook” events to run a macro when the file opens or closes, before save, etc.

How to refer to the Workbook object

ThisWorkbook ‘the workbook where the macro resides

ActiveWorkbook ‘the active workbook.

Workbooks(1)

Workbooks("file.xlsx")

Workbooks "file.xlsx"

For a file in the local drive (Windows OS path example):

Workbooks "C:\Users\Username\Downloads\file.xlsx"

Dim srcFile As String

srcFile = "C:\Users\Username\Downloads\file.xlsx"

Workbooks (srcFile)

In a shared drive the file path may vary (it usually starts with “\\” and uses backwards slashes). For a file in the intranet/internet the path is different and could start like this: srcFile=”http://..... “

Activate, Count, Open, Save, and Close workbooks

A workbook gets activated (or active) when it opens. Any opened workbook can also be activated with VBA code in either of these 2 ways; the third line counts the number of opened workbooks.

Workbooks(2).Activate 'activates the second open workbook

Workbooks("Book1.xlsx").Activate 'activates Book1 (no need to specify the path)

Workbooks.Count 'counts the number of workbooks open

To open a workbook we need to specify the whole path and the file name with extension. That can be put into a variable (e.g. myFile) for easier code handling. The Workbooks.Open method also allows to enable/disable updating links, or input the password for a protected workbook.

Workbooks.Open "C:\Users\Username\Downloads\file.xlsx"

Workbooks.Open ("C:\Users\Username\Downloads\file.xlsx")

Dim myFile As String

myFile = "C:\Users\Username\Downloads\file.xlsx"

Workbooks.Open myFile

Workbooks.Open (myFile)

Workbooks.Open Filename:=myFile, UpdateLinks:=False, Password:="1234"

It is good practice to check if the workbook exists before opening. Furthermore, it is advisable to check whether the workbook is already open in order to avoid errors. Find the code to check both conditions and safely open a workbook in this post.

In order to save or close a workbook, it needs to be activated first (if not already active). The following code example is saving or closing the active workbook.

ActiveWorkbook.Save

ActiveWorkbook.Close

ActiveWorkbook.Close SaveChanges:=True

It is common practice to use object variables when dealing with workbooks, thus allowing to easily refer to each of them without worrying if they are active or not. The following code declares an object variable (myWB), and then opens and sets a workbook to that variable so that it can be used to apply other methods/properties later (save and close properties in the example below).

Dim myWB As Workbook

Set myWB = Workbooks.Open (myFile)

myWB.Save

myWB.Close

In other example, we can see how to open a workbook located in the same folder using object variables (wb1, wb2) for each of the two workbooks referenced.

Dim wb1 As Workbook, wb2 As Workbook

Dim myPath As String, myFileName As String, myFile As String

Set wb1 = ThisWorkbook

myPath = wb1.Path

myFileName = "/Book1.xlsx"

myFile = myPath & myFileName

Workbooks.Open myFile

Set wb2 = ActiveWorkbook

Create (Add), Save, and Delete workbooks

We use Add to create or add a new workbook. Then, Excel automatically gives a default name (usually Book1.xlsx, Book2.xlsx, etc). When saving, it will be saved in the default folder (in Windows OS is usually the Documents folder) unless a different path is specified with SaveAs.

Workbooks.Add

ActiveWorkbook.Save

ActiveWorkbook.SaveAs "C:\User\Username\Documents\ExcelFiles\newfile.xlsx"

We can also declare and define a variable to hold the name of the workbook we want to save including the path name. In the same way, we can also save a protected workbook specifying the password.

Dim myFile As String

myFile = "C:\Users\Username\Downloads\file.xlsx"

ActiveWorkbook.SaveAs myFile

ActiveWorkbook.SaveAs Filename:=myFile, Password:="1234"

We delete a workbook that is active with the Delete method. To delete a workbook that is not active or any other type of file we can use the Kill method. However, if the file is open or does not exist it will prompt an error message.

ActiveWorkbook.Delete

Kill "C:\Users\Username\Documents\file.xlsx"

We can directly set the newly created/added workbook to an object variable and then refer to that variable to save, close, etc, as follows:

Dim myWB As Workbook

Set myWB = Workbooks.Add

myWB.SaveAs "C:\newfile.xlsx"

myWB.Close

Workbook Properties

There are some properties that may come handy when coding with workbooks in order to set/get some basic or specific information about the workbook. The code below sets the name of the active workbook (without extension) and path to the corresponding variables:

Dim myWB As String, myPath As String

myWB = ActiveWorkbook.Name

myPath = ActiveWorkbook.Path

We can also get or set the password to protect a workbook. In the example below, we set the password to whatever the user inputs in a dialog box.

ActiveWorkbook.Password = InputBox("Enter Password")

We can decide the way a workbook handles links to other files with the UpdateLinks property, which can be set to either of the three options shown below.

Workbook Events

Below are examples of event procedures for some of the most used workbook events in Excel VBA. The first example displays a welcoming dialog box when the workbook opens.

Private Sub Workbook_Open()

MsgBox "Welcome Back"

End Sub

This other example saves the workbook before it closes. Cancel is False by default, setting it to True will prevent from closing.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

ActiveWorkbook.Save

End Sub

Similarly, we can run some code just before saving the workbook. We can prevent from saving by setting Cancel to True. SaveAsUI can be set to True in order to display the saving dialog box.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'set of tasks to do before saving

End Sub

The following code will trigger when changing the active worksheet in the workbook. Sh is an object variable that stores the active sheet. In this example, we display the name of that sheet in a dialog box.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

MsgBox Sh.Name

End Sub

Similarly, this will run when adding a new worksheet in the workbook, and that worksheet is automatically stored in the object variable Sh.

Private Sub Workbook_NewSheet(ByVal Sh As Object)

'set of tasks when adding a new worksheet

End Sub

These and other workbook event procedures can be found in the visual basic editor when selecting the object ‘Thisworkbook’ in the VBA project navigation on the left and then Workbook in the drop-down on the right.