Need help updating Pivot table

Brent Blevins · #1 (**permalink**) 12-09-08, 09:06

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?

Brent Blevins · #2 (**permalink**) 12-09-08, 10:10

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"

shades · #3 (**permalink**) 12-10-08, 09:38

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech