MS Query local to specific spreadsheet

[shinezalot]

Hey Everyone!

I am trying to build a cross-tab query using "MS Query". After reading some posts here, I have come along way to accomplishing what I need. However, it appears that once a query is setup in a specific excel file, the query will only look to that same drive path for table data.

How do I put a query inside a template that I can then move and rename anytime I want with the source data for the query coming exclusively from a worksheet inside the template? Is possible, no?

Any advice would be appreciated. Thanks......

 

[nbrcrunch]

Queries cannot be embedded into an excel file. (However, the results of a query can be exported to Excel.) A QRY file is created and stored where ever you decide to put it. If you take the default when saving the query, it ends up in your user "hive" under applications\microsoft\query.

If you are talking about building a "cube," yes you are right, it seems to anchor itself to a particular file. I am limited in this experiece, but I assume your finding are correct that a cube is directly associated to the file that supplies the source data. There does not seem to be any way to get the cube to "Prompt" for any variables.

Have you tried making the cube and then (without changing the structure), delete the original sourcefile and replace it with an updated one with the same name?

Found this using keywords: Excel Cube
http://advanceinfo.com/qbex/


Also, while "hacking" the files, I discovered that building a cube, the system actually creates two files. When opened with Notepad, one is actually discernable and editable. The other (probably hex) appears to be jibberish when opened with Notepad. The extension on one is CUB. I forget what the extension is on the other.

 

[shinezalot]

I have not done much by way of experimentation. But I will try what you are suggesting.

It seems that the whole MS Query thing is just one more example of Microsoft taking a good idea and then managing to make it almost completely impractical. No doubt MS will correct all this in some future version of Excel (so we will buy more product from them) that will not be available for 5-6 years.

 

[nbrcrunch]

Not impractical. just convoluted.