Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I add an item to a PivotCache with VBA? | Excel Discussion (Misc queries) | |||
Update PivotCache | Excel Programming | |||
Delete PivotCache | Excel Programming | |||
Changing PivotCache SQL Database | Excel Programming |