Creating a Hyperlink from 1 WKBK to open another WKBK and activate

[Barb Reinhardt]

I would like to be able to click a hyperlink within an excel workbook and
that would allow me to open another workbook and activate a worksheet within
that. Is this possible to do with a hyperlink, or would i need to create
another procedure to do this? I'm thinking maybe of using a selection change
event and if I select a cell that has what I'd want for a "hyperlink" it
would open the appropriate workbook/worksheet combo. Comments?

Thanks,
Barb Reinhardt

 

[Gary''s Student]

=HYPERLINK("file:///C:\Documents and
Settings\User\Desktop\Book2.xls#Sheet2!B9")


will jump to Book2.xls. If Book2 is not open, Excel will open it.

 

[Barb Reinhardt]

Thanks. What does the file:/// part do?

=HYPERLINK("file:///C:\Documents and
Settings\User\Desktop\Book2.xls#Sheet2!B9")


will jump to Book2.xls. If Book2 is not open, Excel will open it.

 

[Gary''s Student]

Some hyperlink apps need to know if the destination is a file or a website.
Other apps don't care. I use this form out of habit

 

[Barb Reinhardt]

I'm getting a reference not valid error. This is the filepath I have

\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1

I opted to create it this way
aWS.Hyperlinks.Add anchor:=aWS.Cells(lRow, WSName.Column), _
Address:="file:///" & oWB.Path & "/" & oWB.name & "#" & oWS.name &
"!A1", _
TextToDisplay:=oWS.name

What am I missing?

Thanks
Barb Reinhardt

 

[Gary''s Student]

Visually, does the cell look like:
file:///\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1

especially important is the:
///\\

 

[Barb Reinhardt]

I tried manually entering it with the Hyperlink formula and have this

=HYPERLINK("file:///\\SERVER\projects\Measures\Measures Team\Measurement
Program Workbooks\Supporting_Materials\easurement
Specifications\Submitted\WORKBOOK.xls#WORKSHEET!A1","WORKSHEET")

I get the same error.

 

[Barb Reinhardt]

I've even put single quotes around the WS name in the hyperlink because I
know that can cause problems. No dice.

 

[Gary''s Student]

First carefully re-check the spelling of the server name and the other parts
of the folder structure.

Secondly try the string without the file:///

I can see nothing wrong with what you have done, but I can't test it without
have your specific folder structure. Sorry

 

[Tom Ogilvy]

I turned on the macro recorder and did it manually

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"\\DAFFY\data\AGENCIES\D4\POT\DATA\Dpl-div\DB BOOMER\dm metrics.xls" _
, SubAddress:="'Option 2B'!F9", TextToDisplay:= _

If I hover over the hyperlink, it does display a popup with

File:///\\DAFFY\data\AGENCIES\D4\POT\DATA\Dpl-div\DB BOOMER\dm metrics.xls -
'Option 2B!F9'

but I didn't need to use that format to create it.

 

[Barb Reinhardt]

Using the SubAddress worked. The hyperlink looks exactly the same too. How
odd. I wonder if the # is something like the {} for array formulas and needs
to be entered a specific way.