After you change the folder location or the server name of a
database that a query or PivotTable is using in Microsoft Excel, you cannot
manually change the connection information to the new folder or to the server. This
article offers a programmatic solution.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements.
To use the following Microsoft Visual Basic for
Applications (VBA) macro code in Excel, follow these steps:
- Start Excel.
- In an Excel workbook, press ALT+F11 to open the Visual
- On the Insert menu, click
- On the new module code sheet, type or paste the following
Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim rng As Range
'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 = "C:\OldPath\Folder"
NewPath = "C:\NewPath\Folder"
For Each ws In ActiveWorkbook.Sheets
For Each qy In ws.QueryTables
qy.Connection = _
qy.CommandText = _
For Each pt In ws.PivotTables
pt.PivotCache.Connection = _
On Error Resume Next
pt.PivotCache.CommandText = _
If Err.Number <> 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set rng = pt.TableRange2
pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
ActiveCell.PivotTable.PivotCache.CommandText = _
Set pt = rng.PivotTable
Application.ScreenUpdating = True
Function StringToArray(Query As String) As Variant
Const StrLen = 127
Dim NumElems As Integer
Dim Temp() As String
NumElems = (Len(Query) / StrLen) + 1
ReDim Temp(1 To NumElems) As String
For i = 1 To NumElems
Temp(i) = Mid(Query, ((i - 1) * StrLen) + 1, StrLen)
StringToArray = Temp
- On the File menu, click Close and
Return to Microsoft Excel.
- To save the changes to your Excel workbook, click Save on the
The previous code may not work as you expect if you are using shared PivotCaches, an OLAP-based PivotTables, or a Multiple Consolidation Range-based PivotTables to connect to the database.
When DSN is used for the connection method, if one of the following two conditions is operated by ODBC data source administrator, it is connectable to use the server after you make the change.
- Change the server name of the corresponding data source.
- Delete the corresponding data source, and then keep the data source as the same name.
If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. As of March 2003, there is no known workaround for this problem.
Article ID: 816562 - Last Review: May 13, 2008 - Revision: 8.0
- Microsoft Office Excel 2003
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition