Changing ODBC datasource for a Pivot Table

[Sean Lambert]

I have a number of Pivot Tables that were created using an
'on-the-fly' data source (see below for a description of how the data
source was created), as opposed to using a ODBC DSN. We are changing
the name of our server, and since the Pivot tables weren't created
using a DSN, I can't simply update the DSN with the new server name
for the Pivot tables to continue to work.

Is there an embedded property somewhere within the spreadsheet that
specifies the connection properties (server, DB, login, etc), so I can
change the properties on each spreadsheet instead of having to
recreate each Pivot Table in order to point to the new server?


The process for creating the 'on-the-fly' data source...

Select PivotTable and PivotChart Report from the Data menu.

Select External Data Source, click Next

Click Get Data...

Select <New Data Source> and click OK (this would be the location
where I would typically select the existing data source)

Name the data source

Select SQL Server for the driver to use

Click Connect

SQL Server Login window appears, select the Server, login information
and the Database and click OK. It connects to the data source

Click OK twice and you're able to select the available tables and
columns for the Pivot Table.


If I open the spreadsheet up in Notepad, I can see a SERVER property
with the old server name in it, but I'm not sure how to access that
property within Excel. ANY help would be very much appreciated.


Thanks.

sean

 

[Dick Kusleika]

Sean

The only way I know to access that is through VBA. Specifically, you need
the Connection and CommandText properties of the PivotCache object. From
the Immediate Window

?Sheet1.PivotTables(1).PivotCache.Connection
?Sheet1.PivotTables(1).PivotCache.CommandText

Connection is the connection string and will show the table you are using.
CommandText is the SQL statement, but you should look at it because it may
also identify the table, specifically in the FROM clause.

 

[Sean Lambert]

I resolved the problem by putting the following code into the
spreadsheet, running it and then deleting it. When the spreadsheet is
saved, the new connection properties are saved with it. Now I just
have to figure out how to automate this process, so I don't have to go
into each spreadsheet (I have about 90 of them) and manually update
them with this script. Anyone have any ideas on the automation piece
of this task?



Private Sub Workbook_Open()
Dim myConnSringNew As String

'Get current connection information
Set MyCache = ActiveSheet.PivotTables("PivotTable1").PivotCache
myConnString = MyCache.Connection

'Write the current connection information to the Immediate window
to verify
Debug.Print myConnString

'Set the new connection information and refresh the PivotCache
myConnStringNew =
"ODBC;DSN=NewServerName;Description=NewServerName;UID=username;PWD=passwd;APP=Microsoft®
Query;WSID=PRODPLNR;DATABASE=DatabaseName;Network=DBMSSOCN;Address=NewServerName,1433"
Debug.Print myConnStringNew
MyCache.Connection = myConnStringNew
MyCache.Refresh
End Sub