Excel Made Easy

VBA workbook object in Excel

The "WorkBooks" is an important object used for working with Excel Workbook.

The name is self describing and is most often used object in large programs.

The "WorkBooks" has a number of methods, some of which are discussed below.

Methods of WorkBooks object

a) Add

This method is used to create a new Workbook. This method just creates a new unnamed workbook.

The following code illustrates this

Sub workbook_add()
Dim wb As Workbook
Set wb = Workbooks.Add
End Sub

The "Add" method creates a new Workbook.

The "Set" keyword is used to assign the workbook to an object (wb), so that it will be available for access throughout the program

b) Open

This method is used to open an existing workbook.

This method should be supplied wih the full path along with the file name with extension(.xlsx,.xlsm,.xls)

The following code opens a workbook named "test.xlsx".

Sub workbook_demo()
Dim wb As Workbook
Set wb = Workbooks.Open("D:\test.xlsx")
'Perform some modifications
wb.Save
wb.Close
End Sub

c)Save

This method is used to save the workbook

This method is usually used with already existing workbook in which some changes are done and has to be saved.

The example above shows the use of "Save', which saves the workbook, "test.xlsx" after some modifications.

d) SaveAs

This Method is Similar to Save except that this saves the workbook with new name or in new location.

This method is useful when creating a new workbook which must be saved.

This method is also used when there is a need to save an already existing workbook with different name.

The following example illustrates this

Sub workbook_saveAs()
Dim wb As Workbook
Set wb = Workbooks.Add
wb.SaveAs ("D:\MyNewWorkBook.xlsx")
End Sub

e) Close

This method is used to close a workbook which is open.

This method has optional parameters, in which "SaveChanges" is often used.

The "SaveChanges" takes a Boolean value(True/False), which specifies whether the changes to the workbook has to be saved or not.

The following example illustrates this

Sub workbook_demo()
Dim wb As Workbook
Set wb = Workbooks.Open("D:\test.xlsx")
'Perform some modifications
wb.Save
wb.Close (True)

End Sub

You can find similar Excel Questions and Answer hereunder

1) What are the main cell objects in VBA

2) How can I save a WorkSheet as a new WorkBook using VBA?

3) How to avoid screen update in Excel VBA. Various application objects explained

4) How to add email and emailing function in Excel VBA

5) what are the main range object and what can the range objects do in VBA

6) How can I quickly navigate to a frequently used field in a voluminous worbook?

7) What are objects and what are properties in Excel VBA

8) What is a methods. Methods are action that can be performed by an object

9) I have a WorkBook that loads a form automatically when it is opened. In Excel, how can I suppress the form from loading on file open when required?

10) How do I disable the right click option for users in my WorkBook?

11) Excel Workbook protection : how to lock and protect my Excel Workbook.