Can Word Hyperlink to a Specific Sheet and Cell in Excel?
January 19, 2018 - by Bill Jelen
Great question today from Selina:
Can a hyperlink in Word open a specific Excel file, jump to a sheet and jump to a cell?
The syntax is fairly tricky, but it seems to work.
Well, it may not work like you want it to work, but it nominally works.
You can watch the video here, or jump to the summary below the video.
First, figure out the full path & file name of the Excel file. I use these steps:
- Press Alt + F11 to switch to VBA
- Press Ctrl + G to open the Immediate pane
- Type Debug.Print ThisWorkbook.FullName and press Enter
- On the next line, you will see the full path and file name.
- Select the resulting line and press Ctrl + C to copy to the clipboard.
Go to Word. Select where you want the hyperlink to appear. Press Ctrl + K to open the Hyperlink dialog. Press Ctrl + V to paste the results of #5 above.
At this point, the hyperlink would simply open the Excel file to the last active sheet and cell.
Selina's question, though, is how to have Excel jump to a specific sheet and location. Here is an example:
The answer from #5 above is:
You want to jump to the sheet called IncomeStatement
You want to jump to cell Z99.
The hyperlink would have to be:
That is *weird*! The syntax does not feel like Excel. In Excel, you would not have to use apostrophes around the sheet name unless the sheet name contained spaces or punctuation. But here, you have to use it.
In the video, I suggest a different approach. Go to the cell in Excel where you want to hyperlink to. Select the cell. Click in the Name Box to the left of the formula bar. Type a name with no spaces, such as JumpHere. Save the file. Your hyperlink in Word simplifies to:
Problem #1: Word shows an annoying warning box when you Ctrl + Click the hyperlink. They warn you that the Excel file may not be safe. To stop this warning, follow these steps:
- Get the path to where the Excel file is stored.
- Open Word. File, Options, Trust Center.
- On the right side, click Trust Center Settings.
- On the left side, choose the second choice -- Trusted Locations.
- Near the bottom, choose Add New Location. Paste in your path from #1. Click OK. Click OK.
- This step is not optional. Close Word. Re-open Word. I skipped this and had to say some four-words when they kept nagging me about the file being unsafe. But after closing and restarting Word, the message went away.
Problem #2: Excel is a Slacker. If you tell Excel to jump to Z99, you might expect Excel to scroll so that Z99 is the top left corner cell in the window. But Excel doesn't do that. Excel scrolls to, perhaps, F78. Why F78? Because if you can see F78 in the top left, then Z99 is visible somewhere in the window. It is looney. Technically, Excel is doing what you want... showing the people Z99. But it is not intuitive. I guess if I want Z99 near the top, I would have to cheat and ask Excel to go to AT123. (Put Z99 in the top left of your window and then figure out what is the last full cell you can see.) This workaround is not perfect, because it assumes everyone has the same size monitor and keeps Excel full screen.
Title Photo: geralt / Pixabay