Excel Automation Object Model
Microsoft has developed the Excel application with heirarachy of object model.We can do excel operations using excel object model.
Simple object model Example: Excel Application --> Workbooks--> Worksheet--> cells
=> Create an Excel File:
'Create a new Microsoft Excel object
Set myxl = createobject("excel.application")
'To make Excel visible
myxl.Application.Visible = true
myxl.Workbooks.Add
wait 2
'Save the Excel file as qtp.xls
myxl.ActiveWorkbook.SaveAs "D:\qtp.xls"
'close Excel
myxl.Application.Quit
Set myxl=nothing
=>Create an Excel File , Enter some data , Save the Excel and close the Excel:
Set myxl = createobject("excel.application")
'Make sure that you have created an excel file before exeuting the script.
'Use the path of excel file in the below code
'Also make sure that your excel file is in Closed state before exeuting the script.
myxl.Workbooks.Open "D:\qtp.xls"
myxl.Application.Visible = true
'this is the name of Sheet in Excel file "qtp.xls" where data needs to be entered
set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")
'Enter values in Sheet1.
'The format of entering values in Excel is excelSheet.Cells(row,column)=value
mysheet.cells(1,1).value ="Name"
mysheet.cells(1,2).value ="Age"
mysheet.cells(2,1).value ="Ram"
mysheet.cells(2,2).value ="20"
mysheet.cells(3,1).value ="Raghu"
mysheet.cells(3,2).value ="15"
'Save the Workbook
=>Read the data from Excel File:
Set myxl = createobject("excel.application")
'Make sure that you have created an excel file before exeuting the script.
'Use the path of excel file in the below code
'Also make sure that your excel file is in Closed state
myxl.Workbooks.Open "D:\qtp.xls"
myxl.Application.Visible = true
'this is the name of Sheet in Excel file "qtp.xls" where data needs to be entered
set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")
'Get the max row occupied in the excel file
Row=mysheet.UsedRange.Rows.Count
'Get the max column occupied in the excel file
Col=mysheet.UsedRange.columns.count
'To read the data from the entire Excel file
For i= 1 to Row
For j=1 to Col
Msgbox mysheet.cells(i,j).value
Next
Next
'Save the Workbook
myxl.ActiveWorkbook.Save
'Close the Workbook
myxl.ActiveWorkbook.Close
'Close Excel
myxl.Application.Quit
Set mysheet =nothing
Set myxl = nothing
?Compare Two Excel sheets Cell by cell:
Mismatch=0
Set myxl = createobject("excel.application")
'To make Excel visible
myxl.Visible = True
'Open a workbook "qtp1.xls"
Set Workbook1= myxl.Workbooks.Open("C:\qtp1.xls")
'Open a workbook "qtp2.xls"
Set Workbook2= myxl.Workbooks.Open("C:\qtp2.xls")
Set mysheet1=Workbook1.Worksheets("Sheet1")
Set mysheet2=Workbook2.Worksheets("Sheet1")
'Compare two sheets cell by cell
For Each cell In mysheet1.UsedRange
'Highlights the cell if cell values not match
If cell.Value <>mysheet2.Range(cell.Address).Value Then
'Highlights the cell if cell values not match
cell.Interior.ColorIndex = 3
mismatch=1
End If
Next
If Mismatch=0 Then
Msgbox "No Mismach exists"
End If
'close the workbooks
Workbook1.close
Workbook2.close
myxl.Quit
set myxl=nothing
=>Search for Particular value in Excel:
Set myxl = createobject("excel.application")
'Make sure that you have created an excel file before exeuting the script.
'Use the path of excel file in the below code
'Also make sure that your excel file is in Closed state before executing the script.
myxl.Workbooks.Open "D:\qtp.xls"
myxl.Application.Visible = true
'This is the name of Sheet in Excel file "qtp.xls" where data needs to be entered
set mysheet = myxl.ActiveWorkbook.Worksheets("Sheet1")
'Contents of Sheet1
'Name Age
'Ram 20
'Raghu 15
'Select the used range in particular sheet
With mysheet.UsedRange
' Data "Ram" to search
' Loop through the used range
For each search_data in mysheet.UsedRange
' compare with the expected data
If search_data="Ram" then
'make the cell with color if it finds the data
search_data.Interior.ColorIndex = 40
End If
next
End With
'Save the Workbook
myxl.ActiveWorkbook.Save
'Close the Workbook
myxl.ActiveWorkbook.Close
'Close Excel
myxl.Application.Quit
Set mysheet =nothing
Set myxl = nothing
?Copy an Excel sheet to another Excel sheet:
Set myxl = createobject("excel.application")
'To make Excel visible
myxl.Visible = True
'Open a workbook "qtp1.xls"
Set Workbook1= myxl.Workbooks.Open("C:\qtp1.xls")
'Open a workbook "qtp2.xls"
Set Workbook2= myxl.Workbooks.Open("C:\qtp2.xls")
'Copy the used range of workbook "qtp1.xls"
Workbook1.Worksheets("Sheet1").UsedRange.Copy
'Paste the copied values in above step in the A1 cell of workbook "qtp2.xls"
Workbook2.Worksheets("Sheet1").Range("A1").PasteSpecial Paste =xlValues
'Save the workbooks
Workbook1.save
Workbook2.save
'close the workbooks
Workbook1.close
Workbook2.close
myxl.Quit
set myxl=nothing
=> Addsheet Method:
Description: Adds the specified sheet to the run-time Data Table and returns the sheet so that you can directly set properties of the new sheet in the same statement.
Syntax: DataTable.AddSheet(SheetName)
'Create a datatable sheet during Run time.This sheet will be available during run time only.
'We can view this sheet in Result Summaryunder section "Run Time data Table".
datatable.AddSheet("Qtpworld")
'To add column name and a default value under them.
datatable.GetSheet("Qtpworld").AddParameter "name","Ram"
datatable.GetSheet("Qtpworld").AddParameter "age","18"
wait 5
=> DeleteSheet Method:
Description: Deletes the specified sheet from the run-time Data Table.
Syntax: DataTable.DeleteSheet SheetID
Example:
'Create a datatable sheet during Run time.This sheet will be available during run time only.
'We can view this sheet in Result Summary under section "Run Time data Table" .
datatable.AddSheet("Qtpworld")
'To delete datatable sheet
datatable.DeleteSheet("Qtpworld")
datatable.DeleteSheet("Global")
wait 3
=> Import Method:
Description: Imports the specified Microsoft Excel file to the run-time Data Table.
Syntax: DataTable.Import(FileName)
=> Export Method:
Description: Saves a copy of the run-time Data Table in the specified location.
Syntax: DataTable.Export(FileName)
Example:
'If data is stored in multiple sheet in external Excel Workbook,
'we can import multiple sheet data into Datatable and
'Do neccessary operation on the imported data.
datatable.Import "C:\qtptest.xls"
'To get the total count of QTP datatable sheets
msgbox datatable.GetSheetCount
'After the operations are done,you can export the all the qtp datasheets to the External file
'Create a datatable sheet during Run time.This sheet will be available during run time only.
datatable.Export "C:\qtptest.xls"
'We can view this sheet in Result Summary under section "Run Time data Table" .
datatable.AddSheet("Qtpworld")
'To delete datatable sheet
datatable.DeleteSheet("Qtpworld")
datatable.DeleteSheet("Global")
wait 3
=> ImportSheet Method:
Description: Imports a sheet of a specified file to a specified sheet in the run-time Data Table. The data in the imported sheet replaces the data in the destination sheet (see SheetDest argument).
Syntax: DataTable.ImportSheet(FileName, SheetSource, SheetDest)
=>ExportSheet Method:
Description: Exports a specified sheet of the run-time Data Table to the specified file.
If the specified file does not exist, a new file is created and the specified sheet is saved.If the current file exists, but the file does not contain a sheet with the specified sheet name, the sheet is inserted as the last sheet of the file.
If the current file exists and the file contains the specified sheet, the exported sheet overwrites the existing sheet.
Syntax: DataTable.ExportSheet(FileName, DTSheet)
Example:
'If data is stored in a particular sheet in external Excel Workbook ,
'we can import only that particular sheet data into Datatable and
'do neccessary operation on the imported data.
'Create a sheet "Sheet1" in qtp
datatable.AddSheet "Sheet1"
'Sheet1 data from excel file contains the following data
'Name Age
'Ramu 20
'Rakesh 24
'Import Sheet1 data from excel file to qtp sheet "Sheet1"
datatable.ImportSheet "C:\qtpsheet.xls","Sheet1","Sheet1"
'Add a column "Result" for displaying result in qtp sheet
datatable.GetSheet("Sheet1").AddParameter "Result",""
wait 2
'Apply the logic: if age is less than 18 then the guy is " Minor" else "Major"
row =datatable.GetSheet("Sheet1").GetRowCount
For i = 1 to row
datatable.GetSheet("Sheet1").SetCurrentRow(i)
If datatable.Value("Age","Sheet1") > 18 Then
datatable.Value("Result","Sheet1") = "Major"
Else
datatable.Value("Result","Sheet1") = "Minor"
End If
Next
'Export the qtp sheet "Sheet1" bak to external excel
Datatable.ExportSheet "C:\qtpsheet.xls","Sheet1"
' After exporting you can see that the excel file now has been updated with result
=> GetSheet Method:
Description: Returns the specified sheet from the run-time Data Table.
Syntax: DataTable.GetSheet(SheetID)
Example given below
=> GetSheetCount Method:
Description: Returns the total number of sheets in the run-time Data Table.
Syntax: DataTable.GetSheetCount
Example given below
=> GetCurrentRow Method:
Description: Returns the current (active) row in the first sheet in the run-time Data Table (global sheet).
Syntax: DataTable.GetCurrentRow
Example given below
=> GetRowCount Method:
Description: Returns the total number of rows in the longest column in the first sheet in the run-time Data Table (global sheet).
Syntax: DataTable.GetRowCount
Example given below
=> SetCurrentRow Method:
Description: Sets the specified row as the current (active) row in the run-time Data Table.
Syntax: DataTable.SetCurrentRow(RowNumber)
Example:
'Create a datatable sheet during Run time.
'This sheet will be available during run time only.
'We can view this sheet in Result Summary under section "Run Time data Table" .
datatable.AddSheet("Qtpworld")
'To add column name and a default value under them.
datatable.GetSheet("Qtpworld").AddParameter "name","Ram"
datatable.GetSheet("Qtpworld").AddParameter "age","18"
'Enter data into second row of datatsheet "Qtpworld"
datatable.GetSheet("Qtpworld").SetCurrentRow(2)
datatable.Value("name","Qtpworld")="Ramu"
datatable.Value("age","Qtpworld")="23"
'total number of datasheets in the run-time Data Table
Msgbox datatable.GetSheetCount
'Get the max used range of the datasheet
row=datatable.GetSheet("Qtpworld").GetRowCount
'Loop to read all the data in the datasheet "Qtpworld"
For Drow= 1 to row
datatable.GetSheet("Qtpworld").SetCurrentRow(Drow)
Msgbox datatable.Value("name","Qtpworld")
Msgbox datatable.Value("age","Qtpworld")
Msgbox "Current Row is: " & datatable.GetSheet("Qtpworld").GetCurrentRow
Next
=> GlobalSheet Property:
Description: Returns the first sheet in the run-time Data Table (global sheet).
Syntax: DataTable.GlobalSheet
Example given below
=> LocalSheet Property:
Description: Returns the current (active) local sheet of the run-time Data Table.
Syntax: DataTable.LocalSheet
Example:
'To add column name in Global Sheet and a default value under them.
datatable.GlobalSheet.AddParameter "name","ramu"
datatable.GlobalSheet.AddParameter "age","18"
'To add column name in Local Sheet and a default value under them.
datatable.LocalSheet.AddParameter "name","Rakesh"
datatable.LocalSheet.AddParameter "age","22"
No comments:
Post a Comment