Pivotcache
 

Using excel 2003 we have a sheet that accesses a view on a sql server. The
workbook has multiple pivot tables using the first as the source of the
data. In VBA is there any was to unlink or unshare that pivotcache?

Pivotcache
 

Here is the code I'm using it's excel 2003

Public Sub StartHere_Click()

Dim ObjWb As Excel.Workbook



Report = InputBox("Type path and workbook name here")

Set ObjWb = Workbooks.Open(Report)

Set ObjWbCur = Workbooks("PivotTableConverter.xls")



For icount = 1 To ObjWb.Sheets.Count

If ObjWb.Sheets(icount).Name < "Source" And Sheets(icount).Name <
"DATA" And Sheets(icount).Name < "ChangeCon" And Sheets(icount).Name <
"Dispo_List" And Sheets(icount).Name < "Legend" Then

query =
ObjWb.Sheets(icount).PivotTables(1).PivotCache.Com mandText

sname = ObjWb.Sheets(icount).Name

sconnection =
ObjWb.Sheets(icount).PivotTables(1).PivotCache.Con nection

stGotIt = StrReverse(query)

stGotIt = Left(stGotIt, InStr(1, stGotIt, " ", vbTextCompare))

ViewName = StrReverse(Trim(stGotIt))

Set CurCell = ObjWbCur.Worksheets("Source").Cells(icount, 1)

Set CurCell1 = ObjWbCur.Worksheets("Source").Cells(icount, 2)

CurCell.Value = sname

CurCell1.Value = ViewName

Else: End If





Next icount



MsgBox "Now type in new view names in column b. If another pivot table is
the source of a sheet leave it blank. If the views are the same name just
copy and paste from column b to column c. Remeber to leave them blank if the
sheet source is another pivot table"



Call ChangeCon(ObjWb, ObjWbCur)



End Sub









Sub ChangeCon(ObjWb, ObjWbCur)



' sconnection = Sheets(1).PivotTables(1).PivotCache.Connection

' Set CurCell = Worksheets("ChangeCon").Cells(1, 1)

' CurCell.Value = sconnection

' NewCon = InputBox("Type in string above and change DataBase, to type in
the registered tradmark use alt - 0174")





' For icount = 1 To Sheets.Count

' If Sheets(icount).Name < "Source" And Sheets(icount).Name <
"Data" And Sheets(icount).Name < "ChangeCon" And Sheets(icount).Name <
"ViewDiffer" Then

' Sheets(icount).PivotTables(1).PivotCache.Connectio n = NewCon

' Else: End If

' Next icount



OldDB = InputBox("Type in old Database name")

NewDB = InputBox("Type in new Database name")



For icount = 1 To ObjWb.Sheets.Count



If ObjWb.Sheets(icount).Name < "Source" And
ObjWb.Sheets(icount).Name < "DATA" And ObjWb.Sheets(icount).Name <
"Dispo_List" And ObjWb.Sheets(icount).Name < "Legend" Then

Set CurCell = ObjWbCur.Worksheets("Source").Cells(icount, 3)

If CurCell < "" Then

newview = CurCell.Value

query =
ObjWb.Sheets(icount).PivotTables(1).PivotCache.Com mandText



stGotIt = StrReverse(query)

stGotIt = Left(stGotIt, InStr(1, stGotIt, " ",
vbTextCompare))

OldView = StrReverse(Trim(stGotIt))



NewQuery = Replace(query, OldView, newview)

CommText = Replace(NewQuery, OldDB, NewDB)

'InterComm = SplitString(CommText)


ObjWb.Sheets(icount).PivotTables(1).PivotCache.Com mandText = CommText



Else: End If

Else: End If

Next icount

End Sub