Very very very very very LOOOOOOOOOOOONG query in excel
Posted on 2010-08-27
I have this workbook that generates some statistics based on queries from a database. The way it's set up now works perfectly, but I had to do a little workaround, which I'd rather avoid, if I could. So, this is the deal:
I used a QueryTable to get the queries from the server. There's 3 problematic ones, each in its own sheet. The problem with them is that the query is very big. The longest (basically, 8 queries joined with UNION) is about 8500 characters long. When I right click and chose Table-> Edit query (or however the translation is in English), it opens MS query, but as if it's a new query. That is, it opens the query builder, and when I close it and get to the editor, the SQL is empty. However, if I choose refresh in the QueryTable, it refreshes correctly.
I also tried to change the query in a macro (as basically the whole file works on a macro anyway) but there was some error which I can't recall at the moment.
So, every month I need to change some parameters in the query (date intervals). Those parameters are set in a cell in another sheet. I can't set parameters on the QueryTable because 1) it can't be graphically represented and 2) it won't even show on MS query. What I did was create a cell (or, in the case of the really long one, 2 cells) that generates the SQL query (like ="Select field from table where this=" & A1), then I edit the query, copy this and it works. However, I would prefer if I could just open the book, change the date and have it work right away.
1) Is there ANY way to set parameters dynamically in these kinds of queries in excel (i.e., with joins, unions, etc)?
2) Any ideas on how to do this by macro?
3) Is it even possible to use a query this big automatically? Or can't excel handle these many characters in some object's property?