Excel Handling in QTP

Hi Folks,
Handling Excel file is very impotant aspect of QTP coding .It is used in parameterization.
I am describing the process in below steps

1. Reference Object:     

 First step of excel handling is to create a reference object, as per below

Set myexcelObj=CreateObject("excel.application")

Now the myexcelObj is not actully holding anything , it is even not pointing to any excel file yet

2.Linking a workbooks(Excel File):

Second Step is to tell your excel reference object that it is gonna point to an excel file(Workbook, both is same thing).so our code will be as below 
.Please note that myexcelObj can point(Handle) more than 1 excel file.

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

3. Pointing to Worksheet:

Now the myexcelWorkbook object will point to the actual excel file , so the next step is to point the worksheet in that particular file.We can point the worksheet by index or by the sheetname as below

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

Set myexcelWorksheet=myexcelworkBook.worksheets(SheeetName)

4. Getting the Data:

Once we have reference of the workSheet , getting the data is quite easy .

Set myexcelObj=CreateObject("excel.application")

Set myexcelWorkBook =myexcelObj.workbooks.open(FilePATH)

Set myexcelWorksheet=myexcelworkBook.worksheets(SheeetName)

exceldata=myexcelWorksheet.cells(1,"A").value

             Or

exceldata=myexcelWorksheet.cells(1,1).value

Now we have received the data as "exceldata" .so the final code is as shown above

Hope this helped.

N'th Highest salary in Employee Table

Hi Folks ,
What is the most common question in any of SQL development/DB Testing Interview.

write a query for
2'nd highest salary in employee table.

Generally we use a nested query approach for this question as per below

select employee_sal,employee_name 

from employee where employee_sal=(select max(employee_sal) 

from employee where employee_sal<(select max(employee_sal) 

from employee))

Now if I ask for 3rd highest salary , then again we have to add one more level of subquery...cool...?
but if i ask for 12th highest salary then hmm????
So obviously this is not the right way to wrtite this type of query .
below is generic for Nth highest salary.
lets see how
2nd higest salary

select employee_sal,employee_name from employee emp1 
where 1=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

3rd Highest salary

select employee_sal,employee_name from employee emp1 
where 2=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

I think now you have got the idea...now
Nth highest salary

select employee_sal,employee_name from employee emp1 
where (N-1)=(select count(distinct employee_sal) from employee emp2 where 
emp2.employee_sal>emp1.employee_sal)

Hope this helped

 Please subscribe for the Blog , if this helped you