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
(http://support.microsoft.com/kb/816562/
)
.
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.
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
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.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
