Grabbing HTML data from hyperlinks in excel

[dbadash]

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.

 

[Legacy 98055]

Place the following code in a standard module:

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]

You are awesome!

Thank you so much for the help.

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