How to create a new modified connection when I make a copy of a pivot table?

[Rhino_dance]

I have a pivot table which pulls data via a sql query. Let's call it 'Pivot Table 1' which has a Connection named 'Connection 1'. I want to make a copy of Pivot Table 1 (let's call that copy 'Pivot Table 2' ) and establish a new connection for it named 'Connection 2' and then modified the query in Connection 2 to pull slightly different data.

However, when I create Pivot Table 2 and change the name of the connection string to 'Connection 2' at the top of the Connections dialog box and modify the query this also changes the connection to Pivot Table 1. Excel thinks I want to use the same connection for both pivot tables and that I am simply Changing from 'Connection 1' to 'Connection 2'.

When this didn't work I then decided to create separate odc connection files for each pivot table by using a text editor on the Connection1.odc file and editing it to create a Connection2.odc file (I change the 'TITLE' and the Command Text). Now I'm trying to figure out how I can point each pivot table to its separate connection file.

Can anyone help?

Thanks

[noboffinme]

Hi Rhino_Dance,

Did you try creating each Pivot separately from its separate source?

[Rhino_dance]

Thanks for your response. No I didn't. I was trying to avoid that. I actually have a bunch of pivot tables that I want to create based on 'Pivot Table 1' and I wanted to avoid having to create each separately. However, when I make a copy of a sheet holding 'Pivot Table 1' the Pivot table on the new sheet is also named 'Pivot Table 1' by Excel. I thought it would be 'Pivot Table 2'. So I wasn't understanding that.

Now I've now renamed this copy of 'Pivot Table 1' to 'Pivot Table 2'. So the two pivot tables have separate names. But 'Pivot Table 2' is still tied to 'Connection 1'. So how do I created a different connection for 'Pivot Table 2' where I can then assign a different query? Or is a pivot table permanently tied to the connection that it had when it was created?

Thanks

[noboffinme]

Hi,

Could you do a test of 2 queries set up separately to test this theory?

I don't think renaming the Pivot or the Connection is making any difference to what's happening as you may be able to rename without changing any actual properties.

Do a small test & if it works there's your problem, also you would only need to set this up once with your separate queries.

Hope this helps.

[Rhino_dance]

I think you're right. Renaming the pivot is probably only useful for distinguishing it for the purposes of VBA code and such. It doesn't create a new pivot entity that can be assigned a new connection. Thanks