Methods to Remove Data Connection but Preserve Data

[rynsmns]

If I run this vba it removes the external data connection to the pivot table:

ActiveWorkbook.Connections("EXAMPLE").Delete

When I attempt to drill down into the pivot table it states the data connection was removed.

Is there a way to remove the data connection but preserve the data in the pivot cache?

It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.

 

[BIGTONE559]

If I run this vba it removes the external data connection to the pivot table:

ActiveWorkbook.Connections("EXAMPLE").Delete

When I attempt to drill down into the pivot table it states the data connection was removed.

Is there a way to remove the data connection but preserve the data in the pivot cache?

It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.

Can i ask why you need the connection deleted?

Would the following work?

1. Refresh Data from the Data Connection
2. Copy Data Values to another location
3. Delete Data Connection
4. Generate Pivot Table from Copied Data?

 

[rynsmns]

That would probably work. I wanted to see if there were any other methods to preserve the pivot cache. My concerns center around the data connection and it's storage of my user name and password.

Can i ask why you need the connection deleted?

Would the following work?

1. Refresh Data from the Data Connection
2. Copy Data Values to another location
3. Delete Data Connection
4. Generate Pivot Table from Copied Data?

 

[BIGTONE559]

That would probably work. I wanted to see if there were any other methods to preserve the pivot cache. My concerns center around the data connection and it's storage of my user name and password.

i'll outline what i've done historically:

(Everything is done via VBA)

1. Establish Data Connection (Which gives the most current data set)
2. Copy Values to another location (With Headers)
3. Generate "Base" Pivot Table (with settings most used)

What do you think?

 

[rynsmns]

So after performing some research I determined that the connection didn't really need to be deleted. The password just needed to be protected.

So I read up on the PivotCache object which I use in my code and I found this on MSDN:
SavePassword Property [Excel 2003 VBA Language Reference]

I reference the pivotcache object as a variable called pc. All I needed to do was add one line of code to each of my macros:

pc.savepassword = false

So the macro runs, refreshes the external data connection, and clears the password.

The pivot table retains it's functionality and the password is protected.