Sunday, August 11, 2013

How to Make VBA code run faster

Most of the times VBA Developer encounter this issue that there VBA code is taking lot of time to execute, hence i am posting some of the tips that will help VBA code to run faster.

1)Always make Screen Updating Value to false before running the program, it will save lot of time.
    To prove this lets write a simple program without turn off screen updating values and with turn off screen update values.

Condition1 :When Screen Updating is not mentioned by default its true.

Public Function TestVBACode()
       Program_Start_Time = Timer
               For i = 1 To 10
                    Range("A" & i).value = "Hello VBA"
               Next i
        MsgBox Program_Start_Time - Start & "Seconds"
 End Function
Condition2:When Screen Updating is mentioned as false.

Public Function TestVBACode()
       Application.ScreenUpdating   = False
             Program_Start_Time = Timer
                     For i = 1 To 10
                           Range("A" & i).value = "Hello VBA"
                    Next i
         Application.Screen Updating = True

       MsgBox Program_Start_Time - Start & "Seconds"

End Function


Sunday, August 4, 2013

Mobile Testing using QTP(UFT 11.5)

I found one link very much suitable for Mobile Automation Testing using QTP and hence thought to share the same with the community.

This is Pefecto mobile addin and very easy to use with QTP.Below link will guide you step by step and also specify the benefit of it.

Mobile Automation Testing using QTP

Monday, July 22, 2013

VB Script to Automate Internet Explorer without using any tool

In this post i will describe you how vb script can be used for automating internet explorer without using any tool.
The below mentioned text will open internet explorer and then google page put some text in the Text box.

Steps to follow:

Step 1:Open the notepad file

Step 2:Copy the below mentioned code and save the file with the .vbs extension.

Option Explicit

strText = "My VB Script"  'Enter some sample  Text over here.

Dim objLink , ie , WshShell , colLinks
Dim i , intDay , intOff , intcount , btn, strText, j
Dim strUser , strPwd , hwnd, Wnd, oShell, objImage, objImages , strOut

Sub WaitForLoad(obj)
   Do While ie.Busy
   Do While obj.readyState <> 4
End Sub

'Create ie and shell object
Set ie = WScript.CreateObject("InternetExplorer.Application")

Set WshShell = WScript.CreateObject("WScript.Shell")

'set the ie properties whichever you want that your internet explorer should contain.
ie.ToolBar = 1
ie.StatusBar = 1
ie.Width = 1500
ie.Height = 999
ie.Left = 0
ie.Top = 0
ie.Visible = 1

With ie
hwnd = .hwnd
      .Navigate ""  'Enter URL  here that you want to work on.
End With

Set oShell = CreateObject("Shell.Application")
For Each Wnd In oShell.Windows
        If hwnd = Wnd.hwnd Then Set ie = Wnd

'wait until the page has loaded before continuing


'Enter the user details

ie.Document.getElementByID("gbqfq").Value = strText 'Inspect it by using any inspect tool that you have.

Monday, July 15, 2013

VB Script || QC Code || QTP || Upload an attachment file and Send attachment file in mail from test plan automatically.

Below is the code that i used for sending mail from Quality Centre to the recipient list.The attachment is sent from Test Plan.

FilePath = ""    'File Path where your file is located.

Call UpLoadAttachmentToQC(FilePath)        ' Call the function to upload the attachment

Function UpLoadAttachmentToQC(FilePath)

Set ObjCurrentTest = QCUtil.CurrentTest.Attachments 'Creating QC Util Object with attachment

Set ObjAttch = ObjCurrentTest.AddItem(Null)    'Object for Current Test Set

ObjAttch.FileName = FilePath        'Attaching the File in the Object

ObjAttch.Type = 1  

ObjAttch.Post    'Post your Attachment to the QC

ObjAttch.Refresh   'Refresh the Object

Set attachFact = QCUtil.CurrentTest.Attachments 

'Here i have created attachment factory object which is associated with QC and it will fetch all the attachment from the current Test Plan and store it in attachList array of element.

Set attachList = attachFact.NewList("")          
ReDim Preserve AttachmentList(attachList.Count - 1)

  Dim i
  i = LBound(AttachmentList)

'Running loop for each attachment and uploading them from QC to an array.

   For Each theAttachment In attachList
         AttachmentList(i) = theAttachment.ServerFileName
          i = i + 1

Dim fName

  For Each fName In AttachmentList
      MsgBox "The AttachmentList element is: " & fName

Mailto = "abc@domainname"

'Use the send mail method of Quality Centre to sent mail to the concerned authorities with Attachment list array.

QCUtil.QCConnection.SendMail Mailto,,,,AttachmentList

End Function

Thursday, July 4, 2013

Excel QC Addin || Download And install

Most of the times i heard from tester how to use Excel Addin for QC integration. I have listed down the simple steps that would be easily help you to download and install excel addin which is really beneficial.

Step1: Download Excel Addin from the link mentioned below.
Download QC Addin for ALM

Step2: Install the addin on the machine which have QC configured with server link and make sure that any open excel is closed.

Step3:After Successful installation verify the Addin ribbon at the top of the Toolbar.

Step4:It Should show the option "Export to Quality Center"

Step5:Clicking on it wizard must open up successfully.

Step6: Congratulation you have installed the addin perfectly.

The Addin is used only to export the following components from Excel to QC.
  • Requirements
  • Defects
  • Test

If you still encounter any Problem....then let me know i would be happy to assist you.

HP Service Test Integration with ALM

Service Test integrates with ALM, the HP centralized quality solution. ALM helps you maintain a project of all kinds of tests (such as tests, components, business process tests, manual tests, tests created using other HP products,and so on) that cover all aspects of your application’s functionality. Each test or component in your project is designed to fulfill a specified testing requirement of your application. To meet the goals of a project, you organize the tests in your project in unique groups.
ALM provides an intuitive and efficient method for scheduling and running tests or components, collecting results, analyzing the results, and managing test and component versions. It also features a system for tracking defects,enabling you to monitor defects closely from initial detection until
At its most basic level, integrating Service Test with ALM enables you to store and access tests, components, and resource files in an ALM project, when Service Test is connected to ALM.
Service Test integrates with Service Test Management, ALM’s extension for storing and managing application components, such as Web services.Service Test Management is an extension of ALM and it provides an efficient method for storing and retrieving application components.

Sunday, June 9, 2013

Open a Test with Associated Add-ins Loaded

In this post i am going to describe how to automatically load all addin that are associated with test only.The code will open the test at run time and opens associated addins.
'This example opens a test and loads all the add-ins associated with the test.

Dim qtApp 'As QuickTest.Application ' Declare the Application object variable
Dim blnNeedChangeAddins ' Declare a flag for indicating whether the test's 

                        'associated add-ins are currently loaded
Dim arrTestAddins ' Declare the variable for storing the test's associated add-ins

Set qtApp = CreateObject("QuickTest.Application") ' Create the Application object

' Create an array containing the list of addins associated with this test
'Change Location as per your test
arrTestAddins = qtApp.GetAssociatedAddinsForTest("C:\Tests\Test1")

' Check if all required add-ins are all already loaded
blnNeedChangeAddins = False ' Assume no change is necessary
For Each testAddin In arrTestAddins ' Iterate over the test's associated 

                                    ' add-ins list
    If qtApp.Addins(testAddin).Status <> "Active" Then ' If an associated add-in is 

                                                        ' not loaded
        blnNeedChangeAddins = True ' Indicate that a change in the loaded add-ins 

                                   'is necessary
        Exit For ' Exit the loop
    End If

If qtApp.Launched And blnNeedChangeAddins Then
        qtApp.Quit ' If a change is necessary, exit QuickTest to 

                   '  modify the loaded add-ins
End If

If blnNeedChangeAddins Then
    Dim blnActivateOK

'Load the add-ins associated with the test and check whether they load successfully.
    blnActivateOK = qtApp.SetActiveAddins(arrTestAddins, errorDescription)
    If Not blnActivateOK Then ' If a problem occurs while loading the add-ins
        MsgBox errorDescription ' Show a message containing the error
    WScript.Quit ' And end the automation program.
    End If
End If

If Not qtApp.Launched Then ' If QuickTest is not yet open
    qtApp.Launch ' Start QuickTest (with the correct add-ins loaded)
End If
qtApp.Visible = True ' Make the QuickTest application visible

qtApp.Open "C:\Tests\Test1" ' Open the test
Set qtApp = Nothing ' Release the Application object

Wednesday, June 5, 2013

QTP || Check the Test Run Result using VB Script

'This example opens a test, configures run options and settings,
'runs the test, and then checks the results of the test run.

Dim qtApp 'As QuickTest.Application ' Declare the Application object variable
Dim qtTest 'As QuickTest.Test ' Declare a Test object variable
Dim qtResultsOpt 'As QuickTest.RunResultsOptions ' Declare a Run Results Options object variable

Set qtApp = CreateObject("QuickTest.Application") ' Create the Application object
qtApp.Launch ' Start QuickTest
qtApp.Visible = True ' Make the QuickTest application visible

' Set QuickTest run options

qtApp.Options.Run.ImageCaptureForTestResults = "OnError"

qtApp.Options.Run.RunMode = "Fast"
qtApp.Options.Run.ViewResults = False

qtApp.Open "C:\Tests\Test1", True ' Open the test in read-only mode

' set run settings for the test
Set qtTest = qtApp.Test
qtTest.Settings.Run.IterationMode = "rngIterations" ' Run only iterations 2 to 4
qtTest.Settings.Run.StartIteration = 2
qtTest.Settings.Run.EndIteration = 4
qtTest.Settings.Run.OnError = "NextStep" ' Instruct QuickTest to perform next step when error occurs

Set qtResultsOpt = CreateObject("QuickTest.RunResultsOptions") ' Create the Run Results Options object
qtResultsOpt.ResultsLocation = "C:\Tests\Test1\Res1" ' Set the results location

qtTest.Run qtResultsOpt ' Run the test

MsgBox qtTest.LastRunResults.Status ' Check the results of the test run
qtTest.Close ' Close the test

Set qtResultsOpt = Nothing ' Release the Run Results Options object
Set qtTest = Nothing ' Release the Test object
Set qtApp = Nothing ' Release the Application object

Monday, June 3, 2013

VBA code for Generating defects from QC without Queries

'Below mentioned is the code that can be utilized to get connected with QC and also generating the defects from 'the QC using VBA code
'This function will help you to get connected with Qualitty Center
Public Function QualityCenterConnection(ByVal qserver, qDomainName, qProjectname)
qusername = "username"
qpassword = "password"
Dim QCConnection
'Return the TDConnection object.
Set QCConnection = CreateObject("TDApiOle80.TDConnection")

QCConnection.InitConnectionEx qserver

'Login into Username and password

' Set the column names.

QCConnection.login qusername, qpassword
''Pass Domain Name and Project Name
QCConnection.Connect qDomainName, qProjectname
Set ConnectToQC = QCConnection

End function

Public Function exportreport()
Dim qserver As String
Dim qusername As String
Dim qpassword As String

qserver = "http://server name/qcbin"

qDomainName = "Domain Name"

qProjectname = "Project Name"

Dim QCConnection

Set QCConnection = QualityCenterConnection(qserver, qDomainName, qProjectname)

Set bfact = QCConnection.BugFactory

' Get a list of all the defects.
Dim BugFactory, BugList
Set BugFactory = QCConnection.BugFactory
Set BugList = BugFactory.NewList("")
'You can choose any other value that you want as per your need and requirements.
Cells(1, 1).Value = "Bug Id"
Cells(1, 2).Value = "Summary"
Cells(1, 3).Value = "Detected By"
Cells(1, 4).Value = "Priority"
Cells(1, 5).Value = "Status"
Cells(1, 6).Value = "Assigned To"
' Retrieve the values of each bug in excel sheet
Dim Bug, Row
Row = 2
For Each Bug In BugList
   Cells(Row, 1).Value = Bug.Field("BG_BUG_ID")
   Cells(Row, 2).Value = Bug.Summary
   Cells(Row, 3).Value = Bug.DetectedBy
   Cells(Row, 4).Value = Bug.Priority
   Cells(Row, 5).Value = Bug.Status
   Cells(Row, 6).Value = Bug.AssignedTo
     Row = Row + 1

End function

Thursday, May 16, 2013

Simple VB Script code to prevent my system from locking

This is common problem that we encounter that we have automate our suite and run the same at any given time.

However we can schedule the scripts using windows scheduler or other alternative but problem arises when we need to run the scripts unattended.

For that i have developed and tested a vb script that will not attack on existing running functionality and also prevent the system from locking.

'Step 1:Create WScript Object
set wscrpt = CreateObject("WScript.Shell")

'Step 2:Run the Loop so that system repeated again and again.

WScript.Sleep (10*1000)  'Set the timer for number of seconds.Here i am setting 10 seconds.

wsc.SendKeys ("{SCROLLLOCK 2}") 'Use Send Keys to press the keyboard control.

Loop  ' End Loop

Steps To run above Code

Step 1:Copy the above code into notepad file as it is.

Step 2:Save the file with any logical name you want but with .vbs extension to convert the notepad file into vb script executable file.

Step 3:Double click the the file it will start running.

Sunday, May 12, 2013

Simple Way of Adding Defects to Quality Center

Below is the Simple and working code that will log defect in Quality whenever encountered by your scripts.

Connects to Quality Center from a Quick Test test and adds a bug to the database. 

Dim TDConnection
Set TDConnection = CreateObject("TDApiOle.TDConnection")

TDConnection.InitConnection "http://yovav/tdbin" ' URL for the DB 'Enter the URL as per your server.

TDConnection.ConnectProject "TD76","bella","pino"  ' Valid login information.Change Parameter like User Name and Password.
If TDConnection.Connected Then
    MsgBox("Connected to " + chr (13) + "Server " + TDConnection.ServerName _
    + chr (13) +"Project " + TDConnection.ProjectName )
    MsgBox("Not Connected")
End If

'Get the IBugFactory
 Set BugFactory = TDConnection.BugFactory

'Add a new empty bug

Set Bug = BugFactory.AddItem (Nothing) 

'Fill the bug with relevant parameters

Bug.Status = "New"

Bug.Summary = "Connecting to TD"

Bug.Priority = "4-Very High" ' depends on the DB

Bug.AssignedTo = "admin" ' user that must exist in the DB's users list'

Bug.DetectedBy = "admin" ' user that must exist in the DB's users list
'Post the bug to database (commit)

Sending Keyboard Input to an Application

While using the QTP I came across the scenario where i need to press my keyboard keys and for that i found Device replay more suitable.

The Code lines that can be used to press keyboard keys are :

Set obj = CreateObject("Mercury.DeviceReplay")


obj.PressKey 63 

The PressKey method uses the ASCII value for the key.
63 is the ASCII value for F5.
ASCII values for other keys:
F1 - 59
F2 - 60
F3 - 61
F4 - 62
F5 - 63
F6 - 64
F7 - 65
F8 - 66
F9 - 67
F10 - 68
F11 - 87
F12 - 88

QTP Normalize String - Convert a String into Regular Expression using VB Script

'This function will convert a Normal String into Regular expression that can be easily utilized with your QTP 'Script.Hence the name of the function provided as Normalize String.
'It is Reusable code can be utilized as it is.

Function NormalizeString(OrgStr)

    Dim TempStr

    TempStr = Replace(OrgStr, "\", "\\")

    TempStr = Replace(TempStr, "*", "\*")

    TempStr = Replace(TempStr, "+", "\+")

    TempStr = Replace(TempStr, ".", "\.")

    NormalizeString = Replace(TempStr, "?", "\?")

End function

msgbox NormalizeString ("a+b*c.d?e")

'If anyone have any question in this kindly post them, I would be happy to resolve them.

Tuesday, April 30, 2013

Some Useful Tips for QTP

  • Listed down are some of the useful QTP Tips.

  • Always start first then your application to automate.
  • Try to avoid the Text Checkpoints, Recovery Scenarios file and other similar functions that deal with screen resolution as this may fail your test in the long run.
  • Load only those Addin that are required for your application, loading extra AddIn slows down your application.
  • Turn off Run >> Results option while scripting the aplication as it will fasten the execution process.

Wednesday, April 24, 2013

VBA - Create Hyperlink Using VBA Coding on click of button.

 This Post will help you in creating hyperlink dynamically using VBA coding.

Requirement: My Client wants to create hyperlinks dynamically with a click of button with the source value present in column two "Link Source" and the name of link is present in Column 1 with heading "Link Name" and the hyperlink must be created in third column with heading "Final Hyper Link"

To Run the VBA code I have created a button called "Create Hyperlink" Clicking on it will create the hyperlinks in Final Hyper Link Column.

'Function Name: CreateHyperlink
'Author Name: Anshu Khandelwal
'Input : From Excel Work Book as link name and link source.
'This function can be run after calling this function on Button or Directly running it

Public Function CreateHyperlink()
Dim link_name As String
Dim link_value As String
Dim i As Integer

'Using Count to work on Dynamic Records.
 Count = Application.WorksheetFunction.CountA(Range("A:A"))

For i = 2 To Count
    link_name = Cells(i, 1).Value   'Picking up link_name value from Column 1
    link_source = Cells(i, 2).Value 'Picking up link_source value from Column
    Cells(i, 3).Select   'Select a Cell where you want Hyperlink to display
 'Here i am adding hyperlinks with ActiveSheet reference and Passing SubAddress in the form '
 of link_source and Texttodisplay using link_name variable which contains values from excel workbook.
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=link_source, _
Next i

End Function

Note: Anyone can directly use this code just copy and paste in your module and change value in your excel as per your need.

Monday, April 15, 2013

Important VBA(WorkSheet) Functions with Example

Listed down are some of the important VBA worksheet functions that we can use in Day to Day Automation.:

  • Command to Activate a Sheet:Sheets(Sheet_name).Activate
                     Public Function SheetActivateExample()
                      End Function

  • Activate a Sheet with the help of index property:Sheets(index).Activate            
                 Public Function SheetActivateExample()
                 End Function

  • Count the number of Sheets present in your excel workbook.In Excel the indexing start from 1 not by 0.To count till end of the Sheet function is Sheets.count
                Public Function Sheet_count()
                      Dim i as integer
                          For i = 1 to Sheets.count
                                  'It will loop you to the end of Sheets.
                         Next i
               End function

Sunday, April 7, 2013

Visual Basic For Application for Office 2007,2013

                                Running Office 2007 VBA code in office 2010 and 2013.
If you are trying to run VBA code built in 2007 or earlier version of office into 2010 and 2013  
office then you need to take note of the following points

If variable not declared in office 2007 VBA code then your code will not going to be run in office 2010 or 2013 code.