Log In
Register
Home
September 6, 2019
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
September 6, 2019
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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):
September 6, 2019
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
