Hi,
Could you please help me.
I have a sit of URLs and I want to extract all hyper URLS from each one.
Input: one column contain some URLs >> Column A
Output: Two columns >>>> Column C and D
Please check attached sample.
Thanks;
Marsil
H Marsil,
You can try this basic code (you need to add references to Microsoft HTML Object Library and Microsoft Internet Controls):
Dim IE As New InternetExplorer
Dim str As String, i As Long, j As Long
Dim Doc As HTMLDocument
Dim tagElements As Object
Dim element As HTMLObjectElement
i = 2
j = 2
Do Until ActiveSheet.Cells(j, 1) = ""
str = ActiveSheet.Cells(j, 1)
IE.navigate str
IE.Visible = True
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Set Doc = IE.document
Set tagElements = Doc.all.tags("a")
For Each element In tagElements
ActiveSheet.Cells(i, 3) = str
ActiveSheet.Cells(i, 4) = element.href
i = i + 1
Next element
j = j + 1
Loop
IE.Quit
End Sub
Thanks Catalin.
I still need your help as still face an issue when i run the macro, please can u update the code in attached macro and resend me.
Thanks again for your time.
Marsil
Just sending the file without saying what the problem is, is not at all useful. It's like saying "Houston, we have a problem..."
The code works, read again the instructions. Once you add the references, will work. If you don't know how to add a reference, go to visual basic editor-Tools-References and browse for those 2 libraries mentioned.
Catalin Bombea said
you need to add references to Microsoft HTML Object Library and Microsoft Internet Controls):
Hi Catalin,
Wow thanks so much Catalin for your help, this works great!!!
Could you please update the code to remove the duplication from file after the macro finish.
Thanks again for your time.
Marsil
I suggest recording a macro while removing duplicates manually, you will be able to reuse it and call it from the code provided, after the loop.
