Well basically now in VBA the code is :



And in cell L4 : ="[game]=1&date_range_from="&K2&"&date_range_to="&K3&"&sco re_range_from=60&search_type=advanced"

So for now, the only thing we managed to do is to connect excel to the website.

I need to collect the data from the website in question. So some type of information which is on this page : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=09-31-2013&date_range_to=12-31-2013&score_range_from=60&search_type=advanced

More specifically : game console, score of game, name of game and published date.

Do you know how?

I used to—just not using this method. As I said earlier (or maybe that was a different thread), I used to use a .dll that apparently is not available after Win XP. That method involved pulling HTML code from a web page and either parsing the raw HTML or navigating your way downward in a sort of tree structure: The document, then the body within the document, then the third division in the body, then the 14th table (or the element named "XYZ") within that division, then run through the records in that table one at a time and pull out the 3rd and 5th cells from each record...that kind of thing. Probably required more effort than this QueryTable thingy, but more flexible too; that's the usual tradeoff.

Well, I've been wanting to regain the ability to automate web calls, so this'll be my excuse. Let me play with it and see what I can figure out. I'll be back....eventually.

Thanks so much!

Well, when I run a query, it retrieves the identified table from the web page and fills the data in on the worksheet. That part is straightforward enough. After that you just have to arrange the data into something you can work with.

But you're asking how you get the data from the web site in the first place, which leads me to suspect your results are different. What's happening when you run the program? If it's not filling in the data from the web page, what is it doing?

And by the way, what happens when you run that query manually, without VBA? Because after all you're trying to get VBA to to automatically what you can already do by hand, right? So how well does it work when you do it by hand?

I never runned it; I thought there were more steps to it. Now I went back into the file and saw that the formula of L4 was now in K4. I thought the problem of the brackets was fixed, but it's not.

My mistake... sorry :/

I wrote to the support team of Metacritic, but I haven't received any replies unfortunatly...

The error I get is "Invalid web query".

Also, when I do it manually, I don't do it with a query, I copy paste each page individually and make some removal of data manually (it takes quite a while).

Ah, that explains something: When I ran the program I, too, kept getting an invalid Web query. "But" (I thought) "it works for her, so I must be missing something."

However, here's what I found that you can do: You can set up the query manually. After that, your VBA program can do the refresh and reformat the data to suit you. At least, my program could refresh it automatically. Try it:

1) Create the query manually. Now it's stored permanently in the worksheet.
2) Set up a program with just this one statement in it:That's assuming that you have just the one web query in the active worksheet, or at least that we're talking about the first one.
3) Go back to your worksheet and delete or change a few values that the query downloaded when you first set it up.
4) Run the one-statement program I described in step 2.

You should see that the program did the Refresh, which caused the data you changed or deleted to restored; that proves that the program works, even though it doesn't seem to know what to do with those square brackets when you try to get it to set up the query in the first place.

After that, you just have to add to your program whatever statements you need to toss out the data you don't want and arrange the rest into a usable format. If you don't know how to do that, don't despair; you're making progress. Just prove to yourself that the above works, so far, and then come back and we'll talk more.

Thanks again for your help! However, I don't think the above can really work with what I want to do mostly because the website changes each 3 months. That's why there is a formula in K4 (L4 previously).

Also, I need the data from all the different pages.

For example, for the next 3 months, it's going to be the 19 pages : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=09-31-2013&date_range_to=12-31-2013&score_range_from=60&search_type=advanced

The 3 months after that, it's going to be X pages (today 3 pages) : http://www.metacritic.com/search/all/results?cats[game]=1&date_range_from=12-31-2013&date_range_to=03-31-2014&score_range_from=60&search_type=advanced