La CurrentPage delle tabelle pivot, con le date può far ammattire...
A volte qualcuno mi sottopone quesiti particolari, inattesi. Ecco l’ultimo, relativo alla proprietà CurrentPage di un oggetto PivotField di una tabella pivot:
“Come mai l’istruzione seguente che operava correttamente in Excel 2003:
ActiveSheet.PivotTables(NomeTabPivo).PivotFields(DaAna).CurrentPage = DomDaCer
Con la versione 2007 dà errore a run-time?
La DomDaCerc è una variabile di tipo Date e la pagina corrente è relativa a un campo DaAna anch’esso di tipo data.
Non dispongo più di Excel 2003, pertanto non sono in grado di avvalorare o smentire il dubbio del nostro amico, che nel frattempo mi ha comunicato che, in Excel 2007, ha corretto il (presunto) bug traducendo tutte le date in stringhe, alias etichette nel gergo Excel.
In un successivo ritaglio di tempo ho fatto delle prove su questo database posto su un foglio MioDb comprendente un campo Data con date del tipo 5/12/2011 (formato data):
|
Prodotto |
Area |
Data |
Fatturato |
|
Bulloni |
Centro |
11/05/2011 |
€ 5.079,00 |
|
Bulloni |
Centro |
03/10/2011 |
€ 7.400,00 |
|
Bulloni |
Nord |
11/05/2011 |
€ 5.145,00 |
|
Bulloni |
Centro |
11/05/2011 |
€ 6.283,00 |
|
Bulloni |
Sud |
15/08/2011 |
€ 6.409,00 |
|
Bulloni |
Sud |
15/08/2011 |
€ 6.959,00 |
|
Bulloni |
Centro |
03/10/2011 |
€ 3.013,00 |
|
Dadi |
Nord |
11/05/2011 |
€ 5.079,00 |
|
Dadi |
Centro |
11/05/2011 |
€ 2.636,00 |
|
Dadi |
Centro |
15/08/2011 |
€ 6.283,00 |
|
Dadi |
Centro |
03/10/2011 |
€ 6.409,00 |
|
Dadi |
Centro |
03/10/2011 |
€ 6.959,00 |
|
Dadi |
Nord |
15/08/2011 |
€ 3.013,00 |
|
Rondelle |
Nord |
15/08/2011 |
€ 6.929,00 |
|
Rondelle |
Sud |
03/10/2011 |
€ 4.827,00 |
|
Rondelle |
Sud |
11/05/2011 |
€ 6.283,00 |
|
Rondelle |
Sud |
15/08/2011 |
€ 6.409,00 |
|
Rondelle |
Sud |
15/08/2011 |
€ 6.959,00 |
|
Viti |
Nord |
15/08/2011 |
€ 3.013,00 |
|
Viti |
Centro |
15/08/2011 |
€ 4.864,00 |
|
Viti |
Nord |
15/08/2011 |
€ 4.192,00 |
|
|
|
|
(ho omesso intestazioni di riga e colonna, visto che l’elenco parte dalla cella A1)
A MioDb ho appioppato una tabella Pivot Multidim1, posta su altro foglio FoglioPivot:
Nota. La figura qui sotto mostra la situazione in cui il campo Data della tabella pivot viene filtrato secondo la data 11/05/2011 e di conseguenza tutti gli altri valori sono inerenti a tale data). Cliccando sulla casella a discesa si scoprono tutti gli elementi mediante l’opzione (Tutto), ovvero: 11/05/2011; 15/08/2011; 03/10/2011; (Tutto). Rivedere la figura precedente e notare che a (Tutto) corrisponde in VBA (inglese!) la sgtringa “(All)”.
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
Data |
11/05/2011 |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
3 |
|
|
Prodotto |
|
|
|
|
|
4 |
Area |
Dati |
Bulloni |
Dadi |
Rondelle |
Totale complessivo |
|
|
5 |
Centro |
Somma di Fatturato |
11362 |
2636 |
|
13998 |
|
|
6 |
|
Somma di fatturato2 |
11362 |
2636 |
|
13998 |
|
|
7 |
Nord |
Somma di Fatturato |
5145 |
5079 |
|
10224 |
|
|
8 |
|
Somma di Fatturato2 |
5145 |
5079 |
|
10224 |
|
|
9 |
Sud |
Somma di Fatturato |
|
|
6283 |
6283 |
|
|
10 |
|
Somma di Fatturato2 |
|
|
6283 |
6283 |
|
|
11 |
Somma di Fatturato totale |
|
16507 |
7715 |
6283 |
30505 |
|
|
12 |
Somma di Fatturato2 totale |
|
16507 |
7715 |
6283 |
30505 |
|
|
13 |
|
|
|
|
|
|
|
Per comodità del visitatore riporto poi quel che dice la Guida VBA a proposito di CurrentPage:
|
PivotField.CurrentPage, proprietà |
Restituisce o imposta la pagina corrente visualizzata per il campo pagina. È valida solo per i campi pagina. Proprietà di tipo PivotItem di lettura/scrittura.
Sintassi
espressione.CurrentPage
espressione Variabile che rappresenta un oggetto PivotField.
Esempio
In questo esempio viene restituito il nome della pagina corrente per il rapporto di tabella pivot di Sheet1 all'interno della variabile di tipo string strPgName.
|
Visual Basic, Application Edition |
|
Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
strPgName = pvtTable.PivotFields("Country").CurrentPage.Name |
Precisazione importante. A quanto sopra aggiungo che Pagina è un particolare campo della tabella pivot, in pratica è posto nella sua parte in alto. Il campo Data nel nostro caso. A tale proposito riporto un’istruzione che aggiunge appunto un campo del genere (il codice in grassetto dovrebbe chiarire il concetto):
With ActiveSheet
.PivotTables("Multidim1").AddFields RowFields:= _
"Area", ColumnFields:="Prodotto", PageFields:="Data"
.PivotTables("Multidim1").PivotFields("Fatturato"). _
Orientation = xlDataField
.Name = "AnalisiDim"
End With
Tornando all’assillo relativo al campo Data, ecco il codice adottato da chi scrive, che funziona regolarmente in Excel 2007:
Dim mioPivot As PivotTable
Set mioPivot = Sheets(“FoglioPivot”).PivotTables("Multidim1")
mioPivot.PivotCache.Refresh ' Aggiorna tabella pivot
Dim MiaData As Date
MiaData = #5/11/2011#
mioPivot.PivotFields("Data").CurrentPage = MiaData
Nota Si badi bene che per aggiornare il pivot occorre un’istruzione del tipo .PivotCache.Refresh da non confondere con .Update, metodo da usare quando il db originario, specie se esterno, ha subito modifiche.
Insomma non c’è da modificare le date in stringhe, occorre solo che il formato-data sia coerente con quello usato nel MioDB in quanto la tabella pivot mostra “quel che si vede” – a livello formato intendo sottolineare. Faccio notare che in ambiente VBA il formato INGLESE mese/giorno/anno viene regolarmente tradotto in italiano ossia 5/12/2011 ma, attenzione!, se digito il valore assegnato a MiaData come #May 12 20011# esso viene accettato ma automaticamente e inesorabilmente tradotto in #5/12/2011#. E se la formattazione adottata nel database è differente si ottiene un rigetto.
Per la cronaca anche quest’altro tentativo, che utilizza una cella di nome MiaData va a buon fine, ma sempre a condizione di avere lo stesso formato data dei campo presenbte sul database:
mioPivot.PivotFields("DataOrdine").CurrentPage = Range(“MiaData”).Value ' NB - Senza .Value si ha errore
Infine volendo ripristinare i valori globali del pivot si usa l’istruzione:
mioPivot.PivotFields("Data").CurrentPage = "(All)"
Repetita iuvant: non va dimenticato il metodo Refresh del pivot – o, più esattamente, della sua PivotCache - fra un’impostazione e l’altra della CurrentPage.
L’intera routine per creazione automatica della tabella pivot
La riporto qui sotto, affidandone l’esegesi a chi ha sufficiente padronanza del VBA:
Dim SwPivot As Boolean ' Definito a livello Dichiarazioni
Sub CreaPivot()
If SwPivot Then Exit Sub
With Sheets("AnalisiVendite")
.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=.Cells(1, 1).CurrentRegion, TableDestination:="", _
TableName:="Multidim1", HasAutoFormat:=True, _
SaveData:=True
End With
With ActiveSheet
.PivotTables("Multidim1").AddFields RowFields:= _
"Area", ColumnFields:="Prodotto", PageFields:="Data"
.PivotTables("Multidim1").PivotFields("Fatturato"). _
Orientation = xlDataField
.Name = "AnalisiDim"
End With
SwPivot = True
End Sub
Per togliere di mezzo il pivot potremmo ricorrere a quest’altra Sub:
Sub EliminaPivot()
If SwPivot = False Then Exit Sub
Application.DisplayAlerts = False
Sheets("AnalisiDim").Delete
Sheets("AnalisiVendite").Activate
SwPivot = False 'Ridà la possibilità di creare il pivot
End Sub
?>
?>