Skip to main content

Excel Automation Object Model

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)

Example:
'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)
Example given below


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

 Value Property:

Description: DataTable default property. Retrieves or sets the value of the cell in the specified parameter and the current row of the run-time Data Table.

Syntax: DataTable.Value(ParameterID [, SheetID])
Example given below


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)
Example given below


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"
 
wait 5

Comments

Popular posts from this blog

JMeter Exceeded Maximum Number of Redirects Error Solution

While running performance test, JMeter allows maximum 5 redirects by default. However, if your system demands more than 5 redirects, it may result in JMeter exceeded maximum number of redirects error. In this post, we have listed down steps to overcome this error. Actual error in JMeter: Response code: “Non HTTP response code: java.io.IOException” Response message: “Non HTTP response message: Exceeded maximum number of redirects: 5” This error is noticed because  JMeter  allows maximum 5 redirects by default and your system may be using more than 5 redirects. You need to increase this count to more than 5 in jmeter.properties file. Follow below steps to achieve this. Navigate to /bin directory of your JMeter installation. Locate jmeter.properties file and open it in any editor. Search for “httpsampler.max_redirects” property in opened file. Uncomment the above property by removing # before it. Change to value to more than 5 Eg. 20. Save the file and restart JMeter. If

SSO with SAML login scenario in JMeter

SAML(Security Assertion Markup Language) is increasingly being used to perform single sign-on(SSO) operations. As WikiPedia puts it, SAML is an XML-based open standard data format for exchanging authentication and authorization data between parties, in particular, between an identity provider and a service provider. With the rise in use of SAML in web applications, we may need to handle this in JMeter. This step-by-step tutorial shows SAML JMeter scenario to perform login operation. First request from JMeter is a GET request to fetch Login page. We need to fetch two values ‘SAMLRequest’ and ‘RelayState’ from the Login page response data. We can do this by using  Regular Expression Extractor . These two values need to be sent in POST request to service provider. Refer below image to see how to do this. We will get an HTML login page as a response to the request sent in 1st step. We need to fetch values of some hidden elements to pass it in the next request. We can do this b

A Tutorial to Send Email using JMeter

Sending email is a mundane activity in any professional’s life. It’s a common medium for communication nowadays. Therefore performance testing of email server is not only important but necessary for an organization. JMeter can be helpful to perform load testing in such scenarios. In this tutorial, we will see how JMeter can be used to send email. We will use SMTP Sampler of JMeter to send an email. JavaMail API is needed to enable email functionality in JMeter. Download it from  here  and paste the jar in JMeter’s lib folder. Now, perform below steps to configure SMTP Sampler. Add a new Thread Group under Test Plan. Right click on Thread Group and select Add–>Sampler–>SMTP Sampler. We need to populate SMTP server’s details in this sampler. We will use GMail for sending an email. For this, enter these values in SMTP Sampler fields. Server: smtp.googlemail.com, Port: 587. Provide values in Email Address From and To fields of Mail Settings section to specify sender and reci