Working with VBScript Excel Objects

Introduction to VBScript Excel Objects: Tutorial #11

In my previous tutorial, I explained ‘Events’ in the VBScript. In this tutorial, I will be discussing Excel Objects that are used in the VBScript. Please note that this is the 11th tutorial in our ‘Learn VBScripting‘ series.

VBScript supports different types of objects and Excel Objects are among those. Excel Objects are mainly referred to as objects that provide support to the Coders to work and deal with the Excel Sheets.

This tutorial gives you a complete overview of the process of creation, addition, deletion, etc., of an Excel file using Excel Objects in the VBScript with simple examples.

Overview

Microsoft Excel needs to be installed on your computer to work with the Excel files. By creating an Excel Object, VBScript provides you the assistance to perform important operations like creating, Open, and editing Excel files.

It is very important to understand this topic as this forms the basis of working with the Excel sheets and hence I decided to pick this as one of the topics in the series of the VBScript tutorial.

I will try to make you understand all the different codes that are required to be written to work with the Excel files easily so that you can easily write a piece of code on your own.

Now, let’s move on to the practical working of Excel files by understanding the code written for different scenarios focusing mainly on the important ones.

Creating an Excel File Using Excel Object

In this section, we will see the various steps involved in creating an Excel file using the Excel Object mechanism in the VBScript.

Following is the Code for Creating an Excel File:

Set obj = createobject(“Excel.Application”)  ‘Creating an Excel Object
obj.visible=True                                    ‘Making an Excel Object visible
Set obj1 = obj.Workbooks.Add()       ‘Adding a Workbook to Excel Sheet
obj1.Cells(1,1).Value=”Hello!!”         ‘Setting a value in the first-row first column
obj1.SaveAs “C:\newexcelfile.xls”   ‘Saving a Workbook
obj1.Close                                             ‘Closing a Workbook
obj.Quit                                                  ‘Exit from Excel Application
Set obj1=Nothing                                 ‘Releasing Workbook object
Set obj=Nothing                                   ‘Releasing Excel object

Let’s understand how it works:

• Firstly, an Excel Object with the name ‘obj’ is created using the ‘createobject’ keyword and defining Excel application in the parameter as you are creating an Excel Object.
• Then an Excel Object that is created above is made visible to the users of the sheet.
• A Workbook is then added to the Excel object – obj to perform actual operations inside the sheet.
• Next, the main task is performed by adding a value in the first column of the first row of the workbook that is created above.
• The workbook is then closed as the task has been completed.
• Excel Object is then exited as the task has been finished.
• Finally, both the objects – obj and obj1 are released by using the ‘Nothing’ keyword.

Note: It is a good practice to release the objects using ‘Set object name = Nothing’ after the completion of the task at the end.

Reading/Opening an Excel File Using Excel Object

In this section, we will see the different steps of reading the data from an Excel file using the Excel Object mechanism in the VBScript. I will use the same Excel file that is created above.

Following is the Code for reading the data from an excel file:

Set obj = createobject(“Excel.Application”)   ‘Creating an Excel Object
obj.visible=True                                    ‘Making an Excel Object visible
Set obj1 = obj.Workbooks.open(“C:\newexcelfile.xls”)    ‘Opening an Excel file
Set obj2=obj1.Worksheets(“Sheet1”)    ‘Referring Sheet1 of excel file
Msgbox obj2.Cells(2,2).Value  ‘Value from the specified cell will be read and shown
obj1.Close                                             ‘Closing a Workbook
obj.Quit                                                  ‘Exit from Excel Application
Set obj1=Nothing                                 ‘Releasing Workbook object
Set obj2 = Nothing                               ‘Releasing Worksheet object
Set obj=Nothing                                   ‘Releasing Excel object

Let’s understand how it works:

• Firstly, an Excel Object with the name ‘obj’ is created using ‘createobject’ keyword and defining the Excel application in the parameter as you are creating an Excel Object.
• Then the Excel Object that is created above is made visible to the users of the sheet.
• The next step is to open an Excel file by specifying the location of the file.
• Then, a worksheet of the workbook or an Excel file is specified to access the data from a particular sheet of an Excel file.
• Finally, the value from the particular cell (2^nd column from 2^nd row) is read and displayed with the help of a message box.
• The workbook object is then closed as the task has been completed.
• Excel Object is then exited as the task has been finished.
• Finally, all the objects are released by using the ‘Nothing’ keyword.

Deletion From an Excel File

In this section, we will take a look at the steps involved in deleting data from an Excel file using the Excel Object mechanism in VBScript. I will use the same excel file that is created above.

Following is the Code for deleting the data from an Excel file:

Set obj = createobject(“Excel.Application”)   ‘Creating an Excel Object
obj.visible=True                                    ‘Making an Excel Object visible
Set obj1 = obj.Workbooks.open(“C:\newexcelfile.xls”)    ‘Opening an Excel file
Set obj2=obj1.Worksheets(“Sheet1”)    ‘Referring Sheet1 of excel file
obj2.Rows(“4:4”).Delete           ‘Deleting 4th row from Sheet1
obj1.Save()                                   ‘Saving the file with the changes
obj1.Close                                             ‘Closing a Workbook
obj.Quit                                                  ‘Exit from Excel Application
Set obj1=Nothing                                 ‘Releasing Workbook object
Set obj2 = Nothing                               ‘Releasing Worksheet object

Let’s understand how it works:

• Firstly, an Excel Object with the name ‘obj’ is created using ‘createobject’ keyword and defining Excel application in the parameter as you are creating an Excel Object.
• Then an Excel Object that is created above is made visible to the users of the sheet.
• The next step is to open an Excel file by specifying the location of the file.
• Then, a worksheet of the workbook or an Excel file is specified to access the data from the particular sheet of an Excel file.
• Finally, the 4^th row is deleted and the changes are saved on the sheet.
• The workbook object is then closed as the task has been completed.
• Excel Object is then exited as the task has been finished.
• Finally, all the objects are released by using the ‘Nothing’ keyword.

Addition & Deletion of a Sheet from an Excel File

In this section, let’s see the different steps of adding and deleting an Excel sheet from an Excel file using the Excel Object mechanism in VBScript. Here also I will use the same excel file that is created above.

Following is the Code for this scenario:

Set obj = createobject(“Excel.Application”)   ‘Creating an Excel Object
obj.visible=True                                    ‘Making an Excel Object visible
Set obj1 = obj.Workbooks.open(“C:\newexcelfile.xls”)    ‘Opening an Excel file
Set obj2=obj1.sheets.Add  ‘Adding a new sheet in the excel file
obj2.name=”Sheet1”     ‘Assigning a name to the sheet created above
Set obj3= obj1.Sheets(“Sheet1”)  ‘Accessing Sheet1
obj3.Delete       ‘Deleting a sheet from an excel file
obj1.Close                                             ‘Closing a Workbook
obj.Quit                                                  ‘Exit from Excel Application
Set obj1=Nothing                                 ‘Releasing Workbook object
Set obj2 = Nothing                               ‘Releasing Worksheet object
Set obj3 = Nothing                              ‘Releasing Worksheet object
Set obj=Nothing                                   ‘Releasing Excel object

Let’s understand how it works:

• Firstly, an Excel Object with the name ‘obj’ is created using ‘createobject’ keyword and defining the Excel application in the parameter as you are creating an Excel Object.
• Then an Excel Object that is created above is made visible to the users of the sheet.
• The next step is to open an Excel file by specifying the location of the file.
• The worksheet is then added to an Excel file and a name is assigned to it.
• Then, a worksheet of the workbook or an Excel file is accessed (created in the earlier step) and it is deleted.
• The workbook object is then closed as the task has been completed.
• Excel Object is then exited as the task has been finished.
• Finally, all the objects are released by using the ‘Nothing’ keyword.

Copying & Pasting of Data from one Excel File to Another Excel File

In this section, we will see the different steps involved in copying/pasting data from one Excel file to another excel file using the Excel Object mechanism in the VBScript. I have used the same Excel file that was used in the above scenarios.

Following is the Code for this scenario:

Set obj = createobject(“Excel.Application”)   ‘Creating an Excel Object
obj.visible=True                                    ‘Making an Excel Object visible
Set obj1 = obj.Workbooks.open(“C:\newexcelfile.xls”)    ‘Opening an Excel file1
Set obj2 = obj.Workbooks.open(“C:\newexcelfile1.xls”)    ‘Opening an Excel file2
obj1.Worksheets(“Sheet1”).usedrange.copy  ‘Copying from an Excel File1
obj2.Worksheets(“Sheet1”).usedrange.pastespecial  ‘Pasting in Excel File2
obj1.Save                                              ‘ Saving Workbook1
obj2.Save                                              ‘Saving Workbook2
obj1.Close                                             ‘Closing a Workbook
obj.Quit                                                 ‘Exit from Excel Application
Set obj1=Nothing                                ‘Releasing Workbook1 object
Set obj2 = Nothing                              ‘Releasing Workbook2 object
Set obj=Nothing                                  ‘Releasing Excel object

Let’s understand how it works:

• Firstly, an Excel Object with the name ‘obj’ is created using ‘createobject’ keyword and defining Excel application in the parameter as you are creating an Excel Object.
• Then the Excel Object that is created above is made visible to the users of the sheet.
• The next step is to open 2 excel files by specifying the location of the files.
• Data is copied from Excel file1 and pasted to Excel file2.
• Both the Excel Files have been saved.
• The workbook object is then closed as the task has been completed.
• Excel Object is then exited as the task has been finished.
• Finally, all the objects are released by using a ‘Nothing’ keyword.

These are some of the important scenarios which are required in the proper understanding of the concept. They form the foundation to work and deal with the codes for handling different types of scenarios while dealing with the Excel Objects in the script.

Conclusion

Excel plays a prime major role everywhere. I’m sure that this tutorial must have given you a great insight into the importance and effectiveness of using VBS Excel Objects.

Next Tutorial #12: Our next tutorial will cover ‘Connection Objects’ in the VBScript.

Stay tuned and feel free to share your experiences with working with Excel. Also, let us know if you have any queries about this tutorial.