1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Data from a List of URL's

Discussion in 'Ask an Excel Question' started by Amar Shafi, Dec 12, 2017.

  1. Amar Shafi

    Amar Shafi New Member

    Messages:
    11
    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.

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,819
    Need bit more detail really. Does it need to be UDF? Or can it be Macro that fills values in cells? etc.
    Amar Shafi likes this.
  3. Amar Shafi

    Amar Shafi New Member

    Messages:
    11
    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.
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,819
    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.

    Code (vb):

    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
     
  5. Amar Shafi

    Amar Shafi New Member

    Messages:
    11
    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.

Share This Page