Paul,

Try something like this...

Save your MS Query as an .dqy file. You do this in MS Query wizard panels.
1. On the "Query Wizard - Finish" panel, select the "Save Query" Button.
2. This will save the active query to your c:\documents and settings\[your
name here]\application data\microsoft\queries directory.
3. Right click on the .dqy file (to edit, if you double click it will open
and refresh the data) and Edit with Notepad
4. Within the .dqy you will see the full connection string to your database
-- modify as appropriate
5. Within the .dqy you will see the SQL stmt - modify as appropriate
6. Save .dqy using naming convention (so when it comes time to use, you know
which one).
7. Now instead of trying to modify MS Query with VBA, just write VBA to open
the appro. .dqy (see how it automatically refreshes when it opens?).

Can you take it from there?

--
Thx
MSweetG222



"Paul Grayson" wrote:

Hi,

Please can someone helpme with this. I have a pivot table that pulls data
from sql server and I will need to point the pivot data to different tables
depending on what results I want to see. How can I do this quickly through
code or a macro so it takes me straight to Microsoft Query so i can point the
pivot at another data source.

I have tried recording a macro to do this but it does not work.

Thanks

Paul