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:
Code:
ActiveSheet.QueryTables(1).Refresh
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.