You are currently offline, waiting for your internet to reconnect

How to change connection information for a query in Excel

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Excel 97 version of this article, see 269619.
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:
  1. Start Excel.
  2. In an Excel workbook, press ALT+F11 to open the Visual Basic Editor.
  3. On the Insert menu, click Module.
  4. On the new module code sheet, type or paste the following macro code:
    Sub QueryChange()     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 = _                  Application.Substitute(qy.Connection, _                  OldPath, NewPath)            qy.CommandText = _                  StringToArray(Application.Substitute(qy.CommandText, _                  OldPath, NewPath))            qy.Refresh       Next qy       For Each pt In ws.PivotTables            pt.PivotCache.Connection = _                  Application.Substitute(pt.PivotCache.Connection, _                  OldPath, NewPath)            On Error Resume Next            pt.PivotCache.CommandText = _                  StringToArray(Application.Substitute(pt.PivotCache.CommandText, _                  OldPath, NewPath))            If Err.Number <> 0 Then                Err.Clear                On Error GoTo 0                Application.ScreenUpdating = False                Set rng = pt.TableRange2                pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _                           .Range("A1")                ActiveCell.PivotTable.PivotCache.CommandText = _                           StringToArray(Application.Substitute(pt.PivotCache.CommandText, _                           OldPath, NewPath))                ActiveCell.PivotTable.TableRange2.Copy pt.TableRange2                ActiveWorkbook.Close False                Set pt = rng.PivotTable                Application.ScreenUpdating = True            End If            pt.PivotCache.Refresh        Next ptNext wsEnd SubFunction 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)   Next i   StringToArray = TempEnd Function
  5. On the File menu, click Close and Return to Microsoft Excel.
  6. To save the changes to your Excel workbook, click Save on the File menu.
Note 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: 12/08/2015 02:14:42 - Revision: 8.0

Microsoft Office Excel 2003, Microsoft Excel 2002 Standard Edition, Microsoft Excel 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbinfo KB816562