Excel 2007 help - Hyperlinks

[Tattysnuc]

Hi,

I've created a spreadsheet which is linked to the Extreme Overclockers folding web page. The page is dynamically created and contains hyperlinks to further information pages which I'd like to read into a cell.

See: Folding Stats

I actually want to use in Excel the hyperlink data and not the Username as this contains a user number which is more useful to me.

For example, user 1stRaven has the hyperlink http://folding.extremeoverclocking.c...hp?s=&u=408507 attached to his name, but I cannot find how to do this. Ideally I want to extract this text to a cell so I can then identify the user number and then use this to create another query of the site.

Anyone got any ideas?

Mike
(Tattysnuc)

[Repressor]

http://folding.extremeoverclocking.c...tRaven&t=69776

This displays the User Number halfway down the page. You can probably work out how to extract that if you have the time...

[Tattysnuc]

That gets a bit convoluted, but it's something to work with....

[Repressor]

Being handed the answer takes the fun out of the hard work and the satisfaction of getting it to work!

[Tattysnuc]

Shame it wasn;t the answer I was looking for though. gonna have to re-acquaint myself with VBA which I loath doing because it takes me so long to get back into it.

[Tattysnuc]

Got a reply on the MS forums which defines 2 new functions in Excel (VBA)

To extract the full hyperlink...

Function GetHyperlink(FromCell As Range)
Dim xHyper As Hyperlink
Application.Volatile
GetHyperlink = ""
For Each xHyper In Sheets(Application.Caller.Parent.Name).Hyperlinks
If Not Intersect(FromCell, xHyper.Range) Is Nothing Then
GetHyperlink = xHyper.Address
Exit Function
End If
Next xHyper
End Function

Or to extract the user ID (if it is always at the end of the hyperlink address):

Function GetHyperlinkUser(FromCell As Range)
Dim xHyper As Hyperlink
Application.Volatile
GetHyperlinkUser = ""
For Each xHyper In Sheets(Application.Caller.Parent.Name).Hyperlinks
If Not Intersect(FromCell, xHyper.Range) Is Nothing Then
GetHyperlinkUser = xHyper.Address
If GetHyperlinkUser <> "" Then
Dim TheWords
TheWords = Split(GetHyperlinkUser, "=")
GetHyperlinkUser = TheWords(UBound(TheWords))
End If
Exit Function
End If
Next xHyper
End Function

How brilliant is that! Now I have a new function that can extract the user code from the end of the Hyperlink in the folding tables. Yay for me!