VBA Working with Workbooks (The Workbook Object)
In this Article
- The Workbook Object
- Activate Workbook, ActiveWorkbook, and ThisWorkbook
- Open Workbook
- Create New (Add) Workbook
- Close Workbook
- Workbook Save As
- Other Workbook VBA Examples
This guide will introduce you working with the Workbook Object in VBA.
The Workbook Object
First, in order to interact with workbooks in VBA, you must understand the Workbook Object.
With the workbook object, you can reference workbooks by their name like this:
However, this code will only work if the workbook is open. If the workbook is closed, you will need to provide the full workbook path:
Instead of typing out the full path, if your desired workbook is in the same directory as the workbook where your code is stored, you could use this line code to open the workbook:
Workbooks.Open (ThisWorkbook.Path & "\book2.xlsm")
This makes use of the ThisWorkbook object that we will discuss in the next section.
Workbook Index Number
Last, you can reference workbooks by their “Index Number”. The index number of a workbook corresponds to the order that the workbook was opened (technically its the workbook’s position in the Workbooks Collection).
This is useful if you want to do something like close the first (or last) opened workbook.
Activate Workbook, ActiveWorkbook, and ThisWorkbook
If a workbook is NOT ACTIVE, you can access the Workbook’s objects like this:
Workbooks("Book2.xlsm").Sheets("Sheet1").Range("A1").value = 1
However, if the workbook is Active, you can omit the workbook object:
Sheets("Sheet1").Range("A1").value = 1
And if you want to interact with the workbook’s active sheet, you can also ommit the sheets object:
Range("A1").value = 1
To activate a workbook, use the Activate Method.
Now you can interact with Book2’s object’s without explicitly stating the workbook name.
The ActiveWorkbook object always refer to the active workbook. This is useful if you’d like to assign the ActiveWorkbook to a variable to use later.
Dim wb As Workbook Set wb = ActiveWorkbook
The ThisWorkbook object always refers to the workbook where the running code is stored. To activate ThisWorkbook, use this line of code:
To open a workbook, use the Open Method:
The newly opened workbook will always become the ActiveWorkbook, allowing you to easily interact with it.
The Open Method has several other arguments, allowing you to open read-only, open a password-protected workbook, and more. It’s covered here in our article about Opening / Closing Workbooks.
Open and Assign to Variable
You can also open a workbook and assign it to a variable at the same time:
Dim wb As Workbook Set wb = Workbooks.Open("C:\Users\StevePC2\Downloads\book2.xlsm")
Open File Dialog
You can also trigger the Open File Dialog Box like this:
Sub OpenWorkbook () Dim strFile As String strFile = Application.GetOpenFilename() Workbooks.Open (strFile) End Sub
Create New (Add) Workbook
This line of code will create a new workbook:
The new workbook now becomes the ActiveWorkbook, allowing you to interact with it (ex. save the new workbook).
Add New Workbook to Variable
You can also add a new workbook directly to a variable:
Dim wb As Workbook Set wb = Workbooks.Add
Close & Save
To close a workbook with saving, use the Close Method with SaveChanges set to TRUE:
Close without Save
To close without saving, set SaveChanges equal to FALSE:
Workbook Save As
The SaveAs Method is used to save a workbook as.
To save a workbook with a new name, in the same directory, you can imply use this:
where “new” is the new file name.
To save a workbook in a new directory with a specific file extension, simply specify the new directory and file name:
Other Workbook VBA Examples
To get the name of a workbook:
To protect the workbook structure from editing, you can use the Protect Method (password optional):
To unprotect a workbook use the UnProtect Method:
Loop Through all Open Workbooks
To loop through all open workbooks:
Sub LoopThroughWBs() Dim wb As Workbook For Each wb In Workbooks MsgBox wb.Name Next wb End Sub
Workbook Activate Event
You can run some code whenever a specific workbook is opened with the Workbook Open Event.
Place this procedure your workbook’s ThisWorkbook Module:
Private Sub Workbook_Open() Sheets("sheet1").Activate End Sub
This procedure will activate Sheet1 every time the workbook is opened.