Help! Stupid CommandText Problem

[El Pablo]

Hi,

I'm developping a small Excel/Access based project for my company. In one
excel files, I have some pivottables that are linked to an Access DB. I'm
trying to program a way to make the files portable from one computer to
another. Everything works well for querytable, but I have some problem with
the pivotcache.commandtext property. It seems that it is a read-only
property on something like that.

Here's a part of my code:

Sub ChangeLink()
Dim wsh As Worksheet
Dim szOldLoc As String
Dim szOldPath As String
Dim szCurrentLoc As String
Dim szCurrentPath As String
Dim pt As PivotTable
Dim pc As PivotCache
Dim lDBQIndex As Long
Dim lDDIndex As Long 'DefaultDir index
Dim lQryIndex As Long
Const DBQ As String = "DBQ="
Const DEFAULTDIR As String = "DefaultDir="
Const FROM As String = "FROM `"
Const DBNAME As String = "WN05.mdb"
Const EXT As String = ".mdb"
Dim lNextSemiColon As Long

Set wsh = ActiveSheet

szCurrentPath = ActiveWorkbook.Path
szCurrentLoc = ActiveWorkbook.Path & "\" & DBNAME

For Each pt In wsh.PivotTables
Set pc = pt.PivotCache
'First Step change DBQ
lDBQIndex = InStr(1, pc.Connection, DBQ)
'Trouve le prochain point-virgule
lNextSemiColon = InStr(lDBQIndex, pc.Connection, ";")

szOldLoc = Mid(pc.Connection, lDBQIndex + Len(DBQ), _
lNextSemiColon - (lDBQIndex + Len(DBQ)))

pc.Connection = Replace(pc.Connection, _
DBQ + szOldLoc, DBQ + szCurrentLoc)

'Second step change DefaultDir
lDDIndex = InStr(1, pc.Connection, DEFAULTDIR)
lNextSemiColon = InStr(lDDIndex, pc.Connection, ";")

szOldPath = Mid(pc.Connection, lDDIndex + Len(DEFAULTDIR), _
lNextSemiColon - (lDDIndex + Len(DEFAULTDIR)))

pc.Connection = Replace(pc.Connection, _
DEFAULTDIR + szOldPath, DEFAULTDIR + szCurrentPath)

'Third step: Change query
lQryIndex = InStr(1, pc.CommandText, FROM)
lNextSemiColon = InStr(lQryIndex + Len(FROM), pc.CommandText, _
"`")
szOldPath = Mid(pc.CommandText, lQryIndex + Len(FROM), _
lNextSemiColon - (lQryIndex + Len(FROM)))

'CommandText n'est pas une string
Dim szTest As Variant

szTest = pc.CommandText
'StringToArray (Application.Substitute(pc.CommandText, _
szOldPath, Replace(szCurrentLoc, EXT, "")))

pc.CommandText = szTest 'Error here!! WTF! ********************
pc.Refresh

Next

End Sub


As you can see, I'm just trying to copy the commandText to himself via a
Variant object. But I'm always getting this error: Run-time error '1004'
Application-defined
or object error.

I'm almost done with this project except for this stupid line that I'm
working on still the beginning of the day...

Please help!