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

=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 - gsnu200726


"Barb Reinhardt" wrote:

> 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

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

"Gary''s Student" wrote:

> =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 - gsnu200726
>
>
> "Barb Reinhardt" wrote:
>
> > 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

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
--
Gary''s Student - gsnu200726


"Barb Reinhardt" wrote:

> Thanks. What does the file:/// part do?
>
> "Gary''s Student" wrote:
>
> > =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 - gsnu200726
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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

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" wrote:

> 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
> --
> Gary''s Student - gsnu200726
>
>
> "Barb Reinhardt" wrote:
>
> > Thanks. What does the file:/// part do?
> >
> > "Gary''s Student" wrote:
> >
> > > =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 - gsnu200726
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > 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

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

especially important is the:
///\\
--
Gary''s Student - gsnu200726


"Barb Reinhardt" wrote:

> 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" wrote:
>
> > 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
> > --
> > Gary''s Student - gsnu200726
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > Thanks. What does the file:/// part do?
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > =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 - gsnu200726
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > 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

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.



"Gary''s Student" wrote:

> Visually, does the cell look like:
> file:///\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1
>
> especially important is the:
> ///\\
> --
> Gary''s Student - gsnu200726
>
>
> "Barb Reinhardt" wrote:
>
> > 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" wrote:
> >
> > > 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
> > > --
> > > Gary''s Student - gsnu200726
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Thanks. What does the file:/// part do?
> > > >
> > > > "Gary''s Student" wrote:
> > > >
> > > > > =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 - gsnu200726
> > > > >
> > > > >
> > > > > "Barb Reinhardt" wrote:
> > > > >
> > > > > > 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

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" wrote:

> Visually, does the cell look like:
> file:///\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1
>
> especially important is the:
> ///\\
> --
> Gary''s Student - gsnu200726
>
>
> "Barb Reinhardt" wrote:
>
> > 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" wrote:
> >
> > > 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
> > > --
> > > Gary''s Student - gsnu200726
> > >
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > > > Thanks. What does the file:/// part do?
> > > >
> > > > "Gary''s Student" wrote:
> > > >
> > > > > =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 - gsnu200726
> > > > >
> > > > >
> > > > > "Barb Reinhardt" wrote:
> > > > >
> > > > > > 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

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
--
Gary''s Student - gsnu200726


"Barb Reinhardt" wrote:

> 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" wrote:
>
> > Visually, does the cell look like:
> > file:///\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1
> >
> > especially important is the:
> > ///\\
> > --
> > Gary''s Student - gsnu200726
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > > --
> > > > Gary''s Student - gsnu200726
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > Thanks. What does the file:/// part do?
> > > > >
> > > > > "Gary''s Student" wrote:
> > > > >
> > > > > > =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 - gsnu200726
> > > > > >
> > > > > >
> > > > > > "Barb Reinhardt" wrote:
> > > > > >
> > > > > > > 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

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.

--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

> 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" wrote:
>
> > Visually, does the cell look like:
> > file:///\\SERVER\FOLDER\Folder\WBName.xls#WSName!A1
> >
> > especially important is the:
> > ///\\
> > --
> > Gary''s Student - gsnu200726
> >
> >
> > "Barb Reinhardt" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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
> > > > --
> > > > Gary''s Student - gsnu200726
> > > >
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > > > Thanks. What does the file:/// part do?
> > > > >
> > > > > "Gary''s Student" wrote:
> > > > >
> > > > > > =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 - gsnu200726
> > > > > >
> > > > > >
> > > > > > "Barb Reinhardt" wrote:
> > > > > >
> > > > > > > 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