Hyperlink from one workbook to another

Megazoid · Mar 23rd 2016

Hi Guys, a pointer please. I want to create a hyperlink from one workbook to a cell in another book.
At the time of creation, both books are open. I am not sure of the exact syntax to use. I have been trying to get this right for some time without any luck.

I have values for the full path to the open workbook, the sheet name to which the link is to be made and also the cell address, these are stored in separate variables.

e.g C:\MyFiles\MyWorkbookName.xlsx, Sheet3, Cell f5

The method I am using is Cellx.Hyperlinks.Add , Anchor:= Cellx, Address: = ?

Any help please?

Megazoid

**Ger Plante** · Mar 26th 2016

Re: Hyperlink from one workbook to another

Code

Sub Macro2()
    
    ActiveSheet.Hyperlinks.Add Anchor:=Range("A1"), Address:="c:\wb2.xlsx", TextToDisplay:="CLICK ME BABY!", SubAddress:="Sheet1!$D$6" , ScreenTip:="This will open another workbook!"
       
    
End Sub

Anchor:=Range("A1") === The cell which contains the hyperlink
Address:="c:\wb2.xlsx" === the full path to the new workbook (the workbook doesnt need to be open)
TextToDisplay:="CLICK ME BABY!" === the text that is displayed in the cell that contains the hyperlink
SubAddress:="Sheet1!$D$6" === the specific cell to hyperlink to, including the Sheet name... note if you have a space in the sheet name you need to wrap it in single quotes
ScreenTip:="This will open another workbook!" === a tip for when you put the mouse over the hyperlink before you click it.

HTH
Ger

Megazoid · Mar 28th 2016

Re: Hyperlink from one workbook to another

Hi Ger, Thanks for the Reply. I understand this now i.e use subaddress.

Have this working fine.

Where I Have a range of Full Paths in Col A, Sheet Names in Col B and Cell References in Col C Then

Code Snippet:

Code

Dim MyRange as Range
Dim MyCell as Range
Dim LastRow as Long
Dim FirstRow as Long


Set MyRange = Range(Cells(FirstRow,1),Cells(LastRow,1))
For Each Mycell in MyRange
MyCell.Hyperlinks.Add Anchor:=Mycell, Address:=Mycell.Value, Subaddress:= Mycell.Offset(0,1).Value & "!" & Mycell.Offset(0,2).Value, ScreenTip:="This will open another workbook!"
Next Mycell

Many Thanks

Megazoid

**Ger Plante** · Mar 28th 2016

Re: Hyperlink from one workbook to another

Ya, that should work... just watch out for instances where the sheet name contains a space... it might trip itself up there... you might need to add single quotes around the sheet name to look like this:

Asheetname!$A$1 ---- this is fine
'A sheet name'!$A$1 ---- this is fine
A sheet name!$A$1 ---- this is Not fine

Ger

Megazoid · Mar 29th 2016

Re: Hyperlink from one workbook to another

HI Ger, thanks again for the tip about spaces in Sheet Names. I have already encountered this little problem and am dealing with it in my code. I Check all sheet names and substitute all spaces with an underscore.

Happy Easter to you.

Best Wishes

Megazoid

Hyperlink from one workbook to another

Participate now!

Share

Users Viewing This Thread