Problem getting hyperlinks properties in Visual Basic

[Bill]

Didn't see another thread getting this specific, so here goes ...

We are trying to use VB to add hyperlinks and subsequently get the
address of existing hyperlinks. Adding works fine. Getting the Name
and/or Address property doesn't, at least in Office:Mac.

'this works
Sub AddLink()
ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Range("A4"), _
Address:="http://www.apple.com/"
End Sub

'this returns an empty string on Mac, yet works properly on a
WinXP/OfficeXP machine
' the Name property also fails, even following an example in the help
files using Name
Sub GetLink()
ActiveSheet.Range("B4").Formula =
ActiveSheet.Hyperlinks(1).Address
End Sub

'As a work-around, we tested to see if a manually entered ScreenTip
would be returned.
Sub GetLinkScreenTip() 'this works
ActiveSheet.Range("B4").Formula =
ActiveSheet.Hyperlinks(1).ScreenTip
End Sub

'that works ... so lets just add the following to the AddLink
procedure and we should
' be able to extract the link from the ScreenTip instead of the Name
or Address properties
ActiveSheet.Hyperlinks(1).ScreenTip = "http://www.apple.com/"
'or just append to the Add method

This results in the following error:
Run-time error '7'; Out of Memory

Anyone have a clue? We want this automated as part of our workflow
because there will be lots of links. We're using hyperlinks to PDFs
in a subfolder, not web page links, but that shouldn't affect this.

Thanks in advance.

 

[Bill]

I forgot to mention, adding the ScreenTip as part of the
Hyperlinks.Add statement (as below) will very occasionally work, but
crashes Excel most of the time.

 

[JE McGimpsey]

We are trying to use VB to add hyperlinks and subsequently get the
address of existing hyperlinks. Adding works fine. Getting the Name
and/or Address property doesn't, at least in Office:Mac.

<snip>

Anyone have a clue? We want this automated as part of our workflow
because there will be lots of links. We're using hyperlinks to PDFs
in a subfolder, not web page links, but that shouldn't affect this.

Thanks in advance.

This is a bug in XL v.X. You can return the TextToDisplay property
(which isn't ideal, certainly, but it works).

Please send feedback to Microsoft via the Help/Feedback on Excel menu
item (or directly at

http://www.microsoft.com/mac/feedback/suggestion.asp

 

[JE McGimpsey]

I forgot to mention, adding the ScreenTip as part of the
Hyperlinks.Add statement (as below) will very occasionally work, but
crashes Excel most of the time.

I've reported this as a bug. Please send your feedback via the
Help/Feedback on Excel menu item, or directly at:

http://www.microsoft.com/mac/feedback/suggestion.asp