VBA Workbook

VBA Workbook is an object which is part of the Workbooks object collection. We easily see the difference between the part of object collection and the object itself. The plural word of "Workbooks" refers to many "Workbooks." In VBA, we have several other objects like Worksheets, Cells & Ranges, Charts, and Shapes.

In the Excel platform, an Excel file is called a "Workbook, "especially in VBA. However, we never call it a file; rather, we call it a "Workbook."

By referring to the workbook, we can do all the related tasks. Some of the important tasks are "Open Workbook," "Save Workbook," "Save As Workbook," and "Close Workbook." In addition, we can select and activate the open workbooks.

Look at what is the syntax of the Workbook.

An index is nothing but which workbook you want to select. We can refer to the workbook by workbook number or by workbook name.

Example #1

We have two files open right now. The first workbook name is "File 1," and the second workbook name is "File 2."

Now, we are writing the code in the third file. But, first, we want to activate the workbook named "File 1" from this file.

Step 1: Start the macro by creating a VBA sub procedure.

Code:

Sub Workbook_Example1()

End Sub

Step 2: Now, select the workbook object.

Step 3: Now, enter the workbook that we want to activate.

Code:

Sub Workbook_Example1()

  Workbooks("File 1

End Sub

Step 4: After entering the workbook name, we must also enter the file extension. We have saved this workbook as a regular workbook, i.e., the "xlsx" workbook.

Code:

Sub Workbook_Example1()

  Workbooks ("File 1.xlsx")

End Sub

Step 5: We must decide what we want to do with this workbook. Enter the dot to see all the options available with this workbook.

Step 6: We need to activate the workbook and select the method as "Activate."

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").Activate

End Sub

It doesn't matter which workbook you are in. It will activate the specified workbook.

When you select the workbook, it becomes an "Active Workbook."

Example #2 - Enter Values in the Workbook

As we told you, as soon as you select the workbook, it becomes an active workbook. Using an active workbook, we can reference the cell.

In the active workbook, we need to select the sheet by its name or use the word "Active Sheet."

We must select the cell in the active worksheet using the Range object.

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").Activate
  ActiveWorkbook.ActiveSheet.Range("A1").Value = "Hello"

End Sub

When you run this code using the F5 key or manually, it will insert the word "Hello" in cell A1 in the workbook "File 1.xlsx."

We can also use the below code to do the same job.

Code:

Sub Workbook_Example1()

  Workbooks("File 1.xlsx").ActiveSheet.Range("A1").Value = "Hello"

End Sub

It will also insert the word "Hello" to the workbook "File 1.xlsx."

Example #3 - Assign Workbook to Variable

We can also assign the data type as a "workbook" to the declared variable. Declare the variable as Workbook.

Code:

Sub Workbook_Example2()

  Dim WB As Workbook

End Sub

We need to set the object variable to the workbook name using the word “Set.”

Code:

Sub Workbook_Example2()

 Dim WB As Workbook

 Set WB = Workbooks("File 1.xlsx")

End Sub

From now onwards, the variable "WB" holds the name of the workbook "File 1.xlsx".

Using the variable name, we can insert the words.

Code:

Sub Workbook_Example2()

 Dim WB As Workbook

 Set WB = Workbooks("File 1.xlsx")

 WB.Worksheets("Sheet1").Range("A1") = "Hello"
 WB.Worksheets("Sheet1").Range("B1") = "Good"
 WB.Worksheets("Sheet1").Range("C1") = "Morning"

End Sub

Run this code manually or use shortcut key F5. See the result, as shown in the below screenshot.

WB.Worksheets("Sheet1").Range("A1") = "Hello"

Here, WB is referencing the workbook. In that workbook, we reference the worksheet Sheet1 by using the Worksheets object. In that worksheet cell, A1 is equal to the value of "Hello."

We can also reference the workbook by index number. For example, look at the below code.

Code:

Sub Workbook_Example3()

Workbooks(1).Activate
Workbooks(2).Activate
Workbooks(3).Activate

End Sub

Workbooks (1) means whichever workbook is first on the list. Similarly, Workbooks (2) refers to the second workbook, and Workbooks (3) refers to the third workbook.

The main problem with this index number referencing is we don't know which workbook is activated. Therefore, it is dangerous to use index numbers.

Example #4 - For Each Loop for Workbook Object

As we said in the beginning, the workbook is a collection object of Workbooks in VBA. Therefore, whenever we want to perform the same activity for all the opened workbooks, we need to use For Each loop in VBA.

For Each Loop is the loop for all the objects in VBA. Use the below code to save all the opened workbooks.

Code:

Sub Save_All_Workbooks()

    Dim WB As Workbook

   For Each WB In Workbooks
   WB.Save
   Next WB

End Sub

When you run this code through the F5 key or manually, a pop-up asks to save the workbook. Click on "OK" to save.

Use the below code to close all workbooks except the one you are working on.

Code:

Sub Close_All_Workbooks()

Dim WB As Workbook

For Each WB In Workbooks
If WB.Name <> ThisWorkbook.Name Then
WB.Close
End If
Next WB

End Sub

A pop-up window comes before closing the workbook.