Building hyperlinks to a cell in another workbook.

[socomfort]

Hello all,

I am trying to build code using VBA (MS Excel 2003) that will create a hyperlink in a source workbook to a target workbook with a different fully qualified path.

Here is the code segment in question:

'Create a hyperlink for the ADDRESS field to the original file.

ActiveCell.Offset(0,3).Activate
strHypAddr = ActiveCell.Address
strHypText = varData(0,2)

  strHypSubAdd = "='" & funPathName(strFileToOpen & "") & _
  & "\[" & funFileNam(strFileToOpen & "") & "]" & _
  & varData(0,1) & "'!" & strHypAddr

Wth Worksheets (strResultsWS)

.Hyperlinks.Add .Range(strHypAddr), strHypText, strHypSubAdd

End With

Just so you know, varData is a variant that use to search column labels in a range. And the variables I am using have all been declared -- the code compiles.

So my question is: is this the correct syntax to use in order to build the hyperlinks?

Thank in advance of your help,

Ben

 

[GlennUK]

So my question is: is this the correct syntax to use in order to build the hyperlinks?

Well, does it work?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[socomfort]

Aw Shucks Glenn!

Sorry about that; no it does not work. It creates a link (looks like a link including the default underline and font color) but when you click the link a message dialog box appears with the following text:

'Cannot open the specified file'

I successfully recorded a macro while creating a hyperlink using the front-end to see the native code too. It creates the link, but it too will not work. Same message box appears.

Today I figured out that if I have the target workbook (LINK TO) open in the same instance of excel as the source workbook (LINK FROM) and then manually create the link, the link will work once the target book is closed.

That's just too weird for words. Anyone else having this problem?

I'd like to figure out how to dynamically build these hyperlinks to an external workbook!worksheet!cell path using vba.


thanks in advance of your reply,

Ben