Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Unanswered: Need help updating Pivot table

    I am trying to automate updates to a pivot table. I used the macro recorder to get the syntax below:

    Code:
    ActiveSheet.PivotTables("InboundCalls").PivotFields ("[CallDates]").AddPageItem _
            "[CallDates].[All CallDates].[2008].[December].[5]", True
    I then updated the code to include my date variables so that the report will always be updates with the current date:

    Code:
     
    'Get information about today's date
        strDate = Date
        strMonth = Format(strDate, "m")
        strMonthName = MonthName(strMonth)
        strDay = Format(strDate, "d")
        strYear = Format(strDate, "yyyy")
        strReportDate = strYear & strMonth & strDay
        
    'Change the date on the first page
        Sheets("Summary").Select
        Cells(2, 1) = strDate
    'Update the first 4 tabs (Set the Year Month and Day)
        Sheets("LecDaily").Select
        ActiveSheet.PivotTables("InboundCalls").PivotFields("[CallDates]").AddPageItem _
        "[CallDates].[All CallDates]." & [strYear] & "." & [strMonthName] & "." & [strDay] & ", True"
    When the code executes I get an error that says:
    Runtime error 1004
    The item could not be found in the OLAP cube.

    Any ideas on what might be causing this?

  2. #2
    Join Date
    Jun 2007
    Posts
    74
    I know it has to be something with the way I am using the variables because it works without the variables.

    Any Ideas?

    I also tried this:

    Code:
     
    Sheets("LecDaily").Select
    ActiveSheet.PivotTables("InboundCalls").PivotFields("[CallDates]").AddPageItem _
    "[CallDates].[All CallDates].[" & strYear & "].[" & strMonthName & "].[" & strDay & "], True"
    Last edited by Brent Blevins; 12-09-08 at 15:56.

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. It might be easier to rebuild the entire Pivot Table with each update. If you look at Chapter 12 of VBA and Macros for MS Excel, by Bill Jelen, et al you will find all that you need. It not only explains the updating process, but provides several working examples. It actually is as fast as trying to update one item, and it cleans out all the other stuff that accumulates when trying to update. Once I began using that approach, it solved several other problems as well, such as getting around the limitations of the Pivot Tables.

    For me, it was worth buying the book for that chapter alone.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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