Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically refreshing Pivot Table Range.
I have a Pivot Table which is populated by a worksheet which itself is
populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of the worksheet that has the data read into it, and use it for the Upper Row limit of the Pivot Table Range, using the following code: Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R" & dRowCount & "C29" Where dRowCount is my 'dynamic' number of rows returned by the SQL Statement. The problem is after my processes run, the Upper range for the Pivot table data is not the dynamic value I supply above. How can I get it so that I can dynamically and correctly reset the boundaries (rows) for the PivotTable cache to get it to include all of my rows? Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically refreshing Pivot Table Range.
Maybe you can use a dynamic range name
See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic Rob wrote: I have a Pivot Table which is populated by a worksheet which itself is populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of the worksheet that has the data read into it, and use it for the Upper Row limit of the Pivot Table Range, using the following code: Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R" & dRowCount & "C29" Where dRowCount is my 'dynamic' number of rows returned by the SQL Statement. The problem is after my processes run, the Upper range for the Pivot table data is not the dynamic value I supply above. How can I get it so that I can dynamically and correctly reset the boundaries (rows) for the PivotTable cache to get it to include all of my rows? Thank you -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically refreshing Pivot Table Range.
I've tried the Dynamic Range. It's adding columns, but it does not remove
columns. I will have situations where data is brought in dependant upon a date range. If the date range returns fewer records than the prior run, the range is not shrinking and blank rows are included. This then makes any attempts at trying to do grouping within the Pivot table impossible. Can the Dynamic Range shrink in size? "Dave Peterson" wrote: Maybe you can use a dynamic range name See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic Rob wrote: I have a Pivot Table which is populated by a worksheet which itself is populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of the worksheet that has the data read into it, and use it for the Upper Row limit of the Pivot Table Range, using the following code: Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R" & dRowCount & "C29" Where dRowCount is my 'dynamic' number of rows returned by the SQL Statement. The problem is after my processes run, the Upper range for the Pivot table data is not the dynamic value I supply above. How can I get it so that I can dynamically and correctly reset the boundaries (rows) for the PivotTable cache to get it to include all of my rows? Thank you -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically refreshing Pivot Table Range.
This formula taken from Debra Dalgleish's site:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) expects your data in column A to be contiguous--no blanks in column A. The last 1 in the formula tells excel that the range is one column wide. If you know the number of columns, you can change that 1 to what you need (say 13): =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),13) Or you can count the number of cells that are filled in row 1 (again no gaps allowed!): =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),Count a(sheet1!$1:$1)) === If this doesn't help, you may want to post the formula you used. Rob wrote: I've tried the Dynamic Range. It's adding columns, but it does not remove columns. I will have situations where data is brought in dependant upon a date range. If the date range returns fewer records than the prior run, the range is not shrinking and blank rows are included. This then makes any attempts at trying to do grouping within the Pivot table impossible. Can the Dynamic Range shrink in size? "Dave Peterson" wrote: Maybe you can use a dynamic range name See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic Rob wrote: I have a Pivot Table which is populated by a worksheet which itself is populated via a SQL Server Stored Proc, using OLE DB. I get the RowCount of the worksheet that has the data read into it, and use it for the Upper Row limit of the Pivot Table Range, using the following code: Sheets("Projected Shipments").PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "Sheet1!R1C1:R" & dRowCount & "C29" Where dRowCount is my 'dynamic' number of rows returned by the SQL Statement. The problem is after my processes run, the Upper range for the Pivot table data is not the dynamic value I supply above. How can I get it so that I can dynamically and correctly reset the boundaries (rows) for the PivotTable cache to get it to include all of my rows? Thank you -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refreshing a pivot table | Excel Discussion (Misc queries) | |||
Pivot table refreshing | Excel Discussion (Misc queries) | |||
When refreshing pivot tables my pivot table chart type changes | Excel Discussion (Misc queries) | |||
Refreshing a Pivot Table from a dynamic range | Excel Programming | |||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table | Excel Programming |