How to get Column Address from column Name in Excel ?

Requirement:-To find the Address of given Column in Excel. 

In the below image we need to find the Address of “Application” column in the excel.Which is “C”.

Excel View
Excel Data


Code:-

sSheetName = "uftHelp"
sColumnName = "Application"
sPath = "C:TempTest.xlsx"
fnExcelColAddress sSheetName,sColumnName,sPath
'*******************************************Function***********************************
'Function Name:- fnExcelColAddress
'Function Description:- Fetch the Excel Column Address based on User defined Column Name
'Input Parameters:- SheetName,ColumnName,Excel Path
'Output Parameters:- None
'**************************************************************************************
Public Function fnExcelColAddress(sSheetName,sColumnName,sPath)
Dim sAddress
'Creating the Excel Object
Set objExcel = CreateObject("Excel.Application")
'Creating the Workbooks object
Set objWB = objExcel.workbooks.open(sPath)
'Creating the sheet object
Set objsheet = objwb.worksheets(sSheetName)
'Iterating the sheet to find the match
For iColumn = 1 to objsheet.usedrange.columns.count
If objsheet.Cells(1, iColumn).Value = Cstr(sColumnName) Then
sAddress = objsheet.Range("A1").Columns(iColumn).Address
sAddress = Replace(sAddress, "$1", "")
sAddress = Replace(sAddress, "$", "")
fnExcelColAddress= True
Exit For
End If
Next
If Not fnExcelColAddress Then
Msgbox "OOPS!! No Match found for the column Name = '"&sColumnName&"'",,"Address"
else
Msgbox "Column Name = '"&sColumnName&"' has Address = '"&sAddress&"'",,"Address"
End If
'closing the workbook
objWB.Save
objWB.close
Set objWB = Nothing
'Quit the Excel and destroying the Excel object
objExcel.Quit
Set objExcel=nothing
End Function

Excel object Model
Adding Hyperlink in Excel
Randomize Excel Data

How to get Field Names in ALM ?

Requirement:-We need the field names which are used in ALM , these are required to utilize the cabablities of OTA in ALM.

Fields In Test Lab
Test Lab fields

Solution:-
Approach 1:-

A. In ALM , Dashboard — Analysis View

Dashboard ALM
ALM Tabs


B.Create New Excel Report

Excel Report ALM
Excel Report


C.Under Configuration tab – – Query Builder

Configuration Tab ALM
Configuration Tab 


D. Write the following SQL query  and Run it .We have our required Field Names with Labels in ALM.

SQL Query in ALM
SQL Query


Approach 2:-


This will work incase User have the admin rights in the ALM.

A.Tools – – Customize
Customize in ALM
Customize in ALM

B.Select Project Entities

Project Entities in ALM
Project Entities

C.Under Project Entities — Select the field for which we want to check the field Name

View of Fields in Project Entities ALM
View of Fields in Project Entities

Name and Label suggest the Field Name used by ALM and Displayed to User respectively.

Reader Comments and Recommendations are most welcomed :).

Understanding Automatic Runner(ALM) status Error on running UFT scripts.

Following is the list of errors we can face while running scripts from ALM . These status error are sometimes confusing.To clarify on their meaning and comprehend them, we have written about most frequent error messages in automatic runner.

Please feel free to add errors/solutions, incase you have encountered in your interaction of UFT with ALM. 

1.Unable to load the add-ins for the testing tool.

Root Cause:- Check that UFT is having required license.

Hint :- Try to launch UFT manually and verify that UFT is not giving License error.

Automatic Runner
Status Error

2.The Remote procedure call failed.

Root Cause:- Connection with Remote Agent is disconnected.

Automatic Runner Error
Status Error
3.Unable to close the unsaved document.

Root Cause:- UFT instance is already opened with a unsaved Test in it.

Hint:-Save the Test already opened in UFT.

Automatic Runner error
Status Error
4. The RPC server is unavailable.

Root Cause: – This generally indicates a connectivity issue. Like Windows Firewall issue, Host machine is not available to run the test from ALM.For more

Automatic Runner
Status Error


Launching UFT with VAPI-XP from ALM?

Let us further enhance our code to launch UFT script from VAPI-XP in ALM. We are already familiar with “Hello World!!” code in VAPI-XP .We are going to run the same line of code but this time we are utilizing UFT to execute it.

Requirement:-
Launching and running UFT script from VAPI-XP in ALM

Implementation:-

Step1:- 
Create a simple Test in UFT with following lines of code.


Msgbox “Hello World!!”,,“My First VAPI-XP in UFT”

Step2:-
Write the following lines of code in our local txt file.
Hint:- It invokes UFT and execute the “Hello World!!” code

Hello World Test in UFT
Hello World!!

Code:-

Sub Test_Main(Debug, CurrentTestSet, CurrentTSTest, CurrentRun)
On error resume next
'Taking the UserName
Set objNW = CreateObject("Wscript.Network")
sUserName = objNW.UserName
Set objNW = Nothing
'Creating the UFT object
Set objUFT = CreateObject("QuickTest.Application")
Do While Err.Number <> 0
Err.Clear
Sleep 2000
Set objUFT = CreateObject("QuickTest.Application")
Loop
'Launch uft if not launched
If Not objUFT.Launched Then objUFT.Launch
'Make UFT visible
If objUFT.Visible = False Then objUFT.Visible = True
'Incase UFT is already running stop the excution
If objUFT.Test.IsRunning Then objUFT.Test.Stop
'Open the Test
objUFT.Open "C:Users" & sUserName & "Test"
'Run the Test
objUFT.Test.Run
'Destroying the Object
Set objUFT = Nothing
End Sub

Step3:-
Run the VAPI-XP in Test Lab and we are done.

Happy Learning!!Please feel free to share your feedback and comments.

Creating VAPI-XP code in ALM and UFT?

Now we know how to create a VAPI-XP in ALM  .Our next target is to utilize this to code and write our first workable piece of code in the form of  “Hello World!!” program :).

Implementation:-
Step1:-

Delete the default generated code in VAPI-XP testing tool.
VAPI-XP Default code
VAPI-XP default code
Step2:-
Replacing the default code with following lines of code.
VAPI-XP Code in Test Script
Code 


Step3:-

Create a local text file (Name and Location of the file should be same as we used in VAPI-XP script above).Paste the following lines of code in the .txt file.


Sub Test_Main(Debug, CurrentTestSet, CurrentTSTest, CurrentRun)
                msgbox “Hello World!!”,,”My First VAPI-XP”
End Sub

Step4:-
Select the VAPI-XP and move to Test Lab for Running.
After successfully running the script we will get the following message.
Result of VAPI-XP
Hello World!!

Running UFT using VAPI-XP

Working with VAPI-XP in ALM ?

Today we are going to discuss the basics of VAPI-XP and how to create our first VAPI-XP in ALM.

Meaning:-VAPI-XP testing tool facilitate us to create new testing scripts using Microsoft VBScript, Microsoft JavaScript (JScript version), PerlScript, and PythonScript. Furthermore we can integrate these scipts into our application management process. Using VAPI-XP test scripts, we can test COM/DCOM servers, SOAP-based Web services, Java APIs (such as Java classes and EJBs), and console applications.

VAPI-XP is also fully integrated with the ALM Open Test Architecture API. All open test architecture API classes and methods can be referenced from the VAPI-XP user interface  

Creation:-

Step1:-
Create a new Test folder inside a Test Plan in ALM

Creating New Folder in Test Plan
Creating New Folder (VAPI-XP)

Step2:-

a.Create new Test set inside the created folder Select the Type as VAPI-XP TEST.

New Test in ALM
New Test

b.Set the value of Test name

VAPI-XP Type in Test Set
Type as VAPI-XP

c.Click Finish to create a default type of VAPI

HP VAPI-XP Wizard
VAPI-XP Wizard

 Note:-We can also use VAPI-XP to create a LoadRunner virtual user.

Step3:-

Go to the Test Script tab, to write Your code in VAPI-XP
VAPI-XP Coding wizard
Code for VAPI-XP
Note:- It will have its auto generated code by default.

Step4:-
Move Your VAPI-XP file to Test Lab and we are ready to run the VAPI-XP 


Example1

How to Re-Run Test in ALM on failure?

Incase we start our search for any issue on ALM; it is really arduous to find something relevant and meaningful. Furthermore there is a scarcity of website addressing ALM issues or help. So we thought of sharing an article pertaining to “Re-Run of Test case in Test set in ALM in case of failure”.

Requirement:- 
Test Case (say TC2), fails when we ran from ALM for the first time, how we can control the execution flow , whether we want to rerun the test , test set or stop the execution?

Execution flow: – 
TC1->TC2->TC3 but TC2 is failing on first attempt and we want to re-run it to complete the execution flow.

Solution: – 
We can control the Rerun behaviour from “Automation Tab” in ALM.

Finding “Automation Tab”: – 
In ALM, Testing->Test Lab->Select the Test Set ->Automation Tab

Approach 1:- 
We can check the Rerun test option and set the “Maximum test reruns”.
Maximum test reruns: – Specifies the number of times an automated test should be rerun upon failure.
Cleanup test before rerun:-Runs a cleanup test before each test rerun.
Automation Tab in ALM
Automation Tab
Approach2:- 
What can be done in ALM  on final failure of test.? We can select the option from “On final failure”. Like “Stop the test set”

Note: – We can also configure the email settings (Execution Summary) which ALM will send after the run, we can also change it as per our need like whether to send email on failure or pass. (Notification)
Email Notification box
Email Notification in ALM
Also “Settings per test”, Opens the On Test Failure dialog box, enabling you to change the default failure rules for a test in the test set
Test Failure Box
Test Failure window

ActiveX component can’t create object: ‘Mercury.ObjectRepositoryUtil’

Recently we got this error while running our .vbs file, external to UFT. 
It is a common error that one can encounter while working with .VBS files.
Let us focus on the possible solution for the same.

Please feel free to share your experience on the same.As always their are 100 of solutions for a problem, and you never know that you are holding the best solution 🙂

Machine Details :- 64 Bit

Problem :- Microsoft VBScript runtime error (800A01AD)

ActiveX Errror
ActiveX Error Message
Solution :-

1.Get the path of Your .vbs file having the “ObjectRepositoryUtil” code
2.Open CMD prompt
3.Copy paste the following code of line,
%SystemRoot%SysWow64cscript
4. Add the File path to this, so our code of line will be
%SystemRoot%SysWow64cscript “File Path of .vbs”
5.Press Enter and we are done.Our script executes with following messages 

Command Prompt
Command Prompt

How to convert Object Repository into XML format?

We will be focusing on 2 methodologies to achieve our target.

Approach 1:- 
Using “ObjectRepositoryUtil Object

ObjectRepositoryUtil(ORU):-By using this object we can work with object Repository files (Local/Shared) from outside of UFT.



We need to use “ExportToXML” method of ORU object.

ExportToXml
ExportToXML Method

'Creating the ObjectRepositoryUtil to work with OR
Set objORU = CreateObject("Mercury.ObjectRepositoryUtil")
'Exporting the OR into new XML file
objORU.ExportToXML"C:UsersTestMe.tsr","C:UsersTestMe.XML"
'Destroying the object
Set objORU = Nothing


Note:-
Incase TargetFile, already exists UFT will give an error.

Approach 2:-
Using Object Repository manager
1.Open Object Repository manager
   Resources ->Object Repository Manager

Object Repository Manager
Object Repository Manager

2.Open the OR
 File ->open

3.Export to XML format
  File ->Export to XML

Export to XML
Export to XML


After Export we will get the information regarding the objects Exported from OR

XML export
Export Result

ActiveX Component Error

How to change Settings of Remote Agent ?

After launching the Remote Agent successfully. we can play with the settings of Remote Agent in UFT.

Approach 1:-

The Remote Agent’s icon will appear as a Taskbar Button(Check the far bottom right of the taskbar). Right click on remote agent button and select “Settings” from the context menu.

Settings Remote Agent
Remote Agent Button on Task Bar
Remote Settings Dialogbox
Remote Agent Dialog Box


Approach 2:-

Go to run (use Windows + R shortcut key)
Open remote agent setting file, “mic.ini”.

Search RemoteAgent File in UFT
Searching Remote Agent file through Run


Look for below segment to change the settings.

[RemoteAgent]
OperationWaitInterval=2700000
StatusRetrievalWaitInterval=2700000
CloseToolAfterRuns=0
RunQTPHidden=1
IdleMinutesAutoCloseTool=15

Note:-
Operation wait interval is by default is set to 45 minutes(2700 sec = 2700000 microseconds), we can change it as per our requirement.

Change RunQTPHidden=0 to RunQTPHidden=1 if you don’t want to run UFT in Hidden mode when running tests from ALM.