PRB: You Cannot Change the Data Source for a PivotTable in Excel for an Analysis Services Connection

Article translations Article translations
Article ID: 327572 - View products that this article applies to.
This article was previously published under Q327572
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

SYMPTOMS

After you change the server name of the computer that is running Analysis Services that an Excel PivotTable is using, you cannot manually change the connection information to the new folder or server.

WORKAROUND

NOTE: Microsoft has tested the following code sample on Excel 2000.

To work around this problem, use Visual Basic for Applications to programmatically change the data source for a PivotTable in Excel for an Analysis Services connection:
  1. Start Excel 2000.
  2. Open the file that contains the PivotTable (or PivotTables) that you want to modify.
  3. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  4. On the Insert menu, click Module.
  5. In the module, insert the following macro code:
    Option Explicit
    
    Sub ChangeServer()
    
        Dim sh As Worksheet, qy As QueryTable
        Dim pt As PivotTable, pc As PivotCache
        Dim OldPath As String, NewPath As String
        Dim strOld As String, strNew As String
    
        ' Replace the following paths with the original path or 
        ' server name where your database resided, and the new 
        ' path or server name where your database now resides.
        
        OldPath = "<old server name>"
        NewPath = "<new server name>"
    
        For Each sh In ActiveWorkbook.Sheets
    
            For Each pt In sh.PivotTables
    
                strOld = pt.PivotCache.Connection
                strNew = Replace(strOld, OldPath, NewPath)
                pt.PivotCache.Connection = strNew
                pt.PivotCache.Refresh
    
            Next pt
    
        Next sh
    
    End Sub
    					
  6. Change OldPath and NewPath to the appropriate server name. If you use a server that does not exist for NewPath, the multidimensional connection wizard opens when you perform step 12 of this procedure.
  7. On the File menu, click Close, and then return to Microsoft Excel.
  8. Switch to the first sheet.
  9. On the Tools menu, point to Macro, and then click Macros.
  10. In the list of macros, select the ChangeServer macro, and then click Run.
  11. A dialog box opens in which you can change the server and that lists the available cubes. Changing the cube does not change the cube for your queries that are using this code.
  12. The dialog box opens for each of the PivotTables in turn.
  13. Update your PivotTable server name.
  14. Save the spreadsheet. You may want to remove the macro before you distribute the spreadsheet.

Properties

Article ID: 327572 - Last Review: February 27, 2014 - Revision: 5.5
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Analysis Services
Keywords: 
kbnosurvey kbarchive kbprb KB327572

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com