Yep, I get the same thing. I played around with the URL a while, in the program, and believe it or not, what appears to make it unhappy is the square brackets; my browser thinks they're valid, alright, but when I remove "[game]" from the URL then the VBA error goes away.

This may not be good news for you. Unless there's a way to persuade VBA that this is a valid URL, or a way to get the data from metacritic without the square brackets, you may have to rethink your solution. But the first thing I'd do is start asking around to see whether there's an Excel setting that'll tell it to be less stringent about valid URL characters. Maybe you'll be able to Google something about it. Failing that, maybe the folks at metacritic can offer you a workaround.

I might have googled it myself, but I gotta go soon; I have a meeting in a few hours.

Thanks for that indication..!

I've simply put the "[game]=1" portion in my L4 formula and it seems to work for now.


Now I need help for the next steps! :P Should I make another topic?

Really? You put the square brackets in another part of the URL and it worked? Now I'm not sure what to think.

Anyway, my suggestion is that you ask your next question here and I'll see what I think. If it's beyond my expertise, I'll suggest that you start a new thread, so that others will see it (instead of glancing at this one and mutter "oh, someone else already has this one").

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?