Re: Changing ODBC datasource for a Pivot Table

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Dick Kusleika (dickk_at_paragonUNMUNGEconstructioninc.com)
Date: 03/12/04 

Date: Fri, 12 Mar 2004 08:29:03 -0600

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. 

-- 
*** Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Sean Lambert" <slambert007@yahoo.com> wrote in message
news:279f38c0.0403111158.2a37816f@posting.google.com...
> 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 spread*** that
> specifies the connection properties (server, DB, login, etc), so I can
> change the properties on each spread*** 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 spread*** 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
Quantcast