Excel hyperlink on cell to cell location

[silvaa]

Hi, I am trying to add a hyper link to an excel cell so that when clicked it will take you to the relevant area of a sheet ... like a chart

I have recorded a macro that does this, but need to convert it to code I can use in Autoit when creating the workbook

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/10/2009
'

'
    Range("E7").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Sheet1'!A77", ScreenTip:="Chart 2006-07"
End Sub

$oExcel.Range("E7:E7" ).Select

$oExcel.ActiveSheet.Hyperlinks.Add

this is as far as I can get anyone know what I need to do to convert this macro ?

[reb]

#include<Excel.au3>

_ExcelHyperlinkInsert is the one I use to do hyperlinks

REB

sorry Silvaa I'm having to check another way. It seems that _ExcelHyperlinkInsert is for URLs which was what I was using.

Bad advice on my part.

REB

Edited October 9, 2009 by reb

[reb]

Hi

I have a solution for you if I can upload the files correctly.(first for me)

1 place the Check Hyperlink.xls in your documents folder

2 Export your excel module containing your macro2 to your documents folder (my modual name was HyperMacro you will want to change the name on line 14 to match your modual name.

3 run the script

It works for me. I learned something doing this.

Good luck

REB

Well the excel document did not up load

just open a new workbook and save it as CHECK HYPERLINK to your documents folder.

REB

btw it occurred to me that you must put your own path in place of the one I used.

Edited October 10, 2009 by reb

[picaxe]

Global $oExcel = ObjCreate("Excel.Application")

Local $sSheetName = "Link Sheet"
Local $sDestination = "Destination Sheet"

Local $sAnchor = "B8"
Local $sUrlorMail = ''
Local $sSubAddress = "'" & $sDestination & "'!D8"
Local $sScreenTip = "Link to " & $sSubAddress
Local $sTexttoDisplay = "Go There"

With $oExcel
    .Visible = True
    .WorkBooks.Add
    .WorkSheets(3).Delete
    .WorkSheets(2).Name = $sDestination
    .ActiveWorkbook.Sheets(1).Name = $sSheetName
    .ActiveSheet.Hyperlinks.Add(.Range($sAnchor), $sUrlorMail, $sSubAddress, $sScreenTip, $sTexttoDisplay)
EndWith

[silvaa]

#include<Excel.au3>

_ExcelHyperlinkInsert is the one I use to do hyperlinks

REB

sorry Silvaa I'm having to check another way. It seems that _ExcelHyperlinkInsert is for URLs which was what I was using.

Bad advice on my part.

REB

Well lol, wasnt such bad advice as you think, I dismissed that function when I looked first time for same reason, but your reply made me take a look at the _ExcelHyperlinkInsert coding in the Excel UDF and I modified it a little and it works perfectly. Thank you !

$sheetname = "sheet1" ; sheet you want to go to
$cell = "A77" ; cell you want to go to
$HyperCellAddress = "'" & $sheetname & "'!" & $cell
$oExcel.ActiveSheet.Range("E7:E7" ).Select ;where you want the link
$tip = "Chart 2006-07" ; tool tip over the hyperlink
$oExcel.ActiveSheet.Hyperlinks.Add($oExcel.Selection, "", $HyperCellAddress, $tip )

[silvaa]

Global $oExcel = ObjCreate("Excel.Application")

Local $sSheetName = "Link Sheet"
Local $sDestination = "Destination Sheet"

Local $sAnchor = "B8"
Local $sUrlorMail = ''
Local $sSubAddress = "'" & $sDestination & "'!D8"
Local $sScreenTip = "Link to " & $sSubAddress
Local $sTexttoDisplay = "Go There"

With $oExcel
    .Visible = True
    .WorkBooks.Add
    .WorkSheets(3).Delete
    .WorkSheets(2).Name = $sDestination
    .ActiveWorkbook.Sheets(1).Name = $sSheetName
    .ActiveSheet.Hyperlinks.Add(.Range($sAnchor), $sUrlorMail, $sSubAddress, $sScreenTip, $sTexttoDisplay)
EndWith

Thank you, this makes it easier to understand the macro and how to convert

[reb]

Well lol, wasnt such bad advice as you think, I dismissed that function when I looked first time for same reason, but your reply made me take a look at the _ExcelHyperlinkInsert coding in the Excel UDF and I modified it a little and it works perfectly. Thank you !

$sheetname = "sheet1" ; sheet you want to go to
$cell = "A77" ; cell you want to go to
$HyperCellAddress = "'" & $sheetname & "'!" & $cell
$oExcel.ActiveSheet.Range("E7:E7" ).Select ;where you want the link
$tip = "Chart 2006-07" ; tool tip over the hyperlink
$oExcel.ActiveSheet.Hyperlinks.Add($oExcel.Selection, "", $HyperCellAddress, $tip )

Well we learn something every day don't we. Thanks for the feedback.

Glad you solved your problem.

REB