play with excel in vb script

‘how to create an excel using vb script
function create
set xl=createobject(“excel.application”)
xl.workbooks.add
xl.visible=true
set xl=nothing
end function

‘ read excel data using vb script
function read
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(1)
read=ws.cells(1,2).value
msgbox read
wb.close
set ws=nothing
set wb=nothing
set xl=nothing
end function

‘ write data into excel using vb script

function write
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(2)     ‘second sheet in the excel
ws.cells(6,8).value=”india”
wb.save
wb.close
set ws=nothing
set wb=nothing
set xl=nothing
end function

‘ format excel using vb script
function format
set xl=createobject(“excel.application”)
set wb=xl.workbooks.open(“d:\sample.xls”)
set ws=wb.worksheets(1)
ws.cells(4,5).value=”hai”
ws.cells(4,5).font.bold=true
ws.cells(4,5).font.colorindex=50
ws.cells(4,5).font.size=18
ws.cells(4,5).interior.colorindex=20
wb.save
wb.close
set ws=nothing
set wb=nothing
set xl=nothing
msgbox “completed”
end function

‘ function calls
create
read
write
format

1.How to get excel sheets count using vb script

n=wbobject.worksheets.count

in the above wbobject is the variable name defined by the user for the workbook object

2.How to get row and coloumn count in excel using vb script

r=sh1.usedrange.rows.count
c=sh1.usedrange.columns.count

in the above sh1 is the variable name defined by the user for worksheet object

3.How to change the cells colour (interior or outer ) and bold properties in excel using vb script

for i=1 to 4

sh1.cells(i,j).font.bold=true
sh1.cells(i,j).font.colorindex=50
sh1.cells(i,j).interior.colorindex=20

next

in the above sh1 is the variable name defined by the user for worksheet object