Microsoft KB Archive/272922

= HOW TO: Change the ODBC Data Source for a PivotTable in Excel 2000 =

PSS ID Number: 272922

Article Last Modified on 11/4/2002

-

The information in this article applies to:


 * Microsoft Excel 2000

-



This article was previously published under Q272922



IN THIS TASK

 * SUMMARY
 * ** Example Macro to Change Data Source
 * REFERENCES



SUMMARY
In Excel 2000, there is no built-in interface for dynamically changing the ODBC data source for a PivotTable. This article describes how you can use Microsoft Visual Basic for Applications to programmatically change the data source for a PivotTable in Excel. Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

back to the top

Example Macro to Change Data Source
To use Visual Basic for Applications to programmatically change the data source for a PivotTable in Excel, follow these steps:  Start Excel 2000. Open the file that contains the PivotTable that you want to modify. On the Tools menu, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module.  In the module, type the following macro code: Sub ListSourceData 'Declare our variables. Dim newSheet As Worksheet, sdArray As Variant Dim oldSheet As Worksheet, pt As PivotTable, r As Integer

'Set our variables. Set oldSheet = ActiveSheet Set newSheet = ActiveWorkbook.Worksheets.Add

newSheet.Range(&quot;A1&quot;).Value = oldSheet.Name r = 3 'Loop through each PivotTable on the active sheet and place its ODBC information on a new sheet. For Each pt In oldSheet.PivotTables newSheet.Cells(r, 1).Value = pt.Name newSheet.Cells(r + 1, 1).Value = pt.PivotCache.Connection newSheet.Cells(r + 2, 1).Value = pt.PivotCache.Sql r = r + 4 Next pt   newSheet.Cells.EntireColumn.ColumnWidth = 100 newSheet.Cells.EntireRow.AutoFit End Sub

Sub ChangeServer 'Declare our variables. Dim ptc As PivotCache, oldSrv As String, newSrv As String

'Request the name of the old server/file name. oldSrv = InputBox(&quot;Input the name of the old server or file path as listed in the Pivot Tables SQL string.&quot;)

'Request the name of the new server/file name. newSrv = InputBox(&quot;Input the name of the new server or file path which you want the Pivot Table to point to.&quot;)

'Replace the ODBC information of whatever PivotTable is currently active. Set ptc = ActiveCell.PivotTable.PivotCache ptc.Connection = Application.Substitute(ptc.Connection, oldSrv, newSrv) ptc.Sql = Application.Substitute(ptc.Sql, oldSrv, newSrv) End Sub

NOTE: The sample macro that is in this article only functions if the PivotTable is created from an external data source.  On the File menu, click Close and Return to Microsoft Excel. Switch to the sheet that contains your PivotTable.</li> On the Tools menu, point to Macro, and then click Macros.</li> In the list of macros, select the ListSourceData macro. Click Run.</li> A new sheet is created with the PivotTable name, ODBC connection string, and SQL string. Make a note of the current file path or server name, starting from the word &quot;FROM&quot; (without quotation marks but in all uppercase letters).</li> Switch to the sheet that contains the PivotTable that you want to change, and then click any cell inside the PivotTable to activate it.</li> On the Tools menu, point to Macro, and then click Macros.</li> In the list of macros, select the Change Server macro. Click Run.</li> In the dialog box that appears, type the server name or file name of the old ODBC data source, exactly as the ListSourceData macro shows it. Click OK.</li> In the next dialog box that appears, type the server name or file name of the new ODBC data source, in the same format as the old ODBC data source. Click OK. Your PivotTable should be updated.</li></ol>

back to the top

<div class="references_section">