Reading and writing data from an Excel file plays a crucial role. There would be hardly an automation framework where data might not be read from an Excel file for taking input dynamically at runtime.
We will see the following useful functions to play around with an Excel Sheet with QTP/UFT. In this tutorial, I will be using “E:\Test\TestData.xlsx “ as the path of the Excel file. Change it according to your local machine.
Read Excel Sheet Cell Values One by One for all Columns
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Function readeEachColumnDataExcel ( )
Set objExcel = CreateObject ( "Excel.Application" )
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
Set objSheet = objWorkbook . Worksheets ( "Sheet1" )
columncount = objSheet . UsedRange . Columns . Count
RowCount = objSheet . UsedRange . Rows . Count
For i = 1 To columncount
For j = 1 To RowCount
strValue = objSheet . Cells ( j , i )
MsgBox strValue 'Displaying cell values
Next
Next
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
End Function
Call readeEachColumnDataExcel 'Call the function to get the output
Read Excel Sheet Cell Values One by One for all Rows
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Public Function readeEachRowDataExcel ( )
Set objExcel = CreateObject ( "Excel.Application" )
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
Set objSheet = objWorkbook . Worksheets ( "Sheet1" )
ColCount = objSheet . UsedRange . Columns . Count
RowCount = objSheet . UsedRange . Rows . Count
For i = 1 To RowCount
For j = 1 To ColCount
fieldvalue = objSheet . Cells ( i , j )
MsgBox fieldvalue
Next
Next
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
End Function
Call readeEachRowDataExcel 'Call the function to get the output
Read data from a specific cell in Excel Sheet
Public Function GetExcelCellData ( intRow , intCol )
Set objExcel = CreateObject ( "Excel.Application" )
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
Set objSheet = objWorkbook . Worksheets ( "Sheet1" ) 'Or pass sheet number integer value 1,2,etc
strValue = objSheet . Cells ( intRow , intCol )
GetExcelCellData = strValue 'Returning value of the specified cell
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
End Function
Call GetExcelCellData ( 2 , 3 ) 'Call the function and pass the required row and column value
Write data to a specific cell in Excel Sheet
The following function can be used to write data to the required cell.
Public Function SetExcelCellData ( intRow , intCol , strValue )
Set objExcel = CreateObject ( "Excel.Application" )
objExcel . DisplayAlerts = False
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
Set objSheet = objWorkbook . Worksheets ( "Sheet1" ) 'Or pass sheet number integer value 1,2,etc
objSheet . Cells ( intRow , intCol ) . value = strValue
objWorkbook . SaveAs "E:\Test\TestData.xlsx"
objWorkbook . Close
Set objSheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
End Function
Call SetExcelCellData ( 2 , 2 , "TestValue" ) 'Call the function to set the required value in the desired cell
Count Sheets in an Excel file
Public Function GetExcelSheetCount ( )
Set objExcel = CreateObject ( "Excel.Application" )
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
GetExcelSheetCount = objWorkbook . Worksheets . Count
MsgBox GetExcelSheetCount
Set objWorkbook = Nothing
Set objExcel = Nothing
End Function
iSheetCount = GetExcelSheetCount 'Call the function and store the value in a variable
Insert a Column to an Excel Sheet
The below code can be used to insert a column at the right side of the specified cell range.
Const xlToRight = -4161
Set objExcel = CreateObject ( "Excel.Application" )
objExcel . DisplayAlerts =False
objExcel . Visible = True
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
Set objSheet = objWB . Sheets ( "Sheet1" )
objSheet . Columns ( "C:C" ) . Insert xlToRight 'Provide the required range
objSheet . Cells ( 1 , 3 ) = "NewColumn"
objWorkbook . SaveAs "E:\Test\TestData.xlsx"
objWorkbook . Close True
objExcel . Quit
Set objWorkbook = Nothing
Set objExcel = Nothing
After adding a column the sheet will look like the below snapshot.
Delete a Column of an Excel Sheet
Set objExcel = CreateObject ( "Excel.Application" )
objExcel . DisplayAlerts =False
objExcel . Visible = False
Set objWorkbook = objExcel . Workbooks . Open ( "E:\Test\TestData.xlsx" )
objWorkbook . Worksheets ( "Sheet1" ) . Range ( "D:D" ) . delete 'Provide the required range
objWorkbook . SaveAs "E:\Test\TestData.xlsx"
objWorkbook . Close
Set objWorkbook = Nothing
Set objExcel = Nothing
Conclusion
We have learned how to perform various operations on Excel SpreadSheet in UFT.Hope you will find this article helpful.Please don’t forget to share it and if you have any questions/queries, please do mention them in the comment box.
Recommended Posts
I am getting error at line number 5 for the below method.
Read data from a specific cell in Excel Sheet
Hi Rajasekhar
You have not mentioned what error you are getting. All I can suggest you please check whether you are giving a valid sheet name. Hope it will sort out your issue.