extracting links in a HTML file into cells
Posted by freddi furnin on October 01, 2000 4:42 PM
I have a webpage with links. The file shows "abc" for a hyperlink to "www.abc.com". I open it into excel97. Using right buttton...hyperlink...copy hyperlink and then Paste as hyperlink, I can paste www.abc.com in a new cell.
Yet when I try it as a macro I get "abc" (with www.abc.com" as the underlying link. I just want a new cell with "www.abc.com" there.
I'm a beginner so can anyone help me?
Thanks
Posted by Ivan Moala on October 01, 2000 9:04 PM
I think what you may have tried is something like this;
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
End Sub
This will yield a Hyperlink.....to get just the text then
Try something like this;
Notes:
1) assumes links in Column A
2) pastes to column B
Key here is the address of Hyperlink
Sub GetLinkNameOnly()
Dim Adr As String
Dim NoOfAddresses As Integer
Dim CurrentRow As Integer
Dim x As Integer
x = 1: NoOfAddresses = 0
BeginCount: 'Get no of Addresses in Column "A"
If Cells(1 + x, 1) <> "" Then NoOfAddresses = NoOfAddresses + 1: x = x + 1 _
'Put Address in Column "B"
For x = 1 To NoOfAddresses
Adr = Worksheets(1).Range(Cells(2, 1), Cells(2 + NoOfAddresses, 1)).Hyperlinks(x).Address
Cells(1 + x, 2) = Adr
Next
MsgBox Application.UserName & " it's done now", vbInformation
End Sub
HTH
Ivan
