PivotCache to use all rows?

[lameid]

I have an existing workbook that has a bunch of Pivottables based on the same Pivotcache. I made the mistake of not setting it to use enough rows. I am trying to write a macro that changes the pivotcache to use all rows. I receive an error on the red line below. Error 1004 Application-defined or object-defined error.

I am using Excel 2003. Can anybody tell me how to fix this or how to do it manually (short of recreating all the pivot tables)?

Sub PivotTablesAllRows()
    Dim PCache As PivotCache
    Dim PCaches As PivotCaches
    Dim lngRowStart As Long
    Dim strRowStart As String
    Dim strColEnd As String
    Set PCaches = ActiveWorkbook.PivotCaches
    
    For Each PCache In PCaches
        lngRowStart = InStr(1, PCache.SourceData, ":R") + 1
        strRowStart = Left(PCache.SourceData, lngRowStart)
        strColEnd = Right(PCache.SourceData, 1 + Len(PCache.SourceData)_
           - InStr(lngRowStart, PCache.SourceData, "C"))
        [red]PCache.SourceData = strRowStart & "65536" & strColEnd[/red]
    Next PCache
    
End Sub

 

[SkipVought]

Hi,

Why would you include every row in the sheet? In many cases that I would use (cases that include dates and other numeric data that I often GROUP) that would cause grouping to be non-functional! Not in my book!

1. I you want to assign the sourcedata to the entire table, use the CurrentRegion and Address method. Assuming that your table starts in A1 on SourceDataSheetObject...

With SourceDataSheetObject
  PCache.SourceData = .Name & "!" & .[A1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
end with

2. I most often use a dynamic named range, using the OFFSET function in Insert > Name > Define. You can use Database or some other name and the technique in faq68-1331.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I am thinking every row so that I can replace the source data, potentially with more data and the pivot tables still work after the obligatory refresh data.

That said, I adapted your code for testing purposes and the right side still returns a valid looking Range reference but I still receive the same error.

My version of your expression returns...

SheetName!$A$1:$AH$41125

I of course replaced the real sheet name. I'd post it but the test computer is not networked.

I had a thought... maybe Excel doesn't want to edit the pivotcache... maybe what it really need to do is to add a new pivotcache and point the pivottables to it? Does that seem right/necessary?

If that seems like a good approach, any thoughts on how to delete the original pivotcache?

Unfortunately I don't have the time to really kitchen sink this myself right now but I also need a solution quickly.

 

[SkipVought]

you did not seem to include the ReferenceStyle argument that returns R1C1 address. Don't ask me why.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I typed your code and on this 1024X768 screen the line wraps and I missed it. Anyways, my original code fails too and does use the R1C1 address format.

 

[AnotherHiggins]

I strongly second Skip's second suggestion: Dynamic Named Ranges.

I've been doing a lot of this recently.... I'm looking at some reports someone in another department created. They were going through 60 Pivot Tables every month and manually changing the source data in each one!?!? Ugh.

I created a Dynamic Named Range for the source and changed each of the Pivot Table's "Refers To," to the Range Name - something like =rngPTSource

All you have to do is refresh the PTs and you're done.

As a starting point, here's the formula for the range I've been using.
[tab][COLOR=blue white]=OFFSET(INDIRECT("Data!$A$1"), 0, 0, COUNTA(INDIRECT("Data!$A:$A")), COUNTA(INDIRECT("Data!$1:$1")))[/color]
The key is to do your CountAs on a column and a row that will never be empty (column A and row 1 in the sample above).

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[lameid]

anotherhiggins,

Won't changing the pivot table source in effect create multiple files thereby increasing the file? I'm still not quite that knowledgeable about Excel... I'll have to look up dynamic named ranges and related functions.

Anyway if I could update the pivotcache, it would not matter and if I follow this method I will need to to prevent bloat anyways.

Skip,

I did add the referencestyle and I receive the same error as my original code / OP.

The right side now returns...
SheetName!R1C1:R41125C34

 

[SkipVought]

is your sheet containing the source data named SheetName?

If NOT ...

AND it happens to contain SPACES...

With SourceDataSheetObject
  PCache.SourceData = "'" & .Name & "'!" & .[A1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

No spaces in the sheet name... it is actually

qry_Adjusted_Volumes_For_Report

My current version of code, hopefully it does not wrap on your screen... Obviously, I've got some clean up to do once it works.

Sub PivotTablesAllRows()
    Dim PCache As PivotCache
    Dim PCaches As PivotCaches
    Dim lngRowStart As Long
    Dim strRowStart As String
    Dim strColEnd As String
    Dim XLSheet As Worksheet
    Set PCaches = ActiveWorkbook.PivotCaches
    
    For Each PCache In PCaches
         'This for the loop...
'        lngRowStart = InStr(1, PCache.SourceData, ":R") + 1
'        strRowStart = Left(PCache.SourceData, lngRowStart)
'        strColEnd = Right(PCache.SourceData, 1 + Len(PCache.SourceData) - InStr(lngRowStart, PCache.SourceData, "C"))
'        PCache.SourceData = strRowStart & "65536" & strColEnd 'Application-defined or object-defined error.
        
        'Or this for the loop
        Set XLSheet = ActiveWorkbook.Worksheets(Left(PCache.SourceData, InStr(1, PCache.SourceData, "!") - 1))
        PCache.SourceData = XLSheet.Name & "!" & XLSheet.[A1].CurrentRegion.Address(ReferenceStyle:=xlR1C1) 'Application-defined or object-defined error.
    Next PCache
    Set XLSheet = Nothing
    Set PCache = Nothing
    Set PCaches = Nothing
End Sub

 

[lameid]

For grins I embedded the single quotes and it still bombs...

'qry_Adjusted_Volumes_For_Report'!R1C1:R41125C34

 

[SkipVought]

and you have valid headings in all 34 columns (A:AH)?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Considering

? Pcache.sourcedata
qry_Adjusted_Volumes_For_Report!R1C1:R4088C34

I'd say all the column names are valid.

I did see this thread...

http://www.pcreview.co.uk/forums/thread-1004422.php

and while I can't set the sourcetype property I can tell you it is set to xldatabase and not xlconsolidation.

I haven't looked that one up yet.

 

[lameid]

Nevermind that link the TGML ate it... I closed the window and forgot how I found it.

Anyways someone was creating a new Pivotcache using a similar link and it was suggested to change sourcetype to xlconsolidation.

There was no response. I thought it may be indicative to someone in the know.

I found Excel help not helpful on the sourcetype property for pivotcache.

 

[SkipVought]

This is a QueryTable, yes?

The QueryTable has a NAME, that adjusts with the number of rows returned.

Did you ever try using the QueryTable Name in the SourceData
[tt]
qry_Adjusted_Volumes_For_Report!YourQT_Name
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Weak on Excel...

I used docmd.transferspreadsheet to dump the SS originally and then automated Excel to to create some pivot tables. I have subsequently manually added some pivot tables that contain filters and beyond my automation function's support/management.

Anyway, this is where I create the original pivot cache. XL_PT_ID is a number... Can you stear me the right way?

 'Create a Pivot Table
         strTableName = "PivotTable" & XL_PT_ID
         'If statement keeps additional pivotcaches from being added for iterative calls.
         'Assumes there is only one data sheet and it is either the source of the existing pivot OR there is only one data sheet and it is the last sheet.
         
         If XLWorkBook.PivotCaches.Count = 0 Then
            XLWorkBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
               .Range(XLSheet.Range("A1"), XLSheet.Range("A1").SpecialCells(xlLastCell))).CreatePivotTable TableDestination:=XLRptSheet.Cells(3, 1), TableName:= _
                strTableName
            '.ActiveSheet.PivotTableWizard TableDestination:=.ActiveSheet.Cells(3, 1)
            '.ActiveSheet.PivotTableWizard TableDestination:=XLRptSheet.Cells(3, 1)
         Else
            XLWorkBook.Worksheets(2).PivotTables( _
                1).PivotCache.CreatePivotTable TableDestination:=XLRptSheet.Cells(3, 1), TableName _
                :=strTableName
           '.ActiveSheet.PivotTableWizard TableDestination:=XLRptSheet.Cells(3, 1)
         End If
        XLRptSheet.Activate 'Before specifying TableDestination for first pivot and creating sheet, IF block above created new sheet, else block did not w

 

[SkipVought]

Is there a reason for creating the PT with VBA?

With all the VBA coding I've done over the past 15 years, I could probably count on one finger the number of times I've coded creating a PT. The wizard is just so convenient, at least for the kind of work that I do.

Furthermore, the OFFSET function is so convenient and simple, I do it in my sleep. Keeps all my PTs locked on the current scope of data in my source sheets, and I do often have MULTIPLE PTs on MULTIPLE sources in an application. All the code that I might use is to loop thru the PivotCaches and REFRESH.

I think that you're making this MUCH TOO DIFFICULT!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Sorry, I was running to a meeting... A querytable... ODBC/Sql source. Got it now.

No, the source is an Excel Worksheet. It was originally dumped from Access using docmd.transferspreadsheet.

So there is no querytable name.

Since I didn't know what a query table was, I thought it would have to be in the code that created it originally...

I went down that path because it seemed like the right way to go; I obviously didn't know about the dynamic range solution or my code may have exported to Excel and opened two files and copied from the source and pasted in the destination. It is hard to say because I may have been more concerned with a user killing the base file or modifying it.

Now I have a workbook that was originally created with around 6 pivot tables via automation code. Around 6 more PT Sheets have been added manually along the way with annoying filters to deal with temporary implementation issues (garbage in, garbage out). Since I want to maintain my filters, I want to update the source pivot cache that the pivottables use. Based on what I have seen in the UI I assume this has to be done programmatically. Re-entering the PT wizard and modifying the source range apparently creates a new pivot cache. By the time I changed them all manually, my ~10MB file was ~190MB (proably because I went for all rows).

So now that we have established how to do it next time, what about this file?

 

[lameid]

I got my Spreadsheet updated, however the red text is never true. I manually forced it to execute the true block once at runtime. I could get this to work with a boolean variable but it doesn't quite make sense to me that Isempty does not work.

Sub PivotTablesAllRows()
    [green]'Assumes One existing Pivotcache
    'Pivotcache apparently does not want to be edited
    'Creates New pivotcache utilizing all records on existing data sheet[/green]
    Dim PT As PivotTable
    Dim PTTemp As PivotTable
    Dim shtWork As Worksheet
    Dim XLSheet As Worksheet
    Dim XLtmpSheet As Worksheet
    For Each shtWork In ActiveWorkbook.Worksheets
        
        For Each PT In shtWork.PivotTables
            If [b][red]IsEmpty(PTTemp)[/red][/b] Then [green]'Does not detect uninitialized variable properly...
                        'Manually changing executing line appropriately causes code 
                        'to run successfully[/green]
                Set XLtmpSheet = ActiveWorkbook.Worksheets.Add(Before:=ActiveWorkbook.Worksheets(1))
                XLtmpSheet.Name = "Deleteme"
                Set XLSheet = ActiveWorkbook.Worksheets(Left(PT.PivotCache.SourceData, InStr(1, PT.PivotCache.SourceData, "!") - 1))
                Set PTTemp = ActiveWorkbook.PivotCaches.Add(xlDatabase, XLSheet.Name & "!" & _
                    XLSheet.[A1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)).CreatePivotTable(TableDestination:=XLtmpSheet.Cells(3, 1), TableName:="PTTEMP")
                Set XLSheet = Nothing
            End If
            PT.CacheIndex = PTTemp.CacheIndex
        Next PT
    Next shtWork
    
    XLtmpSheet.Delete
    Set PTTemp = Nothing
    Set PT = Nothing
    Set XLtmpSheet = Nothing
    Set shtWork = Nothing
End Sub

 

[lameid]

My brain must be on strike...

If PTTemp Is Nothing Then