Excel cell hyperlink

Question

Brief summary

How to 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 in which users enter data. This table is then used to create some diagrams in SVG. Charts are displayed in Firefox (although this can be changed to something else if something else works better). I would like the objects in the SVG diagrams to have hyperlinks back to the excel cells that generated these objects to make it easier to modify the data behind the diagrams.

I saw tips around hyperlinks like file: /// C: /path/to/workbook.xls#Sheet1! A57 should do the trick, but it only works from IE or Office applications. Attempting to use such a hyperlink in Firefox or from Start → Run opens the workbook in the last active cell the last time the workbook was closed.

I would be happy to just use IE, but of course IE does not support SVG, at least not out of the box.

So, is there a way to create a hyperlink (or maybe some javascript) that will open an Excel workbook with a specific worksheet and active cell?

Answer 1

Here's how you could attack this problem.

Paste the browser object into the form and go to the created chart file.

Since this is your browser object, you can catch the navigation event that is generated when the user clicks on SVG hyperlinks.

Dismantle the navigation target to get a link to a cell, for example. "Sheet1! A57" then call Application.Goto "Sheet1! A57".

Example: add WebBrowser and CommandButton to a custom 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 got the same for working with FireFox using a different technique. In my case, Excel generates a DOT for GraphViz, which generates .svg for FireFox.

The linking technique is pretty ugly in that it requires a lot of small files, but it works very fast. You need to select a new file type or capture an existing rarely used file type, such as .xyz. You write a file for each individual node or edge in svg that you want to return to another Excel cell. The contents of the file stores the name of the file (workbook), worksheet, and cell reference. I just put each on my 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 name of the book and the name of the worksheet and a link to a cell in it to send commands, to succeed to open this book, the worksheet and cell, then you need to associate the vbscript application with the file type (e.g. .xyz) in FireFox. Use Tools-> Options-> Applications. This can be tricky because you must actually enter the vbs file name instead of looking at it (you can go to the folder and then switch to input)! The node and edge links can be passed through .svg (in my case, through the DOT via the URL tag); links in svg should point to the corresponding locally generated file (for example, one of the .xyz files) using the file form: ///.

Then, when you click on the link in .svg, FireFox will run local vbscript as an application with the file name as the parameter. Vbscript reads the contents of the file, finds Excel and sends it commands to activate the correct location. After all this, the script can bring excel to the forefront.

This vbscript snippet will receive a command line argument:

arg = Wscript.Arguments (0)

This vbs snippet will find the current copy of Excel:

Set objExcel = GetObject (, "Excel.Application")

Use these commands to read the file:

Set objFSO = CreateObject ("Scripting.FileSystemObject")
wkbName = objFSO.ReadLine
wksName = objFSO.ReadLine

Use these commands to send messages to Excel:

objExcel.Visible = True
wkb = objExcel.Workbooks (wkbName)
wkb.Activate
wks = wkb. Bookmarks (wksName)
wks.Activate
wks.Rows (rowNum). Select

This snippet will bring Excel to the forefront (tested on winning 7):

set objWsh = CreateObject ("Wscript.Shell")
objWsh.AppActivate objExcel.Name

(Odd Wscript.Shell.AppActivate objExcel.Name not!)