Methods to Remove Data Connection but Preserve Data

rynsmns

New Member
Joined
Jun 22, 2010
Messages
16
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

Active Member
Joined
Apr 20, 2011
Messages
336
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

New Member
Joined
Jun 22, 2010
Messages
16
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

Active Member
Joined
Apr 20, 2011
Messages
336
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

New Member
Joined
Jun 22, 2010
Messages
16
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:

Code:
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.
 

Forum statistics

Threads
1,081,624
Messages
5,360,093
Members
400,570
Latest member
Ben Morgan 1985

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top