Try to change the path of the Query of pivot table in Excel 2000
 

Dear all,

I tried to change the folder location of access database
that a query or PivotTable using according to the article
269619, During the refreshing of data, i see two access
database lock files, one in my local harddisk(NewPath) and
another one in the server(old Path).

it looks like to me that I am actually using the new data
in the new path, but i am still having the connection with
the old access database in the network server.

Thanks
victor

=======================================
Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Sub QueryChange()
'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 = "\\oldserver\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.Sql = _
StringToArray(Application.Substitute(qy.Sql,
_
OldPath, NewPath))
qy.Refresh
Next qy
For Each pt In ws.PivotTables
pt.PivotCache.Connection = _
Application.Substitute(pt.PivotCache.Connection, _
OldPath, NewPath)
pt.PivotCache.Sql = _
StringToArray(Application.Substitute
(pt.PivotCache.Sql, _
OldPath, 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