How to change connection information for a query in Excel 97 (269619)

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 = "C:\OldPath\Folder"
   NewPath = "C:\NewPath\Folder"
   
   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.