Excel automation

[Ankesh]

@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&""""&")")

Next

Let me know if you need any help.

Regards,
Ankesh