Excel Operations In Vb-script

Microsoft has developed the Excel application with hierarchy of object model.We can do excel operations using excel object model.

Simple object model Example: 
Excel Application --> Workbooks--> Worksheet--> cells

Syntax:

Set variable = CreateObject("Class value") 

Excel objects

1) Excel Application Object
It is Used to perform operations on Excel Application

Set variable = CreateObject("Excel.Application")

2) Excel Workbook Object
It is used to work with Excel files/Workbooks

Set variable = ExcelApplicationObject.Workbooks.Add/Open("File Path")

3) Excel Worksheet Object
It is used to work with Excel Sheets/Worksheets

Set variable = ExcelWorkbookObject.Worksheets(Sheet Id Or "Sheet Name")

Excel Application Object is always only one, We can create one or more Excel Workbook objects, We can create one or more Excel Worksheet objects for every workbook object

Difference between FileSystemObject model and Excel object model in case of Sub Objects.

> In FileSystemObject model creating Text stream object (sub object) is mandatory to perform Text(Read, write etc…) related operations

> In Excel object model creating sub objects is optional, but if you want work with multiple files and multiple sheets then sub objects are required.

Create an Excel file

Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True ‘To view the Operation during execution
objExcel.Workbooks.Add ‘To create new file
objExcel.ActiveWorkbook.SaveAs “C:\Users\Desktop\raju.xlsx”
objExcel.Quit ‘To close the Excel Application
Set objExcel = Nothing

Getting value from an existing excel sheet

Dim oExcel, oWB, oSheet, getVal
Set oExcel=CreateObject("Excel.Application")
Set oWB=oExcel.Workbooks.Open("C:\raju.xls")
Set oSheet=oWB.WorkSheets("Sheet1")
oExcel.Visible=TRUE
getVal=oSheet.Cells(1,1).Value
print getVal
oWB.Close
Set oExcel=Nothing

Inserting value to an existing excel sheet

Dim oExcel, oWB, oSheet, setVal
Set oExcel=CreateObject("Excel.Application")
Set oWB=oExcel.Workbooks.Open("C:\raju.xls")
Set oSheet=oWB.WorkSheets("Sheet1")
oExcel.Visible=TRUE
setVal="ValueInserted"
oSheet.Cells(4,1).Value=setVal

oWB.Save
oWB.Close
Set oExcel=Nothing

Create excel file and enter some data save it......  

'Create Excel Object    
Set excel=createobject("excel.application")  
'Make it Visible    
excel.Visible=True  
'Add New Workbook    
Set workbooks=excel.Workbooks.Add()  
'Set the value in First row first column    
excel.Cells(1,1).value="testing"  
'Save Work Book    
workbooks.saveas"D:\raju.xls"  
'Close Work Book    
workbooks.Close  
'Quit from Excel Application    
excel.Quit  
'Release Variables    
Set workbooks=Nothing  
Set excel=Nothing

Deleting Rows from Excel Sheet ...... 

'Create Excel Object    
Set excel=createobject("excel.application")  
'Make it Visible    
excel.Visible=True  
'Open the Excel File  
Set workbook=excel.Workbooks.Open("D:\raju.xls")
'Get the Control on Specific Sheet  
Set worksheet1=excel.Worksheets.Item("Sheet1")
'Delete Row1  
worksheet1.Rows("1:1").delete
'Save Excel  
workbook.SaveAs("D:\raju.xls")
'Close Work Book    
workbook.Close  
'Quit from Excel Application    
excel.Quit  
'Release Variables    
Set worksheet1=Nothing
Set workbook=Nothing
Set excel=Nothing  

Count number of rows in Excel without opening it - using vbscipt in UFT.

Set objExcel=CreateObject("Excel.Application")
Set objWB=objExcel.WorkBooks.Open("C:\abc.xls")
Set objSheet=objWB.WorkSheets(1)
msgbox objSheet.usedrange.rows.count
Set objExcel=Nothing

Add new sheet....

'This code demonstrate how to add new sheet to existing excel file and rename it at runtime

Dim oExcel, oWB, oSheet, getVal
Set oExcel=CreateObject("Excel.Application")
oExcel.visible=True
oExcel.DisplayAlerts = False   'this will not allow alerts to be displayed
Set oWB=oExcel.Workbooks.Open("C:\raju.xls")
Set oSheet1=oWB.Sheets.Add  'a new sheet is added with default name i.e. Sheet4 etc.

oSheet1.Name="UFT"  'rename newly added sheet
oSheet1.cells(1,1).value="Welcome"  'enter value
oWB.Save
oWB.Close
Set oExcel=Nothing

Using excel sheet as database table

Excel sheet can be used as a database for the  parameterization purpose. Following code demonstrate how to connect and consider excel sheet as database table.

This might be usefull while working with databases. You can export database 

table into excel (one time) and then work on excel as database

Dim objCon, objRecordSet, strExlFile, colCount, row,i

Set objCon = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

strExlFile = "D:\TestScript.xlsx"

objCon.Open "DRIVER={Microsoft Excel Driver(*.xls)};DBQ="&strExlFile&"; Readonly=True"

strSQLStatement = "SELECT * FROM [Sheet1$]"

objRecordSet.Open strSQLStatement, objCon 'create recordset

colCount = objRecordSet.Fields.count    'No of columns in the table

While objRecordSet.EOF=false
    row=""
    For i=0 to colCount-1
        row=row &"    "& objRecordSet.fields(i)
    Next 
    Print row
    objRecordSet.moveNext
Wend
Set objRecordSet = Nothing
objCon.Close
Set objCon = Nothing