+ Reply to Thread
Results 1 to 2 of 2

PivotCache.Commandtext

  1. #1
    Pivot Table Pete
    Guest

    PivotCache.Commandtext

    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.



  2. #2
    Pivot Table Pete
    Guest

    Re: PivotCache.Commandtext

    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



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1