Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Last Updated: Mar 27, 2024
Difficulty: Easy

Workbook and Worksheet Object

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

In today’s modern world, data has become one of the most crucial things for individuals. All of the applications or websites you use must take your data. Because they are large amounts of data, they are saved on their separate servers.

However, if a person wishes to learn how to manage data in the form of tables, they can do it in Excel. Excel is a powerful tool or software that we have at our disposal. It may be used to create tables, calculations, sheets, etc.

We work with worksheets and workbooks Object in Excel. Don't be worried if they are unfamiliar terms to you. We will help you properly understand it, and after reading this blog, you will be able to deal effectively with them.

Objects in Worksheet

An object in Excel VBA can contain another object, and that object can contain another object, and so on. In other words, working with an object hierarchy is a part of Excel VBA programming. This may appear to be rather difficult, but we shall understand.

Excel is the mother of all objects. It's referred to as the Application object. Other items are contained within the application object. The Workbook object, for example (Excel file). This might be any worksheet you've made. Other objects, such as the Worksheet object, are contained within the Workbook object. Other objects, such as the Range object, are contained within the Worksheet object.

Workbooks and Worksheets are both plurals, as you may have noticed. This is because they are collections. The Workbooks collection holds all currently open Workbook objects. All Worksheet objects in a workbook are contained in the Worksheets collection.

 

We have three ways to access any member of the collection. They are discussed below.

 

The first one is, using the Worksheet name.

Worksheets("Cars").Range("A1").Value = "Audi"

 

The second is, using the index number(indexing is done from the left side, i.e., 1)

Worksheets(1).Range("A1").Value = "Audi"

Lastly, using the CodeName of the worksheet.

Sheet1.Range("A1").Value = "Audi"

Open the Visual Basic Editor to see the CodeName of a worksheet. The first name in the Project Explorer is the CodeName. The second name is the name of the worksheet (Cars).

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Path and FullName in VBA

In Excel VBA, the Path property delivers the complete, stored path to the workbook (Excel file). At the same time, the FullName property returns the complete, stored path, including the name of the workbook.

By Downloading the path-fullname.xlsm file and pasting it at the "C:\test\" directory and performing the following code operation,

The code below returns the whole path to path-fullname.xlsm.

MsgBox Workbooks("path-fullname.xlsm").Path

Result

The code below retrieves the full path, including the name of the active workbook.

MsgBox ActiveWorkbook.FullName

Result


 

I hope you have got a fair idea about Path and FullName. Now moving to discuss the Close and Open method in VBA WorkBook.

Open and Close method

To close and open workbooks, use the Close and Open Method in Excel VBA. Remember that the Workbooks collection contains all currently open Workbook objects.

To close the openFile.xlsm, use the following lines of code.

Workbooks("openFile.xlsm").Close

The code below closes the currently active workbook.

ActiveWorkbook.Close

The code line below closes all currently open workbooks.

Workbooks.Close

To open a file named Cars.xlsx, use the following lines of code

Workbooks.Open ("Cars.xlsx")

If Cars.xlsx is stored in your default file location, you can open it without specifying the file's path. On the File tab, click Options and Save to change the default file location.

You may alternatively use the Application object's GetOpenFilename function to display the regular Open dialogue box.

Dim MyFile As String
MyFile = Application.GetOpenFilename()

Which will open the dialogue box,

Next, you can open the file in the old school way

Workbooks.Open (My_File)

I hope the discussion on the Open and Close method is clear to you. So, moving to our next part of the discussion.

Looping through Books and sheets

In the following section, we will look at an Excel VBA program that loops through all open workbooks and worksheets.

There are two ways to loop through all of the sheets in a workbook.

  1. For Each Loop
  2. For Next Loop

First, let's see the ForEach loop,

ForEach Loop

  • First, declare a variable that will be used to refer to a worksheet during the loop (wsheet). 
  • Then, begin the loop with the term "For Each," referring to each worksheet in the workbook. 
  • If you wish to input a value in cell A1 of each worksheet, you can use the following code.
Sub vba_loop_forEach()
Dim wsheet As Worksheet
For Each wsheet In ThisWorkbook.Worksheets
   wssheet.Range("A1").Value = "Hello"
Next wsheet
End Sub

ForNext Loop

  • First, you must declare two variables to keep the count value for the loop and the count of the sheets in the workbook. 
  • Set the value of the "sheetCount" variable to the number of sheets in the workbook. 
  • Using the "For i" keyword, begin the code for the loop and use the sheet count of the maximum value as the loop counter.
  •  Use the loop counter to loop through all of the sheets and put the value "Hello" in cell A1 of each sheet.
Sub vba_loop_sheets()
Dim i As Long
Dim sheetCount As Long
sheetCount = Sheets.Count
For i = 1 To sheetCount
   Sheets(i).Range("A1").Value = "Hello"
Next i
End Sub

Now we have completed our discussion on looping through books and Sheets, and I hope you have got a fair understanding of the concept. Now moving to the next part of our discussion.

Working with Files in a Directory

In the following section, we will look at an Excel VBA program that loops through all the closed workbooks and worksheets in a directory and shows all of their names.

Suppose we have added A.xlsx, B.xlsx, C.xlsx, D.xlsx, E.xlsx in the directory "C:\test\"

Initially, our worksheet looked like this,

 

Now let's write the code to loop through all closed workbooks and worksheets in a directory and display all the names.

First, we declare two String variables, a Worksheet object, and two Integer variables.

Dim direc As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

To avoid screen flickering, turn off screen updating.

Application.ScreenUpdating = False

Initialize the variable directory. The Dir function is used to locate the first *.xl?? file in this directory

direc = "c:\test\"
fileName = Dir(direc & "*.xl??")

The variable fileName now contains the name of the directory's first Excel file. Include a Do 

While Loop.
Do While fileName <> ""


Loop

Add the below three code blocks to this loop.

In the first column of the row, we add the name of the Excel file to the variables of type Integer.

i = i + 1
j = 2
Cells(i, 1) = fileName

There is no straightforward method for extracting data (or sheet names) from closed Excel files. As a result, we open the Excel file.

Workbooks.Open (direc & fileName)

Add the names of all the sheets in the Excel file to the other columns of row i and close the file,

For Each sheet In Workbooks(fileName).Worksheets
   Workbooks("files-in-a-directory.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name
   j = j + 1
Next sheet
Workbooks(fileName).Close

The Dir function is a one-of-a-kind function. You can use the Dir function with no arguments to fetch the other Excel files.

fileName = Dir()

When no more file names matches, the Dir function produces a zero-length string (""). As a result, Excel VBA will exit the Do While loop.

Turn on screen updating and flickering again (outside the loop).

Application.ScreenUpdating = True

This results,

I hope this section is clear to you. So Now, let's move on to the next point.

Importing Sheets to other Excel Files

In this section, we will look at how an Excel VBA application imports sheets from other Excel files into a single Excel file.

Add files Example1.xlsx, Example2.xlsx, Example3.xlsx in the directory "C:\test\",

Initially, the worksheet looked like this,

First, we declare two String variables, a Worksheet object, and one Integer variable.

Dim direc As String, fileName As String, sheet As Worksheet, total As Integer

Turn off the screen updating and displaying alerts by using.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set up the variable direc. The Dir function is used to locate the first *.xl?? file in this directory.

direc = "c:\test\"
fileName = Dir(directory & "*.xl??")

The variable fileName now contains the name of the directory's first Excel file. Include one 

Do While Loop.
Do While fileName <> ""


Loop

Now add the below codes in the while loop

//There is no simple way of copying worksheets from closed Excel files. Therefore we open the Excel file.
Workbooks.Open (direc & fileName)

// Importing the sheets from the Excel file into import-sheet.xlsm.
For Each sheet In Workbooks(fileName).Worksheets
   total = Workbooks("import-sheets.xlsm").Worksheets.count
   Workbooks(fileName).Worksheets(sheet.Name).Copy _
   after:=Workbooks("import-sheets.xlsm").Worksheets(total)
Next sheet
Workbooks(fileName).Close

The variable total keeps track of the total number of import-sheet.xlsm worksheets. We use the Worksheet object's Copy method to copy each worksheet and paste it after the last worksheet in import-sheets.xlsm. Now moving ahead to check our result,

The Dir function is a unique function. You can use the Dir function here with no arguments to fetch the other Excel files.

fileName = Dir()
// Turning on the Screen Updates
Application.ScreenUpdating = True
Application.DisplayAlerts = True

This will result as,
 

I hope it's clear now how we import sheets to other excel files. Now moving to our last section of the discussion.

Programming Charts in VBA

In this section, we will look at two Excel VBA scripts. One application loops through all charts on a page and converts each chart to a pie chart. The other program modifies some of the attributes of the first chart. A general plotting as charts is shown below,

 

Now let's modify this chart as per our needs,

 

First, we must create a ChartObject object. A Chart object is contained within the ChartObject object. We call the ChartObject cht, but you can call it whatever you like.

Dim cht As ChartObject

All of the embedded charts on a single sheet are contained in the ChartObjects collection. We want to go over all of the charts on the first sheet in a loop. Include the For Each Next loop below.

For Each cht In Worksheets(1).ChartObjects


Next cht

A chart in a workbook is represented by the Chart object. To convert each chart to a pie chart, add the following code line to the For Each Next loop.

cht.Chart.ChartType = xlPie

Result:

To change any property of a given chart, you can use these methods,

The Chart Objects collection comprises all embedded charts on a single sheet. To enable the first chart, add the following code line: 

Worksheets(1).ChartObjects(1).Activate

Now that this chart has been activated, We can refer to it as an active chart.

To modify the Chart title, add the following code line.

ActiveChart.ChartTitle.Text = "Sales"

 

I hope now it is clear how we play with various methods to add or modify our charts. Since the discussion over topics is completed now, let's move to our FAQs Section.

FAQs

What is VBA?

VBA is an abbreviation for Visual Basic for Applications, a computer language intended to automate specific procedures in Excel spreadsheets.

What is Ribbon in Excel?

The Ribbon is your primary interface with Excel, and it is located at the top of the Excel window. It gives users quick access to many essential commands. It has numerous tabs such as File, Home, View, Insert, etc.

What are Pivot charts in Excel?

MS Excel chart is data visualization tool that allow you to view data in a variety of ways. These charts can be of any type, including bar, pie, area, line, doughnut, pivot, etc.

Mention the Differences between Workbook and Worksheet.

Conclusion

In this article, we have extensively discussed the differences between WorkBook and Worksheets, continuing to various methods like Open and Close, Path, and Full name. Later we concluded by discussing loops and Charts.

To learn more about Excel and its applications you can read these blogs, WorksheetsFunction and Sub, and Must Have Excel Skills .

Refer to our guided paths on Coding Ninjas Studio to learn more about DSA, Competitive Programming, JavaScript, System Design, etc. Enroll in our courses, refer to the mock test and problems; look at the interview experiences and interview bundle for placement preparations.

Do upvote our blog to help other ninjas grow.

Happy Learning!

Topics covered
1.
Introduction
2.
Objects in Worksheet
3.
Path and FullName in VBA
4.
Open and Close method
5.
Looping through Books and sheets
6.
Working with Files in a Directory
7.
Importing Sheets to other Excel Files
8.
Programming Charts in VBA
9.
FAQs
9.1.
What is VBA?
9.2.
What is Ribbon in Excel?
9.3.
What are Pivot charts in Excel?
9.4.
Mention the Differences between Workbook and Worksheet.
10.
Conclusion