How to retrieve data from Excel file using ADODB

The below code retrieves data from Sheet1 from an Excel file using ADODB.

Both the connection strings specified here works.

Set oCmd=createobject("ADODB.Command")
Set oRS=createobject("ADODB.RecordSet")

sCon="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=D:\Programming Samples\QTP\SampleXL.xls;"
'sCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Programming Samples\QTP\SampleXL.xls;Extended Properties=""Excel 8.0;"""
sQry="select * from [Sheet1$]"

oCmd.ActiveConnection = sCon
oCmd.CommandText=sQry
Set oRS=oCmd.Execute
While not oRS.EOF
    print oRS.Fields(1).Value
    oRS.MoveNext
Wend

oRS.Close

Set oCmd=nothing
Set oRS=nothing

How to verify the existance of an environmental variable

The below code illustrate the existence of an environmental variables:

function checkEnvironmentalVariableExists(sEnvVarName)
    Err.clear
    On error resume next
    envValue=Environment.Value(sEnvVarName) 'Env exist
    If Err.number<>0 Then
        checkEnvironmentalVariableExists=False
    else
        checkEnvironmentalVariableExists=true
    End If
    On error goto 0
end function

Environment.Value("name")="Uday"
retVal=checkEnvironmentalVariableExists("name123")
If retVal=true Then
    print "Environmental Variable exists"
else
    print "Environmenta Variable does not exist"
End If

How to check a browser window is minimized

Extern.Declare micLong, "GetMainWindow", "user32" ,"GetAncestor",micLong, micLong 'This is the declaration for the referencing "GetMainWindow" with the GetAncestor method in user32.dll.
GA_ROOT=2

Just opened Gmail and checked whether the browser is minimized or maximized.

We cannot directly use Browser().GetROProperty("minimized") here.


Set oBrowser=description.Create
oBrowser("micclass").value="Browser"
oBrowser("name").value="Gmail.*"

hwnd=Browser(oBrowser).GetROProperty("hwnd")

hwnd = Extern.GetMainWindow(hwnd , GA_ROOT)
msgbox Window("hwnd:=" & hwnd ).GetROProperty("minimized")

The above code returns False if the Tab/browser is maximized else
returns True if the Tab/browser is minimized

The above code worked well with QTP and IE 7.

How to display occurances of a string in a Excel file

Following code helps find the occurrences of a string in a Excel file.

It will return the count as 0, if the string is not found
else returns the number of occurrences of the string

Dim oXLObj,oXLWBObj,olXLWSObj

Function FindStringOccuranceCount(sFileName,iSheetId,sSearchString)
    iCount=0
    set oXLObj=createobject("Excel.Application")
    Set oXLWBObj=oXLObj.workbooks.open(sFileName)
    Set olXLWSObj=oXLWBObj.worksheets(1)

    set cell=olXLWSObj.Range("A:Z").find(sSearchString)
   

    If cell is nothing Then
        CloseExcel()
        FindStringOccuranceCount=iCount
        Exit Function
    End If

    sFirstAddress=cell.address

    Do
        set cell=olXLWSObj.Range("A:Z").FindNext(cell)
        'set CurCell=olXLWSObj.UsedRange.FindNext(sSearchString)
        sCurrentAddress=cell.address
        'sCurrentAddress
        iCount=iCount+1
    loop while not cell is nothing and sCurrentAddress<>sFirstAddress

    CloseExcel()
    FindStringOccuranceCount=iCount

End Function

Function CloseExcel()
    oXLWBObj.close
    oXLObj.application.quit
    Set oXLWBObj=nothing
    Set oXLObj=nothing
End Function

x=FindStringOccuranceCount("C:\Test1.xls",1,"Uday")
msgbox x

Sample Excel file is here:

QTP built-in environmental variables

I found people forget the all the built-in environmental variables, so thought of putting all built-in variables here in this post.

ActionIteration - Indicates which action iteration is currently running.
ActionName - Indicates which action is currently running
ControllerHostName - the name of the computer which serves as a controller
GroupName - The scenario identification number
LocalHostName - Local Host Name
OS - Operation System
OSVersion - Operating system version
ProductDir - folder path where the product is installed
ProductName - Product Name
ProductVer - Product Version
ResultDir - Folder path where the results are saved
Scenarioid - The scenario identification number
SystemTempDir - System Temporary Directory
TestDir - Path of the Test
TestIteration - Indicates which test iteration is currently running
TestName - The name of the test
UpdatingActiveScreen -
UpdatingCheckpoints -
UpdatingTODescriptions -
UserName - Windows Login User Name
VUserId -