Pivotcache

[Pivot Table Pete]

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?

 

[Pivot Table Pete]

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.CommandText

sname = ObjWb.Sheets(icount).Name

sconnection =
ObjWb.Sheets(icount).PivotTables(1).PivotCache.Connection

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.Connection = 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.CommandText



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.CommandText = CommText



Else: End If

Else: End If

Next icount

End Sub