VB Script using Excel Library Methods and Properties

Write a Script to create a workbook and create a worksheet and add information in the cells in VB script using Excel Library.

The Script to create a workbook and worksheets with cells is given below.

'Create an Object variable to interact with Excel Library
Set xlapp = CreateObject("Excel.Application")

'Create New Excel Workbook using the xlapp object
Set wbook = xlapp.workbooks.Add

'Create a pointer to the first sheet in the workbook
Set wsheet = xlapp.worksheets(1)

'Here we are adding the data in first column and color in second column
For i = 1 to 56

wsheet.cells(i,1).value = "The code of this color is: "&i
wsheet.cells(i,1).font.Name = "Verdana"
wsheet.cells(i,1).font.Size = 14
wsheet.cells(i,1).font.bold=TRUE
wsheet.cells(i,1).font.colorindex=i
wsheet.cells(i,2).interior.colorindex=i

Next

wbook.SaveAs "F:\Textexcel.xls"
wbook.Close
xlapp.Quit

'Releasing the objects
Set wsheet=Nothing
Set wbook=Nothing
Set xlapp = Nothing

Once you run this script go to the folder where the excel workbook is created and open and see how it looks like. The work sheet now contains information in two columns and in the Cell (1,1) you will find the value as The code of this color is 1 in black color and in bold and in the cell (1,2) you will find the cell filled with black color and like wise till row number 56.

You can manipulate the cells or using the wsheet.cells(rows,columns) syntax.