MS Query local to specific spreadsheet

shinezalot

New Member
Joined
Jan 26, 2005
Messages
5
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......
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
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

New Member
Joined
Jan 26, 2005
Messages
5
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,983
Messages
5,621,966
Members
415,869
Latest member
LWSkinner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top