Workbooks Collection in VBA – Workbook Object

Today we will learn a very useful topic which is to use workbooks with the help of VBA in Excel. Workbooks come with their methods and we will learn by taking examples. We will learn to use Workbooks Collection, Open, Close, and Save a Workbook along with other operations.

So let us start learning.

Workbooks Collection and Indexing

In VBA, we have a predefined collection for all the currently opened workbooks that are indexed. The first workbook has an index of 1 and it keeps on increasing as more workbooks are opened and automatically added to the Workbooks Collection. The Workbooks collection returns a reference to the Workbook object.

The workbooks in the Workbooks Collection are indexed based on the order in which they are opened.

For instance, this is how we can activate a workbook using the Workbooks Collection.

Workbooks(1).Activate

We can also replace the index with the name of the workbook.

Workbooks("Book1.xlsx").Activate

Get Path Where Workbook is Stored

We can get the path where the workbook is saved in our computer locally with the help of the Path property of the workbook.

Let us say we have opened a workbook named MyData.xlsx and we wish to print the path of the workbook using the message box.

Sub printPath()
'print the path of workbook
MsgBox Workbooks("MyData").Path
End Sub

Assign Workbook to a Variable

The Workbooks collection can be used to refer to different opened workbooks in Excel. Workbooks collection returns a reference to Workbook Object. Workbook Object is like a variable having its methods and properties.

We can declare a new workbook object using the Dim keyword and set a workbook to the Workbook object with the help of the Set keyword.

Sub AssignWorkbookToObject()
'declare a workbook
Dim myWorkbook As Workbook
'set the workbook to refer ot the last workbook of opened workbooks in Workbooks Collection
Set myWorkbook = Workbooks(Workbooks.Count)
'Workbooks.count returns the total number of workbooks in the collection
End Sub

You can see the list of methods and properties of the myWorkbook Object.

Open a Workbook using VBA

We can use the Open method of the Workbooks to open a specific workbook. This also requires the path of the workbook and the workbook name to open it.

For instance, we can open MyData.xlsm Workbook using the following code.

Sub openMyData()
'open a workbook using VBA in excel
Workbooks.Open "D:\Documents\Excel Unlocked\Excel Files Functions\macros\myData.xlsm"
End Sub

When you run the above Sub procedure, it opens the workbook myData.xlsm.

Open Using File Dialog Box

We can open a file with the help of the File Dialog box in Excel. You can use the Application.GetOpenFileName() to open the File Dialog Box. Application.GetOpenFileName method returns a text string containing the complete path and filename of the selected file. We can use the returned text string to open the file using the Open method of the Workbooks collection.

You can use the following Sub procedure to open the file selected using the Windows file dialog box in Excel.

Sub openUsingDialogBox()
'a text string to store the string returned by getOpenFilename()
Dim filename As String
'open the windows filename dialog box and assign the returned value to filename
filename = Application.GetOpenFilename()
'print the filename
MsgBox filename
'open the file selected in open dialog box
Dim ws As Workbook
Set ws = Workbooks.Open(filename)
'activate the opened workbook
ws.Activate
End Sub

Here ws is a workbook object that has been set to the opened workbook using the Set keyword.

Get a List Of All Opened Workbooks using VBA

We can print the list of all the open workbooks. The Workbooks Collection contains all the workbooks that are presently open. We can loop through the Workbooks collection with the help of For loop and print their names in a range of cells.

You can use the following Sub Procedure to print the list of all opened workbooks in a new workbook.

Sub getWorkbooksNames()
Dim totalWorkbooks As Integer
'get the total number of workbooks that are open
totalWorkbooks = Workbooks.Count
'take a new workbook to print the names of opened workbooks in it
Workbooks.Add
'activate cell a1 of new workbook's worksheet
ActiveSheet.Range("A1").Activate
'using for each loop
For i = 1 To totalWorkbooks
'accessing the names using workbooks collection and then print them in first column.
Cells(i, 1).Value = Workbooks(i).Name
Cells(i, 1).Interior.Color = RGB(249, 185, 195)
Next i
Range("A1").EntireColumn.AutoFit
End Sub

As a result, a new workbook is created showing the list of all opened workbooks in column A.

Save and Close All Opened Workbooks in Excel

We can use VBA to first Save all the workbooks and then close them. We need to place this sub-procedure that first saves and then closes all the opened workbooks in the new Module in PERSONAL.XLSB project in VBE. This Sub Procedure contains a condition that makes sure that the PERSONAL.XLSB file is not closed and only saved so that the macro works and is not terminated in between because the workbook is closed.

Place the following sub-procedure in PERSONAL.XLSB file to work.

Sub CloseAndSaveAll()
'this sub procedure should be placed in a module in personal.xlsb file to work properly
Dim wb As Workbook
'loop through all opened workbooks in collection
For Each wb In Workbooks
'do not close the PERSONAL.XLSB workbook
If wb.Name = "PERSONAL.XLSB" Then
'save and do not close
wb.Save
Else
'close and save changes for all other workbooks except personal.xlsb
wb.Close savechanges:=True
End If
Next wb
End Sub

When you run this Sub Procedure, it will save the changes for the workbooks that were already saved on your PC. For a workbook that has been newly created, the Save As Dialog box will open.

We can use the Close method to save the ActiveWorkbook. This works for a single Workbook in VBA. Here SaveChanges argument is set to TRUE, if we set the SaveChanges argument to FALSE then the workbooks will be closed without saving the changes.

This brings us to an end.

Thank you for reading.❤