Hyperlink to a cell?
I want to retrieve a hyperlink to a cell in Excel and then paste this hyperlink in a third pa开发者_如何学运维rty 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?
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)
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
)
精彩评论