Extract Data from a List of URL's

[Amar Shafi]

I have a list of URL's each URL refers to a different company.
I just simply need to populate the "Firm Partners" name in column K (refer to the yellow cells in the spreadsheet), using the link/website available in column F (refer to the green cells in the spreadsheet).

Your help is much appreciated.

 

[Chihiro]

Need bit more detail really. Does it need to be UDF? Or can it be Macro that fills values in cells? etc.

 

[Amar Shafi]

Need bit more detail really. Does it need to be UDF? Or can it be Macro that fills values in cells? etc.

I would like a UDF (User Defined Function) as this would be better for me as I am no VBA expert.

Also if possible I am looking to scrape other websites as well, so if a easy solution can be build to scrape those other websites as well that would be greatly appreciated.

Thank you for your continued support.

 

[Chihiro]

2nd part is pretty much impossible. Since every website has it's own structure and how data populates the site.

For the first part, something like below. Though I don't recommend UDF approach for getting info off the web.

Function GetPartner(cel As Range) As String
    Dim xml As Object
    Dim x
   
    Set xml = CreateObject("MSXML2.xmlHttp")

    With xml
        .Open "GET", cel.Value, False
        .send
        x = Split(Split(Split(.responseText, "Firm partners")(1), "<li>")(1), "</li>")(0)
    End With
   
    GetPartner = x
End Function

 

[Amar Shafi]

Hey Chihiro,

Sorry for the delayed response. I just really wanted to thank you for the Macro you have provided to me.

I really appreciate it. Your a true Excel Ninja.