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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 2 3 4 5 6 7 8 9 10 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 |
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
- Read Excel Using ADODB Connection in UFT | VBA
- Reading and Updating MS Access Database Using ADODB in UFT
- File System Object UFT | VBA
- All You Need to Know About Object Identification in UFT
- DataTable in UFT One | Example of Datatable Methods
- Import Excel File into Datatable in UFT
- How To Use Dictionary Object in UFT With Examples
- VBScript MySQL Database Connection in UFT
- Read and Update XML File in UFT | VBA
- Most Useful VBScript Functions That You Must Know
- VBScript Loops in UFT
- 20 Useful VBA Date Functions in UFT You Must Know
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.