Excel Parameters in VB Scripting

To interact with Excel Library from VB Script we need to create an object which points to the excel library from VB Script.

Let us look at some of the common methods used to deal with Excel Library.

Workbooks Method is used to point to a work book.
Worksheet method is used to point to a worksheet.

Before creating a Workbook or worksheet we need to create an Object to interact with Excel Library.

The syntax to create an object is

Set [Object] = CreateObject("Excel.Application")
Set xlapp = CreateObject("Excel.Application")

To Create a new workbook we need to use this syntax. The syntax to add a workbook is

Set [Variable] = Object.Workbooks.Add
Set wbook = xlapp.Workbooks.Add

To Open an existing workbook we need to use this syntax. The syntax to open an existing workbook is

Set [Variable] = Object.Workbooks.Open ()
Set wbook = xlapp.Workbooks.Open("F:\TestFolder ")

To create a worksheet in the workbook we use this syntax and the syntax to create a worksheet is

Set [Variable] = Object.worksheets(id)
Set wsheet = xlapp.worksheets(1)
Set wsheet = xlapp.worksheets("Testing")

Here id can be the sheet id and the index begin with 1. You can also give a string for the id but should be enclosed in " ".

To save a new workbook in a destination we use this syntax

Variable.SaveAs ()
wbook.SaveAs(("F:\TestFolder\Test.xls")

Every time a workbook is created or opened it should be closed and to close the workbook we need a Close Method. To close a Work book the syntax is

Variable.Close
wbook.Close

It is a good practice to quit the application once your work is over and to quit the excel application we need a Quit Method. The syntax is

Object.Quit
Xlapp.Quit

As soon as the application is quit it is required to clear the variables so that it does not interfere with other scripts. It is important that the worksheet, workbook, excel application variables should be cleared. To clear the variable used from the memory we use the Nothing Method. The syntax is

Set [Object] = Nothing
Set wsheet = Nothing
Set wbook = Nothing
Set xlapp = Nothing

To create cells in the worksheet we use this syntax

wsheet.cells(rows,columns)
wsheet.cells(1,1)

Each Cell is identified with Row and Column as (Row, Column)

Check this script to understand how these methods are used.

VB Script using Excel Library Methods and Properties