Hyperlink to an Excel Cell

Question

Quick Summary

How can I create a hyperlink to a specific cell in Excel that will work from Firefox, or achieve the same result in javascript?

Detailed Description

I have an excel spreadsheet that users enter data into. This spreadsheet is then used to generate some diagrams in SVG. The diagrams are displayed in Firefox (though this could be changed to something else if something else would work better). I'd like objects in the SVG diagrams to have hyperlinks back to the excel cells that generated those objects, to make it easy to change the data behind the diagrams.

I've seen advice around that hyperlinks such as file:///C:/path/to/workbook.xls#Sheet1!A57 should do the trick, but that only works from IE or Office applications. Trying to use this sort of hyperlink in Firefox or from Start -> Run opens the workbook at the last active cell last time the workbook was closed.

I'd be perfectly happy just using IE, except of course IE doesn't support SVG, at least not out of the box.

So is there a way to form a hyperlink (or perhaps some javascript) that will open an excel workbook with a particular worksheet and cell active?

Answer 1

This is how you could attack this problem.

Embed a browser object on a form and navigate to your generated diagram file.

Since this is your browser object you can catch the navigate event generated when the user clicks on the SVG hyper links.

Parse the navigate target to get the cell reference e.g. "Sheet1!A57" then call Application.Goto "Sheet1!A57".

Example: Add a WebBrowser and a CommandButton to a user form then paste this code behind.

Private Sub UserForm_Initialize()
    Me.WebBrowser1.Navigate2 "file:///C:\Test.svg"
End Sub

Private Sub CommandButton1_Click()
    Me.WebBrowser1.Navigate2 "workbook:Sheet1!A57"
End Sub

Private Sub WebBrowser1_BeforeNavigate2(ByVal pDisp As Object, URL As Variant, Flags As Variant, TargetFrameName As Variant, PostData As Variant, Headers As Variant, Cancel As Boolean)
    Dim pos As Integer
    pos = InStr(1, URL, "workbook:", vbTextCompare)
    If 1 <= pos Then
        Dim cref As String
        cref = Mid(URL, pos + Len("workbook:"))
        Application.Goto Range(cref)

        Cancel = True
    End If
End Sub

Answer 2

I've gotten this same thing to work with FireFox using a different technique. In my case, Excel generates DOT for GraphViz, which generates .svg for FireFox.

The technique for links is kinda ugly in that it requires a lot of little files, but runs plenty fast. You need to choose a new file type, or hijack an existing seldom used file type, like .xyz. You write a file for each and every separate node or edge in the svg that you want to go back to a different cell of Excel. The contents of the file stores the name of the file(workbook), the worksheet, and the cell reference. I just put each on their own line. You create one vbscript (.vbs) as a separate script file, this will be your application. This vbscript takes one parameter, which is the name of the file, and what it does is open the file, read the workbook name and the worksheet name and the cell reference therein to send commands to excel to bring up that workbook, worksheet and cell. You'll then need to associate your vbscript application with the file type (e.g. .xyz) in FireFox. Use Tools->Options->Applications. This can be tricky, because you have to actually type the name of the vbs file instead of browsing to it (you can browse to the folder, then switch to typing)! The node & edge links can be passed thru the .svg (in my case through DOT via the URL tag); links in the svg should point to an appropriate local generated file (e.g. one of the .xyz files) using the file:/// form.

Then when you click on a link in the .svg, FireFox will launch the local vbscript as the application with the file name as the parameter. The vbscript reads the contents of the file, locates Excel, and sends it commands to active the right location. After all of that, the script can bring excel to the front.

This snippet of vbscript will get the command line argument:
arg = Wscript.Arguments(0)

This snippet of vbs will find the running copy of Excel:
Set objExcel = GetObject(, "Excel.Application")

Use these kind of commands to read the file:
Set objFSO = CreateObject("Scripting.FileSystemObject")
wkbName = objFSO.ReadLine
wksName = objFSO.ReadLine

Use these kind of commands to send messages to Excel:
objExcel.Visible = True
wkb = objExcel.Workbooks(wkbName)
wkb.Activate
wks = wkb.Worksheets(wksName)
wks.Activate
wks.Rows(rowNum).Select

This snippet will bring excel to the front (tested on win 7):
set objWsh = CreateObject("Wscript.Shell")
objWsh.AppActivate objExcel.Name

(Oddly Wscript.Shell.AppActivate objExcel.Name doesn't!)