9

In reference to Creating hyperlink from Excel FlowChart to MS Word Heading

I would like to know the correct syntax to hyperlink to a specific cell in an Excel Workbook from a Word document.

For example: Say I am in word document "Explaining" and I want to insert a hyperlink to the Excel file DataStuff, but to Cell ZX200. How would I do this manually, without VBA/MACROS?

I already know that to hyperlink to DataStuff I would type

    File:///c:\users\Win7User\desktop\DataStuff.xlsx

Then hit ENTER. (It then becomes an underlined hyperlink. Clicking on it opens DataStuff)

However, I want to add the fact that it should goto cell ZX200, not just open DataStuff at cell A1. The following does NOT work. Can someone fix the syntax for me?

    Does not work:
    File:///[c:\users\Win7User\desktop\DataStuff.xlsx]Sheet1!ZX200

I don't get how the Excel to Word question was answered in about 10 minutes, and my Word to Excel question accumulated crickets and tumbleweed. No MS Word experts??

I've figured it out after browsing many useless, and one useful document. The correct syntax is:

    File:///c:\users\Win7User\desktop\DataStuff.xlsx#Sheet1!ZX200

That should be typed into your word document, and will go to cell ZX200 in worksheet Sheet1 in Excel. Using the information fro the hyperlink I had in my question, you now have a wonderful BIJECTION between Word and Excel hyperlinking. So that when you send people somewhere, you can also send them back!

http://www.shaunakelly.com/word/word-and-excel/excelhyperlinks.html

Community
  • 1
  • 1
VISQL
  • 1,960
  • 5
  • 29
  • 41
  • Wow. Digging Deeper I've found the full functionality. CTRL+F9 then F9 toggles entering field codes in Word. ALT+F9 shows all linking that uses field codes. This is tricky. The best solution is to use ___ c:\users\Win7User\desktop\DataStuff.xlsx#Sheet1!ZX200 ___ as the correct syntax for making a hyperlink with a specific location in the target file. After the "!" you can be put an Excel range name , not just a cell reference. Then, in the hyperlink pop-up you can also specify the Display text (which CAN'T be done via field codes) and also the ScreenTip (which can be done via field codes). – VISQL May 03 '12 at 21:04
  • For more information http://office.microsoft.com/en-us/word-help/field-codes-hyperlink-field-HA102017477.aspx – VISQL May 03 '12 at 21:09
  • A couple of added notes: My experience is that the field codes act separately from the hyperlink pop-up. Maybe I just needed to save and re-open the doc, but edits made in one don't necessarily show up when you switch to the other. Secondly, technically, you can use the _ScreenTip_ in the field codes by using the `\o` switch. For example: `{ HYPERLINK "c:\\users\\Win7User\\desktop\\DataStuff.xlsx" \l "Sheet1!ZX200" \o "Click here for data" }` – GlennFromIowa Jun 01 '15 at 22:48

4 Answers4

1

All you need to do is copy the cell in Excel that you want to reference, then in Word, in the location where you want the link to be, you will 'Paste Special' > 'Link & Merge Formatting'

What then happens is a Field is inserted in Word (that won't look like a hyperlink, but will be greyed out if the cursor is placed in the text). This field can link back to the specific cell in the Excel file by doing the following steps:

  1. Right-click on the field
  2. Then select Linked Worksheet Object > Open Link

Your Excel file should open up to the desired cell.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • Note: This link is now dead. Perhaps this article is similar to the one noted above? https://support.office.com/en-US/article/Field-codes-Link-field-09422d50-cde0-4b77-bca7-6a8b8e2cddbd However, both the answer and the page referencing links seem to indicate an OLE link, which will essentially import the data into Word. What the OP was looking for was a **hyperlink** that would _redirect_ the user to a specific place in the Excel document **opened in Excel**. – GlennFromIowa Jun 01 '15 at 21:27
  • Dead link: bummer. But if you test my method, it pastes the information, but allows opening the source file with a right-click > Linked Worksheet Object > Open Link. It then opens directly to the linked cell, which was the request of the OP. Give it a try! – guitarthrower Jun 02 '15 at 15:41
1

I've been reading through these links and may have another solution for the non-VS guru (like me).

  1. Open your Excel work book go to Formulas -> Define NAME
  2. Create a "NAME" for each of the cells or groups of cells that you would like to link. For example, I hyper-linked a Question # in a Word document to my Excel document that is used for importing questions into our Learning Management System. Example NAME = Question_22 and refers to cell range =WBT16DS058!$A$90 (=worksheet!cellrange)

  3. Save & close Excel workbook.

  4. Open the Word document and create your text (Question 022) , highlight and insert a hyperlink.

  5. Browse & Select your Excel document, append the end of the address to include #NAME. (i.e. - R312Test.xlsx#Question_22).
  6. Select the new link, and your Excel document will open to the correct question.

We have inserted and deleted rows to make sure the reference of the Excel NAME sticks with the cell range when selecting the hyperlink in Word.

JEBoylston
  • 11
  • 2
0

The most concise explanation I've found of the various ways to do this (Office 2013) is actually in the dialog that pops up on opening a file that contains links (after expanding the Show Help button). The first 2 options are relevant for Excel:

Word - link update dialog

The text of which lists your options for creating links:

  • By using the Paste Special command (on the Home tab, in the Clipboard group, click the arrow below Paste, and then click Paste Special) and then clicking the Paste link option.
  • By using the Object command (on the Insert tab, in the Text group, click Object, and then click Object) and then clicking the Link to file option on the Create from File tab.
  • By using the Link to File or Insert and Link command in the Insert Picture dialog box (on the Insert tab, in the Illustrations group, click Picture, and then click the arrow next to Insert).

You can also manage many links in one place quite easily as described in this article, which also visually illustrates the above procedures. Options include automatic/manual updates and locking current values from updating.

brichins
  • 3,825
  • 2
  • 39
  • 60
0

I believe the intended question relates to a permanent link to a specific Excel value even if the value moves to a new cell. With Paste Link, if a row or column is inserted above or below the desired value, the referenced cell no longer contains the desired value. The integrity of the link is compromised, bring incorrect data or blank data into the Word doucment. Ideally, the user could use a Named Range in Excel and hyperlink to the named range. The named range will still follow the value if the item is moved in Excel.

user5676190
  • 1