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