Hyperlink to a cell?

Question

I want to retrieve a hyperlink to a cell in Excel and then paste this hyperlink in a third party app. Clicking the hyperlink should load Excel with the appropriate workbook and that cell should be selected with the cursor.

The hyperlink should work in Microsoft-related products like OneNote; for example, if I have a workbook "D:\abc.xls" and I want to jump to cell C12, I would make an hyperlink like "D:\abc.xls#C12".

And this would work; however, if I paste this in a non-Microsoft applicaltion it just opens up Excel and the workbook, but the cell isn't highlighted. How can I get this to work?

Answer 1

This must be something automagically done by Office apps as the path: abc.xls#C12 taken as a whole is a potentially valid filename itself - if you try to run it from the shell you'll get a "path not found" error.

If you click that link in onenote and look at the command line the resulting instance of excel was opened with there is only the "-embedding" switch which probably means onenote is parsing out abc.xls, launching excel via its automation features then activating C12.

I think if you want that functionality you would need to do the same thing using a helper app and use paths like "C:\your_XL_loader.exe D:\abc.xls#C12".

(If the 3rd party application honors windows protocol handers its trivial to make your_XL_loader.exe associate its self with something like XXX://abc.xls#C12)

Answer 2

RE comment

That's what i was suggesting, for example save the following as a .reg and import it

REGEDIT4

[HKEY_CLASSES_ROOT\XLOPEN]
@="URL:Excel Opener"
"URL Protocol"=""

[HKEY_CLASSES_ROOT\XLOPEN\shell\open\command]
@="CSCRIPT.EXE \"C:\\TEMP\\XLOPEN.VBS\" \"%1\""

This makes an XLOPEN:// URL protocol handler thatr when invoked will run XLOPEN.VBS.

Save the following as C:\TEMP\XLOPEN.VBS

rem //get an argument like "XLOPEN://C:\null\text.xlsx/#F55" note extra /
dim arg:  arg = WScript.Arguments.item(0)
dim arr:  arr = (split(ucase(arg), "#"))
rem unmangle the url
dim filename: filename = replace(arr(0), "XLOPEN://", "")
if (right(filename, 1) = "/") then filename = mid(filename, 1, len(filename)-1)
dim xl:   set xl = createobject("excel.application")
xl.Workbooks.Open filename 
xl.range(arr(1)).select
xl.visible = true

Now if you run

xlopen://c:\null\test.xlsx#Q50

or use

<a href="xlopen://c:\null\test.xlsx#Q50">bla bla</a>

Windows will lookup xlopen:// and pass the string xlopen://c:\null\test.xlsx/#Q50 to XLOPEN.VBS which extracts the file path c:\null\test.xlsx1 and opens it, then selects the range after the #.

This works if called in browsers/from the shell/via the windows API, no idea if it will work in the 3rd party app. (You would replace the script with a helper exe)