Fetching data from multiple URL

  • Dear Friends,


    I have recorded a Macro and its something like this:



    This Macro would fetch details from URL mentioned and paste the data in "A1" on Sheet-1.


    Requirement: I have over 3000 such URLs from which data needs to be downloaded. the URL links are present in a separate worksheet. How do i reference "Connection:=_" to dynamically get it from the work sheet and increment it by 1?


    Also, how do i set the Destination:=Range to dynamically refer to last row + 1 rather than hardcoding it as $A$1 or $A$20, etc?


    Any help in this regard is highly appreciated.

  • Re: Fetching data from multiple URL


    I wouldn't use a web query for this, can you share the url?


    And what output are you wanting - you need to mock something up

  • Re: Fetching data from multiple URL


    Kyle,


    Attached excel contains the list of URL's from which i would the data to be extracted. I am sorry for jumping onto the other thread. I posted bcz i felt both queries were relevant. Apologies if its against the forum rules.


    Output - Requires formatting. I think i will be able to do once sheet 2 has information about Webtable 6 from all URLs..

    Files

    • Refernece.zip

      (62.45 kB, downloaded 108 times, last: )
  • Re: Fetching data from multiple URL


    Kyle,


    Yes. I am after the entire information available on web table - 6


    1. Office Address
    2. Phone
    3. Fax
    4. Email
    5. Website
    6. Categories.

  • Re: Fetching data from multiple URL


    How's this as a starter for 10:

  • Re: Fetching data from multiple URL


    Kyle,


    I tried copying this code as a macro, retained row B2 to B10 on Sheet2 and executed the macro.. getting "Run time error -2147024891 (80004005) Access is denied" error message.


    I ain't trying to access anything that is password protected. Could you kindly let me know where i am going wrong? Attaching screenprint for reference.


    Also, should the detail

    Code
    1. With .getelementsbytagname("table")(5)


    be replaced with


    Code
    1. With .getelementsbytagname("table")(6)
  • Re: Fetching data from multiple URL


    Try changing:

    Code
    1. Dim req As Object: Set req = CreateObject("msxml2.xmlhttp")


    to

    Code
    1. Dim req As Object: Set req = CreateObject("WinHttp.WinHttpRequest.5.1")


    And no, the table number doesn't need changing

  • Re: Fetching data from multiple URL


    Kyle,


    No - I don't get the error now. but by the look of response on the worksheet URL it doesn't seem to be alright as the data seems to be overwriting the contents.



    The correct data should be as per the attachment (either one is fine)


    NOTE: I am getting the same error while executing Macro GetData". If i execute macro test, it gives "Application defined or object defined error"

  • Re: Fetching data from multiple URL


    Well have you resized the rows and columns?


    You need to make a list of all the possible bits of info that get listed in that table, you then need to check this list against the list on the actual page. Some pages have more data than others and you need some way of sorting which data goes into which columns

  • Re: Fetching data from multiple URL


    Kyle - Agree.. If we could get info as per "Unformatted" page it would make sense as well as I can use some Sort/ filer options to re-arrange it in way specified in the "formatted" page.

  • Re: Fetching data from multiple URL


    Why on earth would you want the data as per the unformatted tab?


    I've told you how to do it as a one stage operation, why would you want to add a second stage "sort and filter"?

  • Re: Fetching data from multiple URL


    Kyle,


    I was hoping that getting information as per unformatted tab might be simpler when compared to formatted one. Also, I wasn't sure if Formatting was doable because of following reasons:


    1. Address - will be more than one line. So, merging them all and adding it to a single cell might be difficult.
    2. Categories - Likewise. Sometimes, we can have one category, but many a time, we end up with more than 5-6 of them.


    Even if we couldn't add it to a single cell, its OK if we could identify the Max number of rows (Across columns A-H) occupied by previous query and start from the next one.


    Hope this helps and forgive me as i don't possess coding skills on VB. I do know programming, so i can understand the logic happening in the program, however not upto the mark in getting to code.