Grabbing HTML data from hyperlinks in excel

dbadash

New Member
Joined
Sep 27, 2019
Messages
3
Hi all,


I have a document with a column of 400 hyperlinks for different companies. Each link is to a website with a description of each company. The link leads to the same sight and while the description changes for each company the format and location of the description does not change from link to link.


i am not an expert at Visual Basic and would appreciate the help if this is the direction i need to take. Could someone possible assist me with a code i could use to run across all 400 cells to grab the description of each business or advise on an alternative method?


an example of one of the links is https://www.african-markets.com/en/stock-markets/jse/listed-companies/company?code=AFH


The description is always in the source after Content = "ISIN ..... ID number.... Description"

I have tried searching for similar issues but it's possibly i am not using the correct phrasing so my apologies if something similar exists. I tried.


Thank you for any assistance in advance.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Place the following code in a standard module:

Code:
Public Function GetCompanyInformation(Target As Range) As String
    Static request As Object
    Static doc As Object
    
    If request Is Nothing Then
        Set request = CreateObject("MSXML2.XMLHTTP")
        Set doc = CreateObject("htmlfile")
    End If
    
    With request
        If Target.Hyperlinks.Count > 0 Then
            .Open "GET", Target.Hyperlinks(1).Address, False
        Else
            .Open "GET", Target.Value, False
        End If
        .Send
    End With
    
    doc.body.innerHTML = request.responseText
    
    GetCompanyInformation = doc.getElementsByTagName("SPAN")(92).innerText
End Function


If cell A2 contains a hyperlink or address, place the following in some other cell such as B2:

=GetCompanyInformation(A2)



Here is an example workbook. https://1drv.ms/u/s!Anxrq_k7ozqailW41QZy8fxWXE88?e=RISCVg


I'm on a metered connection so I only tested this on the first 10 symbols.
I hope that it works for you. :)
Have a nice day!
 

dbadash

New Member
Joined
Sep 27, 2019
Messages
3
You are awesome!

Thank you so much for the help.

I did some tweeking with the get element and managed to make it work.
 

Watch MrExcel Video

Forum statistics

Threads
1,098,859
Messages
5,465,111
Members
406,412
Latest member
superjoejoe

This Week's Hot Topics

Top