QTP Script to get Excelsheet Rowcount and Columncount

QTP Script To retrieve no.of columns and no.of rows in an excelsheet.


In excelsheet we have values as shown below

A B C
1 aa ll
2 bb aa
3 cc xx
4 dd mm
5 ee ii

Check This Post for Reading Specific cell value from excel sheet using VBScript in QTP

VBScript to get rowcount and columncount.

Set a= CreateObject("Excel.Application")
Set b=a.workbooks.open("d:\es.xls")
Set c=b.worksheets("Sheet1")
column_count= c.usedrange.columns.count
row_count= c.usedrange.rows.count
Msgbox "No.of Columns="&column_count
Msgbox "No.of Rows="&row_count
b.close

Result:

No.of Columns=3
No.of Rows=5

SQL Queries Collection
Movie Songs Lyrics
QTP Script to export database data to excel sheet
VBScript to Check Whether Particular File Exists or Not.
Check this Post for Datatable Methods.
Excel Sheet Creation and Writing Data Into It
Data Driven Using Excel Sheet
Data Driven Using Notepad

4 comments:

Anonymous said...

Seems like I have found some cases where the data range function is inaccurate...
As a requirement we expect any sheets to be utilized have column headings. So the following example is a function to locate a specific column to be inspected. You can see the logic here continues checking columns until the cell is empty:
Private Function XLS_FindColumn(objExcel, ColName)
Dim y
Dim strTemp

XLS_FindColumn = -1
y = 1
Err.Number = 0
Do Until objExcel.Cells(1,y).Value = ""
strTemp = objExcel.Cells(1, y).Value
If strComp(strTemp, ColName, 1) = 0 Then
XLS_FindColumn = y
Exit Do
End If
y = y+1
On Error Resume Next 'Added to make importing compatible
strTemp = objExcel.Cells(1,y).Value = ""
If Err.Number <> 0 Then Exit Function
On Error Goto 0 'Disable Error Handling
Loop
End Function

Anonymous said...

I am assuming this code checks how many columns there are in excel.

What if I know the row I am using, how do I count the number of columns that have valid data in that row and be able to retrieve each columns data per that row and use it in my QTP script?

Thanks,
Steve

Anonymous said...

i want to retrve row from excelsheet by using qtp..
like
usrname=arpita
password=pwd
port-2341

can u suggest me some answer

Anonymous said...

This works fine. But, can anyone please let me know how to do the same without opening a new instance of excel.

Related Posts Plugin for WordPress, Blogger...