Workbooks

In this chapter you will learn about using Workbook object by using VBA.

Table of Contents

How to Open a workbook?

To open a workbook, we need to use Open property of Workbooks object. Below given code to be used in the module to open a workbook. Change the path and name of your workbook accordingly.

Sub Open_Workbook()
'Change your path and filename
Workbooks.Open Filename:="C:\Users\UserName\Desktop\Sample Data.xlsx"   

End Sub

If you want to open the workbook as read-only then put Readonly:=True.

Sub Open_Workbook_AS_Readonly()
'Change your path and filename
Workbooks.Open Filename:="C:\Users\UserName\Desktop\Sample Data.xlsx", ReadOnly:=True

End Sub

How to Save As a workbook?

If you want to save as a workbook, then SaveAs property to be used. Below is the example of workbook save as.

Sub Workbook_SaveAs()

ThisWorkbook.SaveAs Filename:="C:\Users\UserName\Desktop\Sample Data.xlsx"

End Sub

How to Save a workbook?

To save a workbook Save property is used. Below given code to be used in the module.

Sub Workbook_Save()

ThisWorkbook.Save

End Sub

How to Close a workbook?

If you want to close a workbook, then Close property of workbooks is used. Below is the example.

Sub Workbook_Close()

Workbooks("Sample Data.xlsx").Close shavechanges:=True

End Sub

Shavechanges are used to save the workbook before close. If you put shavechanges=false then workbook will be closed without saving.

How to create a new workbook?

If you want to create a workbook (like Ctrl+N in excel) then Add property of workbooks is used.

Sub Workbook_Create()

Workbooks.Add

End Sub

A New workbook will be created with name of Book1, Book2 or any other Book.

How to Protect/Unprotect a workbook?

A workbook can be protected by using below given code. After protecting a workbook, worksheets of that workbook cannot be renamed, deleted, hidden etc.

Below is the code to protect and unprotect an open workbook.

Sub Protect_Workbook()

Workbooks("Book1").Protect Password:="1234"

End Sub

Sub UnProtect_Workbook()

Workbooks("Book1").Unprotect Password:="1234"

End Sub

Difference between Thisworkbook and Activeworkbook

ThisWorkBook means the workbook in which we are writing the VBA Code. ActiveWorkBook means the workbook which active currently.

Most of the time, both are same workbook, but if the macro workbook (workbook in which VBA code is available) is not the active workbook then they will point to different workbooks.

Workbook Events

Workbook events are actions or occurrences associated with the workbook, to trigger a VBA code or macro. We can put our code to run to a workbooks event like workbooks open, before close, Activate, Deactivate etc.

To fire a workbook event, open the Visual Basic Window by pressing alt+F11
Double click on Thisworkbook
Select the Workbook in left drop down box in place of General
Now you can select the workbook event from right drop-down box as given in below image.

Workbooks Even list — Workbooks Events list

For example, we want to put welcome message while opening a workbook and goodbye message on closing the workbook

'Code for workbook open

Private Sub Workbook_Open()

    MsgBox "Welcome"

End Sub

'Code for workbook close

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox "Goodbye"

End Sub