Some questions on developing my web query macro

Hi,

I'm still relatively new to VBA. I've worked out how to create a web query macro to pull data from a url and then referencing the second part of the url within a certain cell with the following code at the beginning of the macro:

Please Login or Register  to view this content.

But there's a few things I can't work out how to do that I'd like some help on:

1. I'd actually like to run the macro from a separate sheet (separate tag on same workbook) so at the top I'd like to reference cell A1 on one sheet (call it Sheet1) but have the resulting data pasted into Sheet2 (starting at cell A1). I can't work out how to do that so some help appreciate there.

2. Also, the above only does one query on the one page, but is there a way I could get the macro to then continue running vs. references in e.g. cell A2, A3, A4 etc. just stopping when it hits a blank cell? I know I can just copy and paste my code down and change the cell reference, but the problem I have is then how do I reference the destination cell to start at the next blank row so it doesn't overwrite the previous data. Also maybe there is a cleaner, simpler way to get the macro to repeat downwards rather than copy and paste, but if so how do I do it and how do I get it to stop (or would it just stop when it hits a blank cell with no reference.

Hope that's all clear and any help appreciated.

Thanks!

I do not know what is in column A values, so it is difficult to test the code. Try this one and if it does not work, please attach a sample with the relevant data.

Please Login or Register  to view this content.

Hi,

Thanks for the quick response. That actually works perfectly! One second follow up question. If I wanted to put the whole url in a cell and reference that cell so e.g.

A1 = http://www.xscores.com/soccer/league.../2015-2016/p/1
A2 = http://www.xscores.com/soccer/league.../2015-2016/p/2
A3 = http://www.xscores.com/soccer/league.../2015-2016/p/3

etc. How would I do that?

Thanks,

Change this line of code

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Thanks for your help. It all seems so simple now you've mapped it out... just couldn't work it out before. Thanks again"

You are welcome!
Please mark the thread as solved.

Hi,

Sorry, one more question on this. Have actually found a better way to get the data with the macro below, but have the same questions as to how to reference the URL from the cells and to get it to repeat and paste in new tables below the previous one created (e.g. A1 URL will create a table on a sheet, then A2 URL will create a table and add below the first table etc.).

Also getting an error of e.g. Table 1 already exists, so not sure if that would be tied into this as well?

Please Login or Register  to view this content.

Thanks