Hyperlink field code

[Guest]

I would like to create a hyperlink from Word to Excel that goes to a named
range. Does anyone know the field code/field switch and syntax to do this?
Thanks in advance!

 

[Jay Freedman]

I would like to create a hyperlink from Word to Excel that goes to a
named range. Does anyone know the field code/field switch and syntax
to do this? Thanks in advance!

The field code syntax is the same as the field code to open the Excel file,
plus the \l switch followed by the name of the range:

{HYPERLINK "<path>\<filename>" \l "rangeName"}

The easy way to get this is to use the Insert > Hyperlink dialog, select the
Excel file (if you visited the file, it should be listed in Recent Files),
click the Bookmark button, and select the range from the list.

By default, the result of the field will show the range name at the end
following a # sign.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Guest]

Hi Jan - I had tried the \l field switch but my syntax was wrong, however,
following your syntax exactly, it still didn't work with or without spaces as
in your example. The bookmark also didn't work, as I presume that Named
ranges aren't bookmarks (as such, or at least, not according to Word). I had
tried that too. I didn't understand your last sentence : By default, the
result of the field will show the range name at the end > following a # sign
- no #. Or perhaps I've completely misunderstood the syntax?
Thanks anyway!

 

[Jay Freedman]

Hi Lynn,

When Word deals with Excel, it does consider a named range to be the same as
a bookmark. It should work -- I've just done it again to make sure, and I
have no problems with it. When I click the hyperlink, the worksheet opens in
Internet Explorer with the first cell of the named range at the top of the
window.

The part about the # sign happens only if you create the hyperlink in the
Insert > Hyperlink dialog, not if you directly edit the field code.

When you say "didn't work", do you mean that the worksheet opens at the
beginning instead of at the named range, or that it doesn't open at all, or
something else? Exactly what is the content of the field code?

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Tony Jollans]

1. The Bookmark button has never worked for me - I am a bit surprised Jay
mentioned it; maybe there's some trick I don't know.

2. Entering hyperlink fields can be tricky. Can you show us exactly what you
entered?

3. Adding # followed by the range name (with no spaces) immediately after
the workbook name (in the hyperlink - Ctrl+K - dialog) should do the trick.
I'm not sure what may have gone wrong for you there.

 

[Suzanne S. Barnhill]

When I try using the Bookmark button (to insert a named range in an Excel
sheet) in Word 2003, I get an error message that "Microsoft Word could not
open this file or could not parse a file of this type." The Help says, "This
message can appear if you attempt to create a hyperlink to a bookmark in a
file that does not support bookmarks. For example, XML files do not support
bookmarking." I thought this might be a result of having "Confirm conversion
at Open" enabled (Tools | Options | General), but disabling it didn't help.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jay Freedman]

Arrgghhh... bad test environment. :-( I have two files with the same root
name, a .xls workbook that was saved from Excel as a .htm file. The named
ranges in the workbook became some sort of ExcelName tag in the .htm file.
When I used the Hyperlinks dialog in Word, the display was too narrow to
show the whole file name, and I clicked the .htm instead of the .xls without
noticing. That's why it worked.

When I try it with the .xls file, I do get the same error message.

Tony's item 3 is correct. You just have to know the name of the range, and
type it correctly. It's not nice that Word can't figure out how to extract
the information from a .xls file in the same way it can from .doc or .htm
files.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Guest]

Hi guys
Many thanks for all your suggestions. It's still not working for me so
maybe I still haven't understood the syntax correctly. In my excel file,
which is named test12.xls I've edited the hyperlink (Alt F9) and added #Name
(name of named range) so that it's inside the } bracket so it looks like:
{HYPERLINK "LYNN/EXCEL/test12.xls"#Name} It's in the same drive, Lynn
Folder, Excel Subfolder). If however, I type in #name so it doesn't exactly
match, it does open the file but just to where I left the cursor when I saved
it.
Any more clues?
Thanks in advance Lynn

 

[Tony Jollans]

Hi Lynn,

Use the #Name syntax in the Hyperlink Dialog - not when directly editing the
field.

 

[Guest]

Eureka! Finally got the syntax in correctly (I think I misunderstood because
of the two different ways described).

Many thanks to Tony, Suzanne and Jay.