Microsoft KB Archive/269619

= How to change connection information for a query in Excel 97 =

Article ID: 269619

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q269619





For a Microsoft Excel 2000, Microsoft Excel 2002, and Microsoft Office Excel 2003 version of this article, see 816562.



SUMMARY
After you change the folder location or server name of a database that a query or PivotTable is using, you cannot manually change the connection information to the new folder or server. This article offers a programmatic solution.



MORE INFORMATION
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. Sub QueryChange Dim sh As Worksheet, qy As QueryTable Dim pt As PivotTable, pc As PivotCache Dim OldPath As String, NewPath 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 = &quot;C:\OldPath\Folder&quot; NewPath = &quot;C:\NewPath\Folder&quot; For Each ws In ActiveWorkbook.Sheets For Each qy In ws.QueryTables qy.Connection = _ Application.Substitute(LCase(qy.Connection), _              LCase(OldPath), LCase(NewPath)) qy.Sql = _ StringToArray(Application.Substitute(LCase(qy.Sql), _ LCase(OldPath), LCase(NewPath))) qy.Refresh Next qy     For Each pt In ws.PivotTables pt.PivotCache.Connection = _ Application.Substitute(LCase(pt.PivotCache.Connection), _              LCase(OldPath), LCase(NewPath)) pt.PivotCache.Sql = _ StringToArray(Application.Substitute(LCase(pt.PivotCache.Sql), _ LCase(OldPath), LCase(NewPath))) pt.PivotCache.Refresh 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

Known Issues with This Code

 * The previous code may not work as expected if you are using shared PivotCaches, an OLAP based PivotTables, or a Multiple Consolidation Range based PivotTables to connect to the database.
 * In Microsoft Excel 97, if the connection information changes and the PivotTable is refreshed, you receive an error message, and the subroutine does not work. To work around this problem, close and reopen the file, and then run the subroutine before you try to refresh the PivotTable.

Note This problem does not occur in Microsoft Excel 2000 or later.
 * If multiple PivotTables on a worksheet are derived from the same PivotTable, the subroutine does not work after it processes the first PivotTable. There is no known workaround for this problem.

Additional query words: XL2002 XL2000 XL97 XL

Keywords: kbhowto KB269619

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.