Jump to content

Excel hyperlink on cell to cell location


 Share

Recommended Posts

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 ?

Link to comment
Share on other sites

#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 by reb

MEASURE TWICE - CUT ONCE

Link to comment
Share on other sites

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 by reb

MEASURE TWICE - CUT ONCE

Link to comment
Share on other sites

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

Link to comment
Share on other sites

#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 )
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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

MEASURE TWICE - CUT ONCE

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...