How to add HyperLink in Excel using UFT/QTP?

Scenario:-
How we can add “HyperLink” to a Excel Cell.
We used this thing while “Reporting” UFT results.Hyperlinks was used as pointer to screenshot of Results after the execution of Scripts.

Method 1

Logic:- We’ll use HyperLinks.Add method of Excel worksheet to create a Hyperlink.
Step1:- Activate the particular cell that we want as “Hyperlink”
Step2:- Add the Destination path and Text to be displayed to User in Excel

Code:-
For basics of Excel,Please refer the link.

'Variable Declaration
Dim objExcel
Dim objWB
Dim iRow : iRow = 1

'Function Call to Open Excel
fnOpenExcel
'Function Call to Create Hyperlink
fnWriteData "First Link","www.uftHelp.com"
fnWriteData "Second Link","www.Google.com"
fnSaveExcel

'Function to Open Excel
Function fnOpenExcel()
  
        Set objExcel = CreateObject("Excel.Application")
        Set objWB = objExcel.Workbooks.Add
        objExcel.Visible = True
        'objExcel.Application.Visible = True

  
End Function

'Function to Write data, with Hyperlink
Function fnWriteData(sData,sLink)

    objExcel.Cells(iRow,1).Value = sData
    'Selecting Cell, so that Hyperlink is created in this Cell
    objExcel.Cells(iRow,2).Activate
    objExcel.Worksheets(1).Hyperlinks.Add objExcel.Selection,sLink
    iRow= iRow + 1

End Function

Function fnSaveExcel
    objExcel.ActiveWorkbook.SaveAs "c:Template.xls"
End Function

**************************************************************************
**************************************************************************
Method2

Logic:- Sending the command to create, particular cell as HyperLink(Using the concept of Excel formulas)

Note:- Use the above methods to Open and Save Excel

'Variable Declaration
Dim objExcel
Dim objWB
Dim iRow : iRow = 1
'Function Call to Open Excel
fnOpenExcel
'Function Call to Create Hyperlink
fnWriteData "First Link","www.uftHelp.com"
fnWriteData "Second Link","www.Google.com"
fnSaveExcel
'Function to Write data, creating Hyperlink
Function fnWriteData1(sData,sLink)

    objExcel.Cells(iRow,1).Value = sData
    'Creating Hyperlink
    objExcel.Cells(iRow,2).Value= "=HYPERLINK("&Chr(34)&sLink&Chr(34)&",""ClickHere"")"
    iRow= iRow + 1

End Function

More Excel Example

One reply on “How to add HyperLink in Excel using UFT/QTP?”

Leave a Reply Cancel reply