V
vs88
I'm using VBA code to extract data from a SQL database. The macro
clears a variable range and repopulates it with current data. Then it
refreshes a pivot table based on the data returned to the variable-
length named range. I am using PivotCache.MissingItemsLimit =
xlMissingItemsNone to eliminate phantom dropdown filter entries which
no longer exist in the underlying data, and that works fine. However,
I recently noticed that entries in the pivot table page and row
dropdown filters are no longer sorted alphabetically. Is there a
coding convention to address the sort order picked up in the pivot
filters?
Here is the current code:
Sheets("General_work").Select
ActiveSheet.AutoFilterMode = False
ActiveWorkbook.Names.Add Name:="OldData", RefersTo:= _
"=OFFSET(General_work!$A$2,0,0,COUNTA(General_work!$A:$A),46)"
Range("OldData").ClearContents
Call RefreshGeneral 'executes SQL DB refresh of General_work
Sheets("General_Pivot").Select
ActiveSheet.PivotTables("GeneralPivot1").PivotCache.MissingItemsLimit
= xlMissingItemsNone
ActiveSheet.PivotTables("GeneralPivot1").PivotCache.Refresh
One last comment-- I upgraded to Excel 2007 about 2 months ago, and
did not notice this problem before the upgrade. Prior to that, I'd
been using this code successfully for about 18 months. Don't know if
there's a connection there.
Any ideas on how to fix the page and row field dropdown sort order to
A-Z on the pivot table? Thanks!
--Vicki
clears a variable range and repopulates it with current data. Then it
refreshes a pivot table based on the data returned to the variable-
length named range. I am using PivotCache.MissingItemsLimit =
xlMissingItemsNone to eliminate phantom dropdown filter entries which
no longer exist in the underlying data, and that works fine. However,
I recently noticed that entries in the pivot table page and row
dropdown filters are no longer sorted alphabetically. Is there a
coding convention to address the sort order picked up in the pivot
filters?
Here is the current code:
Sheets("General_work").Select
ActiveSheet.AutoFilterMode = False
ActiveWorkbook.Names.Add Name:="OldData", RefersTo:= _
"=OFFSET(General_work!$A$2,0,0,COUNTA(General_work!$A:$A),46)"
Range("OldData").ClearContents
Call RefreshGeneral 'executes SQL DB refresh of General_work
Sheets("General_Pivot").Select
ActiveSheet.PivotTables("GeneralPivot1").PivotCache.MissingItemsLimit
= xlMissingItemsNone
ActiveSheet.PivotTables("GeneralPivot1").PivotCache.Refresh
One last comment-- I upgraded to Excel 2007 about 2 months ago, and
did not notice this problem before the upgrade. Prior to that, I'd
been using this code successfully for about 18 months. Don't know if
there's a connection there.
Any ideas on how to fix the page and row field dropdown sort order to
A-Z on the pivot table? Thanks!
--Vicki