How to call a function existing in a DLL file using QTP?

Generally we cannot directly open the DLL files in notepad or in some editors to check the functions in a DLL.
We can use Dependency Walker or PE Explorer to open the DLL files to check the functions.

Here is the method to call a function existing in a DLL file:
We can use Extern object which enables you to declare calls to external procedures from an external dynamic link library.
Once you use the Declare method for a method, you can use the Extern object to call the declared method.

Syntax:
Extern.Declare(RetType, MethodName, LibName, Alias [, ArgType(s)])

RetType - Data type of the value returned by the method.
MethodName - Any valid procedure name.
LibName - Name of the DLL or code resource that contains the declared procedure.
Alias - Name of the procedure in the DLL or code resource.
Note: DLL entry points are case sensitive.
Note: If Alias is an empty string, MethodName is used as the Alias.

Example:
The following example uses the Extern.Declare and Extern. methods to change the title of the Notepad window.
'Declare FindWindow method
Extern.Declare micHwnd, "FindWindow", "user32.dll", "FindWindowA", micString, micString
'Declare SetWindowText method
Extern.Declare micLong, "SetWindowText", "user32.dll", "SetWindowTextA", micHwnd, micString
'Get HWND of the Notepad window
hwnd = Extern.FindWindow("Notepad", vbNullString)
if hwnd = 0 then
MsgBox "Notepad window not found"
end if
'Change the title of the notepad window
res = Extern.SetWindowText(hwnd, "Uday")

To check the above code, open one blank notepad file.
Observe the title of the notepad window will change to Uday.

How to pass parameters(input & output) between actions?

Here i want to pass two values from Action1 to Action2. In Action2 i am defining input parameters and output parameters. I do some operation and return the value as output parameter.

1. Start a new test
2. Create a new action by choosing Insert Menu -> Call to New Action
3. Choose "Reusable action" checkbox for Action2 and click Ok.
4. Now you are in Action2, now choose Edit Menu -> Action -> Action Properties and click on parameter tab.
Add two inpur parameters. To do this click on "+" sign.
Give the first parameter name as "a" and type as number.
Add another parameter with name as "b" and type as number.
In the output parameter add a parameter name with "result" of type number and click on ok button.
If any warning message displayed click on Yes button.
5. Do whatever operation you want in Action2. Here i want to add these values and assign to output parameter value soI am adding one statement called
parameter("result")=Parameter("a")+Parameter("b")
6. Now in the Keyword view, if Action2 is displayed, then right click on that action and choose delete. Choose "Delete the selected call to the action" and click ok button.
7. Now swith to Expert view and choose Action1.
8. Include the following two statements in Action1.
RunAction "Action2",0,34,54,var '(Here 34 and 54 vare const values i am supplying as input parameters to action2 and the result i am saving in 'var' variable).
msgbox var

How to find the number of rows and columns used in an Excel sheet

strFilePath="D:\EmpDetails.xls"
Dim fileSysObj
Set xlObj=createobject("Excel.application")
Set xlWorkBookObj=xlObj.workbooks.open(strFilePath)
Set xlWorkSheetObj=xlWorkBookObj.worksheets(1)

intRowCount=xlWorkSheetObj.UsedRange.rows.count 'intRowCount=xlWorkSheetObj.rows.count - it returns the number of rown in an Excel sheet

intColCount=xlWorkSheetObj.UsedRange.columns.count
' intColCount=xlWorkSheetObj.columns.count - it returns the number of columns in an Excel sheet

msgbox "No. of rows used "&intRowCount&" and No. of columns used. "&intColCount

xlWorkBookObj.close
xlObj.application.quit

Set xlObj=nothing
Set xlWorkBookObj=nothing
Set xlWorkSheetObj=nothing

Working with Excel functions in QTP

strXLFilePath="H:\Sample VBScripts\SampleXL.xls"
rowId=6
colId=6
sheetId=2
setValue="Senior Member Technical"

Set fileSysObj=CreateObject("Scripting.filesystemobject")
result=getCellValue(strXLFilePath,rowId,colId,sheetId)
MsgBox(result)

setXLCellValue strXLFilePath,rowId,colId,sheetId,
setValueresult=getCellValue(strXLFilePath,rowId,colId,sheetId)
MsgBox(result)

temp=ComputeMacro(strXLFilePath,sheetId,rowId,colId)

Public Function getCellValue(strXLFilePath,rowId,colId,sheetId)
Set xlObj=CreateObject("Excel.Application")
If fileSysObj.FileExists(strXLFilePath) Then
Set xlWorkBookObj=xlObj.workbooks.open(strXLFilePath)
Else
MsgBox("The specified Excel file does not exist")
End If
Set xlWorkSheetObj=xlWorkBookObj.worksheets(sheetId)
returnData=xlWorkSheetObj.cells(rowId,colId).value
getCellValue=returnData
xlWorkBookObj.close
xlObj.application.quit
Set xlObj=Nothing
Set xlWorkBookObj=Nothing
Set xlWorkSheetObj=nothing
End Function

Public sub setXLCellValue(strXLFilePath,rowId,colId,sheetId,setValue)
Set xlObj=CreateObject("Excel.Application")
If fileSysObj.FileExists(strXLFilePath) Then
Set xlWorkBookObj=xlObj.workbooks.open(strXLFilePath)
Else
MsgBox("The specified Excel file does not exist")
End If
Set xlWorkSheetObj=xlWorkBookObj.worksheets(sheetId)
xlWorkSheetObj.cells(rowId,colId).value=setValue
xlWorkBookObj.save
xlWorkBookObj.close
xlObj.application.quit
Set xlObj=Nothing
Set xlWorkBookObj=Nothing
Set xlWorkSheetObj=nothing
End sub
Public Function ComputeMacro(fileName,SheetName,rowId,colId)
Set xlObj=CreateObject("Excel.Application")
If fileSysObj.fileexists(fileName) Then
Set xlWorkBookObj=xlObj.workbooks.open(strXLFilePath)
Else
MsgBox("The specified Excel file does not exist")
End If
Set xlWorkSheetObj=xlWorkBookObj.worksheets(sheetId)
cellValue=xlWorkSheetObj.cells(rowId,colId).value
ComputeMacro=cellValue MsgBox(cellValue)
End function

ReturnDate function in QTP

'This function returns the date in the specified format
'General usage returndate(offset,format)
'Examples: returndate(NULL,NULL) displays output in 4/1/2009 format
' returndate(2,NULL) displays output in 4/3/2009 format
' returndate(2,"mm-dd-yyyy") displays output in 04-03-2009 format
' returndate(2,"dd-mm-yy") displays output in 03-04-09 format
' returndate(2,"dd/mm/yy") displays output in 03/04/09 format

retdate=returndate(2,"dd-mm-yy")
msgbox retdate

public Function returndate(intOffset,strDateFormat)
Dim retDate
If isnull(intOffset)=-1 or isnull(strDateFormat)=-1 Then
retDate=date
End If
If isnull(intOffset)<>-1 Then
retDate=CDate(Date+intOffset)
End If
If isnull(strDateFormat)<>-1 Then
Select Case ucase(strDateFormat)
Case "MM-DD-YYYY"
retDate=Cstr(Right("0"+Cstr(month(retDate)),2)) +"-" + Cstr(Right("0" & CStr(Day(retDate)), 2))+"-" + Right(CStr(Year(retDate)), 4)
Case "MM/DD/YYYY" retDate=Cstr(Right("0"+Cstr(month(retDate)),2)) +"/" + Cstr(Right("0" & CStr(Day(retDate)), 2))+"/" + Right(CStr(Year(retDate)), 4)
Case "MM-DD-YY" retDate=Cstr(Right("0"+Cstr(month(retDate)),2)) +"-" + Cstr(Right("0" & CStr(Day(retDate)), 2))+"-" + Right(CStr(Year(retDate)), 2)
Case "MM/DD/YY" retDate=Cstr(Right("0"+Cstr(month(retDate)),2)) +"/" + Cstr(Right("0" & CStr(Day(retDate)), 2))+"/" + Right(CStr(Year(retDate)), 2)
Case "DD-MM-YYYY" retDate=Cstr(Right("0" & CStr(Day(retDate)), 2))+"-" +Cstr(Right("0"+Cstr(month(retDate)),2)) +"-" + Right(CStr(Year(retDate)), 4)
Case "DD/MM/YYYY" retDate=Cstr(Right("0" & CStr(Day(retDate)), 2))+"/" +Cstr(Right("0"+Cstr(month(retDate)),2)) +"/" + Right(CStr(Year(retDate)), 4)
Case "DD-MM-YY" retDate=Cstr(Right("0" & CStr(Day(retDate)), 2))+"-" +Cstr(Right("0"+Cstr(month(retDate)),2)) +"-" + Right(CStr(Year(retDate)), 2)
Case "DD/MM/YY" retDate=Cstr(Right("0" & CStr(Day(retDate)), 2))+"/" +Cstr(Right("0"+Cstr(month(retDate)),2)) +"/" + Right(CStr(Year(retDate)), 2)
End Select
End If
returndate=retDate
End Function