UFT\QTP Learner

Saturday, 21 October 2017

Excel" Finding the Number of Used Rows

Dim oexl
Set oexl=CreateObject("Excel.Application")
Set owb= oexl.Workbooks.Open("D:\uftCTexit\NandaHybrid\testarea\Range Object.xlsx")
Set objWS=owb.Worksheets("Sheet1")

Set objRange = objWS.Range("A1").CurrentRegion
Msgbox objRange.Rows.Count

=>4

Msgbox objWS.Usedrange.Rows.count
=>4

'msgbox objWS.Cells(3000, 1).End(xlUp).row
'Rem -4162 is enumeration for xlup
msgbox objWS.Cells(3000, 1).End(-4162).row

=>6

owb.Close

set owb=nothing
oexl.Quit

'Add Extra spaces in workbooks after last row

Msgbox objRange.Rows.Count
=>4

Msgbox objWS.Usedrange.Rows.count
=>6

Thursday, 19 October 2017

Vbscript: Finding count of files by file extension

Dim fso
Set fso = CreateObject("scripting.filesystemobject")
vbfilecount=0
xlsxfilecount=0
txtfilecount=0

Set ofolder=fso.GetFolder("C:\Users\sunshine\Desktop\vbscriptprgm\SeparateFiles by Extension\")
set filecol=ofolder.Files
Print "Total files are: "&filecol.Count

For each filename in filecol
    Print filename.Name
    fullname=filename.Name
    If instr(1,fullname,".vbs")>0 Then
    vbfilecount=vbfilecount+1

    ElseIf instr(1,fullname,".xlsx")>0 Then
    xlsxfilecount=xlsxfilecount+1

    ElseIf instr(1,fullname,".txt")>0 Then
    txtfilecount=txtfilecount+1

    End If
Next

Print "vbfilecount is "&vbfilecount
Print "xlsxfilecount is "&xlsxfilecount
Print "txtfilecount is "&txtfilecount

Set fso=nothing

------Output-------------------------

Total files are: 6
BlankFile.vbs
New Microsoft Office Excel Worksheet.xlsx
New Text Document (2).txt
New Text Document.txt
test.txt
xlfile.xlsx
vbfilecount is 1
xlsxfilecount is 2
txtfilecount is 3

Tuesday, 17 October 2017

UFT WebTable Descriptive Program to find occurance of a name

Set owebel=description.Create
owebel("micclass").value="WebElement"
owebel("html tag").value="TD"
count=0

set webelecol=Browser("Buy Women Clothing Online:").Page("SamplePage").WebTable("empid").ChildObjects(owebel)

For i = 0 To wt.count-1 Step 1

If webelecol(i).GetroProperty("innertext")="vura" Then
    count=count+1
End If

Next

msgbox count

========Sample Page

<Html>

<Head>
<Title>Sample Page </Title>
</Head>

<Body>
<Form>
Username:<input type="text" value=""/><br>
Password: <input type="text" value=""/><br>

Gender:<Br>
Male <input type="radio" name="grp" value="Male" Checked><br>
Female<input type="radio" name="grp" value="Female" ><br>

Indian<input type="radio" name="country" value="Indian Citizen" ><br>
Accept Condition<input type="checkbox" name="accept_condition" value="Indian Citizen" ><br>
Link: <a href=http://www.Google.com/ target="_blank">Googlei<br></a>

</Form>
<br>
<br>
<br>
<br>

<table border=1 cellpadding=3 cellspacing=3 name="tableemp" >
<tr>
   <th></th>
   <th>empid</th>
   <th>empname</th>
   <th>department</th>
   <th>joindate</th>
   <th>city</th>
</tr>

<tr>
   <td> <input type="checkbox" name="accept_condition" value="Indian Citizen" ></td>
   <td>101</td>
   <td>sumit</td>
   <td>uat</td>

   <td>29 sep 2014</td>
   <td>mumbai</td>
</tr>

<tr>
   <td> <input type="checkbox" name="accept_condition" value="Indian Citizen" ></td>
   <td>102</td>
   <td>vura</td>
   <td>qa</td>

   <td>29 sep 2016</td>
   <td>hyd</td>
</tr>
</table>
<br>
<br>

</Body>

</html>

Monday, 16 October 2017

UFT and XML

REM:Sample File:

REM:Stored location:"C:\Users\sunshine\Desktop\vbscriptprgm\samplexmlnanda.xml"

<Resources>
<Module1>
<Function_Libararies>
<Library>module1lib1.vbs</Library>
<Library>module1lib1</Library>
<Library>module1lib1</Library>
</Function_Libararies>

<Repositories>
<Repository>module1repo.tsr</Repository>
<Repository>module2repo.tsr</Repository>
   </Repositories>
</Module1>

<Module2>

<Function_Libararies>
<Library>module2lib2.vbs</Library>
<Library>module2lib2</Library>
<Library>module2lib2</Library>
</Function_Libararies>

<Repositories>
<Repository>module2repo.tsr</Repository>
<Repository>module2repo.tsr</Repository>
   </Repositories>
</Module2>

</Resources>

REM:Code to Read File:

Dim Modules
Modules=Array("Module1","Module2")

Const XMLDataFile="C:\Users\sunshine\Desktop\vbscriptprgm\samplexmlnanda.xml"
Set oxml= CreateObject("Microsoft.XMLDOM")
oxml.Async = False
oxml.Load(XMLDataFile)

If oxml.Parseerror.Errorcode<>0 Then

msgbox "Error Parsing XMl"

End If

For i=lbound(Modules) To ubound(Modules) Step 1

parent=cstr(Modules(i))

Set Col=oxml.getElementsByTagName(parent).Item(0)

Set olibcol=Col.getElementsByTagName("Function_Libararies").Item(0)

Set lib=olibcol.Childnodes

For each  strlib in lib

msgbox strlib.text

Next
Next

VBscript: Finding Occurance of Each Character in the string

Dim a()
Dim strtext
'Provide the text
strtext="this is sparta"

'Create An Array with each letter of the text
ReDim a((len(strtext)-1))
For i=1 to len(strtext) Step 1
a(i-1)=mid(strtext,i,1)
Next

'Create Dictionary Object to store unique values of the text

Set odic=CreateObject("Scripting.Dictionary")
'ReDim a((len(strtext)-1))
For i=1 to len(strtext) Step 1

If NOT odic.Exists(mid(strtext,i,1)) Then
    odic.add (mid(strtext,i,1)),"i"
End If

Next

alluniquekeys=odic.Keys

'Compare unique items with each item present in the array and increase the counter when there is a match
For i = 0 To ubound(alluniquekeys) Step 1
    count=0
    For j = 0 To ubound(a) Step 1

        If alluniquekeys(i)=a(j) Then
        count=count+1
        End If
    Next
    print " "&a(i)& "appears "&count&"times"
Next

REM===> OUTPUT
t appears 2 times
h appears 1 times
i appears 2 times
s appears 3 times
   appears 2 times
p appears 1 times
a appears 2 times
r appears 1 times

Saturday, 14 October 2017

UFT: Connecting to SQL SERVER with ADODB

Set con=CreateObject("ADODB.connection")
Set Rs=CreateObject("ADODB.Recordset")

On Error Resume next
con.Open "Provider = sqloledb.1;Server=SUNSHINE-PC;Database=northwind;User Id=sa;Password=password;"

rs.open "select * from [dbo].[Categories]",con

While rs.EOF<>True

msgbox rs.Fields.Item(0)&" "&rs.Fields.Item(1)

rs.MoveNext


Wend

If Err.Num<>0 Then

msgbox "Something Wrong with ADOBDB"
End If

rs.Close
con.Close

Set rs=nothing
Set con=nothing

RegisterUserFunc in UFT

RegisterUserFunc "WebEdit", "fnWebEditSet", "fnWebEditSet"
RegisterUserFunc "WebEdit", "fnWebEditSetSecure", "fnWebEditSetSecure"

Function fnWebEditSet(objControl,objControlText)

If objControl.Exist(10) Then

           If objControl.GetRoProperty("Disabled")=0 Then
           objControl.Set objControlText

           Reporter.ReportEvent micPass,"WebEdit Set",""&objControl.ToString()&" "&"value captured"

           Else
           Reporter.ReportEvent micFail,"WebEdit Set",""&objControl.ToString()&" "&"is in disabled state"

           End If

Else
Reporter.ReportEvent micFail,"WebEdit Check",""&objControl.ToString()&" "&" does not exist"
End If

End Function

Function fnWebEditSetSecure(objControl,objControlText)

If objControl.Exist(10) Then

           If objControl.GetRoProperty("Disabled")=0 Then
           objControl.SetSecure objControlText

           Reporter.ReportEvent micPass,"WebEdit Set",""&objControl.ToString()&" "&"value captured"

           Else
           Reporter.ReportEvent micFail,"WebEdit Set",""&objControl.ToString()&" "&"is in disabled state"

           End If

Else

Reporter.ReportEvent micFail,"WebEdit Check",""&objControl.ToString()&" "&" does not exist"

End If

End Function

'Browser("Buy Women Clothing Online:").Page("Buy Women Clothing Online:").WebEdit("edsrchword").fnWebEditSet "sumit"

Browser("Buy Women Clothing Online:").Page("Buy Women Clothing Online:").WebEdit("edsrchword").fnWebEditSetSecure "x"