CODEWITHSUNDEEP
excelpivot-table
Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

Change PivotTable data source without validating the connection

I've developing a solution for a client that includes an Excel template with a PivotTable. The PivotTable's data source is a query against a SQL Server. Before I send the template to my client, I'd like to change the connection string to reference my client's SQL Server instead of my local server. The problem is, that connection string doesn't work on my computer, so Excel throws an error when I try to edit the connection and won't save the new connection string.

What's really frustrating about this is that I somehow did this successfully about a week ago, but can't remember how. I have a version of the spreadsheet that I successfully created with the external connection string. Unfortunately, I had to make a change to the query, and trying to make that change to the existing spreadsheet results in the same problem -- Excel tries to connect to the data source to validate the query, and fails to do so.

How can I change the connection string without Excel trying to refresh the data? I know it can be done, because I've done it, but I have no idea what I did!

Upvotes: 0

Views: 712

Answers (1)

MP24
MP24

Reputation: 3200

You will be able to suppress the auto-refresh by setting the .EnableRefresh = False property of your PivotCache.

However, you will need to somehow reset the property to True upon start of your spreadsheet or delivery to the client, else the pivot table won't be able to refresh at all.

Upvotes: 1

Related Questions