How to change connection information for a query in Excel

Article translations Article translations
Article ID: 816562 - View products that this article applies to.
For a Microsoft Excel 97 version of this article, see 269619.
Expand all | Collapse all


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))
           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
                    On Error GoTo 0
                    Application.ScreenUpdating = False
                    Set rng = pt.TableRange2
                    pt.TableRange2.Copy Workbooks.Add(xlWorksheet).Worksheets(1) _
                    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
            Next pt
    Next ws
    End Sub
    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)
       Next i
       StringToArray = Temp
    End 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: May 13, 2008 - Revision: 8.0
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
kbbug kbinfo KB816562

Give Feedback


Contact us for more help

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