Help on VBA Macros

[Rahul163]

Hi All,
I need to extract a table from webpage using URL and paste it in an excel sheet.
Here i have around list of 200 unique IDs, so everytime i need to go to webpage-copy table paste in an excel again use back button in webpage,go to next ID and copy table and paste.

I would like to know if is it possible to automate this process using macros.

I mean i just need to enter the ID and Macros automatically copy the table from webpage nad paste it. I tried a simple code which look like

Sub URL_Get_Query()
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL;[URL]http://xyz?id=[[/URL]""QUOTE"",""Enter ID""]", _
         Destination:=Range("a1"))

      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .Refresh BackgroundQuery:=False
      .SaveData = True
   End With
End Sub

Using this i am able to fetch exact data but i need to give ID everytime and my results are displayed in a1 (sheet1)

Is it possible to get all the result in same column and for next ID, the details are diaplayed below previous one.

Thanks in Advance.

 

[NARAYANK991]

Hi Rahul ,

Is it possible for you to post the URL , or is it an internal site ?

Narayan

 

[SirJB7]

Hi, Rahul163!

Give a look at this:
http://chandoo.org/forum/threads/web-query.11493/#post-67537
http://chandoo.org/forum/threads/copy-data-from-a-website-to-excel.12897/

You'll find may examples of web queries with changing URL.

Regards!

 

[ThrottleWorks]

Hi @Rahul163 Welcome to the Forum !
I am aware that 2 Ninjas have already guided you but would like to add some more.

You have unique IDs like this post, http://chandoo.org/forum/threads/help-on-vba-macros.15344/
so the next post will have ID as .....15345/

If you know the code to derive table for 1 link you can try following.
I am assuming you have the list of all the unique IDs. If they are in serial order more better.

Try to run your code in a loop. Use "For I loop". Increase the loop by 1 with every run.
Pick next ID with every loop.

So for example at first run the table will be derived for 15344, in the next run it will run for 15345.
I am assuming you know how to use loop and loop related coding.

Sorry for not writing the actual code and assuming that this might help.
Would have definitely tried to write the code if I had time. (no sure about it's success though)

Have a nice day ahead.