Results 1 to 3 of 3

Thread: Grabbing HTML data from hyperlinks in excel

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Grabbing HTML data from hyperlinks in excel

    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/s...mpany?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.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grabbing HTML data from hyperlinks in excel

    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_k7ozqailW4...WXE88?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!

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Grabbing HTML data from hyperlinks in excel

    You are awesome!

    Thank you so much for the help.

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •