How to add HyperLink in Excel using UFT/QTP? – User friendly Tech help
Scenario:-
nHow we can add “HyperLink” to a Excel Cell.
nWe used this thing while “Reporting” UFT results.Hyperlinks was used as pointer to screenshot of Results after the execution of Scripts.
n
nMethod 1n
Logic:- We’ll use HyperLinks.Add method of Excel worksheet to create a Hyperlink.
nStep1:- Activate the particular cell that we want as “Hyperlink”
nStep2:- Add the Destination path and Text to be displayed to User in Excel
n
nCode:-
nFor basics of Excel,Please refer the link.
n
'Variable Declaration
Dim objExcel
Dim objWB
Dim iRow : iRow = 1
'Function Call to Open ExcelfnOpenExcel
'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
n
****************************************************************************************************************************************************
n
Method2
n
Logic:- Sending the command to create, particular cell as HyperLink(Using the concept of Excel formulas)
n
Note:- Use the above methods to Open and Save Excel
n
'Variable Declaration
Dim objExcel
Dim objWB
Dim iRow : iRow = 1
'Function Call to Open ExcelfnOpenExcel
'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
n
