Transfer of data from one sheet to other after web query

Hello!

Setup of a recorded macro - file attached to view the setup.
In Sheet1 I have few urls from where I wish to download some tables.
I have recorded a macro in Sheet2 to get some tables from a webpage. This is working fine.
Column A has headers and column B has the corresponding values
Similarly few more headers are in Column C and column D has the corresponding values.
The above data is placed vertically with headers in two columns A and C and their corresponding values in two columns B and D

What I need to do
In Sheet3, in row 1, I have manually typed all headers from sheet2 and I want to copy data from sheet2 to sheet 3 under every header so that the data is laid out in a tabular format based on a criteria.

Problems that I am facing
I have written a loop but I dont know the syntax on how to capture url from sheet1 for the loop till the last url
Dont know how to transfer downloaded data from sheet2 to sheet3 in tabular format once the web query is run in the loop and if it meets the criteria
Criteria: The transfer of downloaded data from sheet2 to sheet3 should ONLY happen if value of B10 in sheet2 is a number (not text or blanks)
Do I need all the parameters that I got it when the macro was recorded? will it decrease the processing speed if my urls are more?

Appreciate some help here... Thanks.

Please Login or Register  to view this content.

Please help!

How did you get the data in sheet2?
This site http://www.mywebsite.com/mypage1.php
requires a log in access. I have got a different data if you use the URL's in sheet1.

This is a sample url. Its not the real one. The data retrieval part is working fine. I just need to identify if value of B10 in sheet2 is a number and based on that transfer data from sheet2 to sheet3

Thanks for your reply. I am going to try this soon and revert.

As mentioned, I have a list of urls in sheet1 and recorded a macro to get the web query result in sheet 2. I can place your code between the loop so that it runs continuously until the loop is over (all urls are completed)

1. I wish to again ask about how to how to capture url from sheet1 for the loop till the last url? I am not getting the syntax right because of the double quotation marks. how can I replace the url that i mentioned in my code (recorded macro) to the urls given in sheet1? In short... how to club both the codes, recorded macro + the code you gave so that I can use the same code to run a loop to process all urls in sheet1

2. Do I need all the parameters that I got when the macro was recorded? They are too many.... will it decrease the processing speed if these parameters are kept as it is especially when my urls are more?

Out of curiosity.... Is it possible to read the result of the line .WebTables = "17,19,20,21,22,23,24,25,26" in memory before they are dumped in sheet2? If this is possible, we can check the numeric value that goes in cell B10 and dump it only if it is numeric. This may increase the processing speed I guess.

Thanks again.

Is it possible to read the result of the line .WebTables = "17,19,20,21,22,23,24,25,26" in memory before they are dumped in sheet2?
I doubt but I am not sure.
The code on module -3 loops through each cell in sheet1 column A. As long as you have valid URLs in column A, you should be able to get the desired data, but formatiing these data in sheet3 is a different ball game. I suggest you should first get the data in sheet2, see the format and then anther code in to sheet3

Thank you @AB33

Note: First of all, I have changed the file and contents of worksheet so please do not refer to the attachment in question. Please see the code below.

I was playing with your solution in the past couple of days because I wanted to find a solution myself after the wonderful start you gave. I have been successful to an extent with a small issue for which I need help.

This is the flow:
1 The code visits an url
2 dumps few html tables in sheet2
3 finds a word "mysearch" in sheet2
4 if found, it copies all data from sheet2 to sheet1
5 moves to next url in sheet3
6 does the same thing until all urls in sheet3 are processed

Problem No.1
Everything is working but when the condition (point 3) is met, it also copies the data. However, as it loops, it keeps on overwriting data in row 1 of sheet1. It does not append copied data in next rows. I am assuming there is something wrong in the code marked in red

Problem No.2
Code runs slow. Is there anyway to optimize it?


Thanks

Please Login or Register  to view this content.

The code is not moving in to next empty row as you have fixed the cell address. If you look at my code, you have changed this line.

Please Login or Register  to view this content.

In to

Please Login or Register  to view this content.

You need to use this line

Please Login or Register  to view this content.

Oh yes! Thank you so much