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

How to findout the QTP version you are using scripting

msgbox("Product Name: "&Environment("ProductName"))
msgbox("Product Version: "&Environment("ProductVer"))
msgbox("Product Directory: "&Environment("ProductDir"))

msgbox("Test Name: "&Environment("TestName"))
msgbox("Test Directory: "&Environment("TestDir"))

What is the present version of the VB Script we are using

Present version of VB Script is 5.6.

We can findout the version of vbscript you are using by going through the location
C:\Windows\System32, search for the file vbscript.dll, and right click on it and choose the properties. Move to the version tab.

You can findout the version number.

Can a function return dictonary object

Yes.

Functions can return a dictonary object.

Dim dicObj
Set dicObj = CreateObject("Scripting.Dictionary")

Set obj=getname
MsgBox(obj.item("name"))


Public Function getname()
dicObj.add "name","Uday Kumar"
Set getname=dicObj
End function