P
Pivot Table Pete
Our company had 200 + excel reports that we automatically refresh the pivot
tables each day and email them out. When we do a server migration or copy
for a new program we go into each pivot table and add the new view from the
new database. I have successfully changed the command text to the new view
on the new database, but to save file size we usually try to link pivot
tables to one pivot table if the fields/view is the same. When I try to do
it on these on one of those I get an error. Does anyone have a solution for
this? Is there a better way to manage all these excel reports?
Help! Frustrated Buissness Intelligence analyst.
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)
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
tables each day and email them out. When we do a server migration or copy
for a new program we go into each pivot table and add the new view from the
new database. I have successfully changed the command text to the new view
on the new database, but to save file size we usually try to link pivot
tables to one pivot table if the fields/view is the same. When I try to do
it on these on one of those I get an error. Does anyone have a solution for
this? Is there a better way to manage all these excel reports?
Help! Frustrated Buissness Intelligence analyst.
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)
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