Aggiornare una tabella pivot (con una macro, forse, inedita)
Una tabella pivot è un oggetto molto complesso, per cui non c’è da stupirsi che in ambito VBA abbondino le sue proprietà e metodi, un ginepraio da cui non è facile districarsi. Può essere di un (qualche) aiuto il Registratore di macro che, sulla creazione di un “pivot” (come d’ora in poi chiamerò alla breve tale oggetto che addirittura ha come nome completo un logorroico “rapporto tabella pivot”) applicato a un intervallo database alias elenco o tabella. Per fissare le idee diciamo che sia posto in un Foglio2 denominato ELENCO ORDINI a partire da una certa cella A6 e dotato di intestazioni del tipo COD, DATAORD, CLIENTE, QUANT, IMPORTO ecc. (in un ardito sussulto di originalità...). Sulle nostre specifiche azioni il Registratore ci spiattella qualcosa del genere:
Sub CreaPivot()
'
' CreaPivot Macro
Range("A6").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"ELENCO ORDINI!R6C1:R176C19", Version:=xlPivotTableVersion12) _
.CreatePivotTable TableDestination:="Tabella Pivot!R1C1", TableName:= _
"Tabella_pivot1", DefaultVersion:=xlPivotTableVersion12
Sheets("Tabella Pivot").Select
Cells(1, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Tabella_pivot1").PivotFields("CLIENTE")
.Orientation = xlRowField
.Position = 1
End With
. . . OMISSIS
End Sub
In sintesi, possiamo apprendere magari ricorrendo all’Help, che:
- il nostro pivot è racchiuso in un oggetto PivotCache facente parte dell’insieme PivotCaches della cartella di lavoro attiva ActiveWorkbook;
- la fonte dati è del tipo database Excel (SourceType:=xlDatabase) e deriva da una precisa SourceData, definita – si badi bene! – mediante una stringa di sintassi ovvero con indirizzo nel formato R1C1 anziché nel più usuale A1;
- il nome (default) di tale tabella pivot è “Tabella_pivot1” (un ulteriore pivot verrebbe denominato “Tabella_pivot2” ecc.); in genere ci conviene accettare tale nome.
Lascio come esercizio la comprensione delle successive istruzioni, che visualizzano il pivot creato in un foglio ad hoc Sheets(“Tabella Pivot)” che, dimenticavo, ho inserito preliminarmente. Mi limito a far notare che Tabella_pivot1 è anche il nome della tabella pivot incorporata in tale foglio dedicato.
Ma vale davvero la pena di sfruttare concretamente una macro del genere? A mio avviso potrebbe aver senso a due condizioni: a) si desidera un modello “leggero”, ossia privo di pivot; b) il modello non viene salvato completo di pivot, altrimenti si va incontro a confusione. In altri termini, il pivot viene creato al volo, una tantum.
Una prassi più ragionevole consiste nel creare manualmente un pivot in base a una situazione qualsiasi ma non vuota dell’elenco origine (SourceData, in VBA) affidandone la gestione sempre manuale ad un utente consapevole, che se la cavi con gli strumenti che il riquadro Elenco campi tabella pivot offre a iosa.
Nota. VBA rispecchia tali azioni e volendo si può affidare quelle permesse a un utente-zombie ad appositi pulsanti per macro. Chi ne ha voglia si accomodi, comunque questo non interessa il pigro che qui scrive.
Una questione però resta sul tappeto, dovuta al fatto che (a differenza di quanto avviene coi grafici normali) pivot e grafici tabelle pivot non si aggiornano automaticamente a fronte di modifiche dell’elenco “pivotato” (se si perdona l’estemporaneo quanto orrendo neologismo). Peggio che mai vanno le cose se quest’ultimo subisce aggiunte o cancellazione di record (en passant, è un mestiere che neanche i grafici classici sanno fare).
Qui ci vuole una macro. Ecco quella annunciata, con un certo non del tutto ingiustificato trionfalismo, nel titolo:
Sub AggiornaPivot()
MsgBox ActiveWorkbook.PivotCaches(1).SourceData ' Servito per debug
' Cambia l’elenco origine dei dati
ActiveWorkbook.PivotCaches(1).SourceData = _
Foglio2.Name & "!" & _
Range("InizioElenco").CurrentRegion.Address(True, True, xlR1C1)
' Rinfresca di fatto la tabella pivot
ActiveSheet.PivotTables("Tabella_pivot1").PivotCache.Refresh
End Sub
Premetto che né nei sacri testi né sul web si trova nulla del genere (eccezion fatta per i più zelanti, ovviamente), mentre abbondano le macro per modificare in vari mondi un pivot, in un primo tempo ho fortunosamente trovato il metodo Refresh applicabile all’oggetto PivotCache che a sua volta è un oggetto figlio della nostra PivotTables(“Tabella_pivot1”). Così ho sperato che l’ultima istruzione fosse sufficiente allo scopo di aggiornamento che mi prefiggevo. Illusione! Per due motivi:
1. soprattutto non tiene conto dell’aggiunta o cancellazione di record (per la cronaca, nemmeno con tabelle, oggetti ListObjects);
2. la modifica di un record (esistente), diciamo “Brambilla & C.” => “Brambilla Snc” creava nel pivot un risultato nuovo ma anomalo (divertitevi a trovarlo e, se ci riuscite, spiegatemi il motivo di tale bug).
Il rimedio, presto trovato, consiste nel riassegnare dinamicamente la sorgente dati – SourceData del nostro PivotCaches(1) della cartella di lavoro attiva. E qui è nato un ammattimento dovuto alla particolare sintassi della stringa da assegnare a SourceData, nella fattispecie "ELENCO ORDINI!R6C1:R176C19". Questa corrisponde a "ELENCO ORDINI!A6:S176" (Il nostro elenco originario si estendeva da riga6, colonna 1 a riga 176, colonna diciannovesima).
La seconda istruzione della macro risolve brillantemente (modestia a parte) il problema. Partendo dalla cella pre-denominata “InizioElenco” (*).
Nota (*) Che poi sarebbe la A6 dell’esempio, ma così la cosa assume carattere più generale e indipendente da eventuale inserimento di righe vuote in testa all’elenco.
Tale clou consiste nel concatenamento di Foglio2.Name & “!” che nel nostro esempiucolo darebbe “ELENCO ORDINI!” seguito da un Range("InizioElenco").CurrentRegion.Address(True, True, xlR1C1). Qui ricordo che CurrentRegion fornisce l’intero intervallo “attorno” alla cella “InizioElenco” ovvero il database corrente a patto che – attenzione! – le intestazioni si trovino a riga 1 o, quantomeno, siano precedute da una riga vuota e, inoltre, non vi siano “buchi” o righe aggiuntive fasulle in fondo. Il tutto culmina con la proprietà Address(True, True, xlR1C1) che grazie al terzo argomento fornisce l’agognato indirizzo nella sintassi dovuta.
Riordino, sovente necessario
Come si scopre, il più delle volte l’aggiornamento implica che non solo un nuovo record (come è da attendersi) ma anche un record soltanto modificato (come invece non ci si aspetta) venga collocato in fondo al pivot. In tali casi sarà opportuno prevedere un riordino. Il codice VBA, che potrebbe essere inserito in fondo alla routine AggiornaPivot, è del tipo seguente (notare la variante, rispetto ad ActiveSheet, che evoca il foglio di nome “Tabella Pivot“ a distanza, ovvero anche se tale foglio non è al momento attivo:
With Worksheets("Tabella Pivot").PivotTables("Tabella_pivot1")
.PivotFields("CLIENTE").AutoSort xlAscending, "CLIENTE"
End With
Qui viene chiamato in gioco PivotFields ovvero l’insieme dei campi del pivot, di cui nell’esempio si considera quello etichettato “CLIENTE”, al quale a sua volta viene applicato il metodo Autosort con opzioni di limpida semantica: xlAscending e “CLIENTE” (chiave di sort, guardacaso il campo in questione.
La scelta di tale campo dipende ovviamente dal caso concreto. Per concludere, si esamini la Sub seguente che esplora i vari PivotFields messaggiandone i nomi:
Sub EsploraCampiPivot()
Dim CampiPivot As PivotFields, CampoPivot As PivotField
Set CampiPivot = _
Worksheets("Tabella Pivot").PivotTables("Tabella_pivot1").PivotFields
For Each CampoPivot In CampiPivot
MsgBox CampoPivot.Name
Next
End Sub
?>
?>