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.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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!
 
Upvote 0
You are awesome!

Thank you so much for the help.

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

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top