+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: UFT / QTP Beginners (https://www.learnqtp.com/forums/Forum-UFT-QTP-Beginners)
+--- Thread: Excel automation (/Thread-Excel-automation)
Excel automation - Sathiya - 11-17-2012
Hi Friends,
i am working on excel automation. i want to enable hyperlink within the same excel sheet. If sheet 1 has the following values
A
1 ABC
2 BCD
3 EDR Etc., then
now i need to add a sheet with names "ABC","BCD","EDR" etc and the value of A1 in every sheet (except sheet1) should be "Home" with hyperlink enabled which should be navigate to sheet1 and same way if we click on values in the sheet1 it should navigate to the corresponding sheets.
i have done with adding sheets with the values in sheet1. need guidance to complete the hyperlink.
Reply ASAP
Thanks in advance
RE: Excel automation - Ankesh - 11-17-2012
@Sathiya,
To insert a hyperlink in the same file bt a different sheet, below is the formula.
Code:
=HYPERLINK("[aaa.xls]Sheet2!A1","Sheet2")where
aaa.xls is the file name. Just pass the file name.
Sheet2 is the sheet name where you want to jump to.
A1 is range in the destination sheet where the cursor will move
Sheet2 is the friendly name to be displayed in the current sheet.
'***********
Let me know if you need more help.
Regards,
Ankesh
Sathiya,
I have wriiten a sample code which is working fine.
There are few assumption which i have made here. Sheets name are as Sheet1, Sheet2, Sheet3 and so on.
Hyperlink is in the A1 column.
Code:
strCompleteFilPath="C:\aaa.xls"
strTemp=Split(strCompleteFilPath,"\")
strFileName=strTemp(Ubound(strTemp))
Set objXL = CreateObject("Excel.Application")
objXL.visible = true
Set wkb = objXL.Workbooks.Open(strCompleteFilPath)
Set ws = wkb.Sheets(1)
'get cell data
'i am assuming that the sheet names are written in A1.
intRowCount=ws.usedrange.rows.count
For i=1 to intRowCount
v_Range="A"&i
v_Sheet="""["&strFileName&"]"&"Sheet"&i+1&"!A"&i&""""
objXL.Range(v_Range).Value=Trim("=HyperLink("&v_Sheet&","&"""Sheet"&i+1&""""&")")
NextLet me know if you need any help.
Regards,
Ankesh