Relink di tabelle pivot da sorgente MS-Access
La scorsa settimana mi è capitato di dover gestire l’ennesimo problema relativo ad i link di MS-Excel.
Un mio collega ha generato una tabella pivot contenente dati provenienti da una query di MS-Access.
Questa pivot viene successivamente utilizzata per alimentare alcuni grafici di Excel ed alcune slides in Power Point.
Basta aggiornare di volta in volta i dati nel file mdb per aggiornare a cascata anche i grafici e le slides.
C’è un solo vincolo a tutto ciò. E’ necessario che i files restino sempre nella stessa posizione pena la perdita del collegamento dei dati sorgenti nella tabella pivot.
Purtroppo, dopo aver fatto il rilascio dell’applicativo presso il cliente non è stato semplice mettere a posto il link al file mdb.
Come risolvere agevolmente il problema?
La prima idea che mi è venuta in mente è stata quella di utilizzare un percorso assoluto disponibile in tutti i sistemi Windows like (es: c:\).
Non è una soluzione elegante ma di sicuro è veloce.
Ho provato ad impiegare un DSN su file contenente tutte le informazioni necessarie per un collegamento alla tabella tramite ODBC.
Non ha funzionato perchè una volta creato il link, Excel prende tutte le informazioni e le conserva nel file. Di fatto modificare i parametri contenuti nel file DSN per aggiornare il link non ha senso dal momento che il file DSN non verrà più interrogato da Excel.
A questo punto ho deciso di mettere mano al codice e di risolvere una volta per tutte questo problema.
La soluzione proposta:
Di seguito una piccola sub che può essere applicata a qualunque pivot, per consentire l’aggiornamento dei link.
L’unico presupposto per il corretto funzionamento del codice è che tutti i files (xls ed mdb) si trovino nella stessa directory.
Sbirciando il codice noterete quanto sia grezzo il sistema con cui viene risolto il problema ma quando il tempo a disposizione è poco ci si accontenta anche di questo.
‘ Questa funzione prende in input un worksheet e
‘ per ogni pivot in essa contenuta controlla che
‘ il link sia valido. In caso negativo sostituisce il
‘ link con il percorso del file corrente.
Public Sub PivotUpdate(ByRef ws As Worksheet)
Dim pt As PivotTable
Dim OldPath As String, NewPath As String
Dim aPath() As String
Dim intStart As Integer
Dim IntStop As Integer
For Each pt In ws.PivotTables
’ Un modo bizzarro per controllare la correttezza del
’ link. In caso di errore, estriamo dalla descrizione
’ del problema il nome del file da linkare e sostiuiamo
’ il path con quello del file xls.
On Error Resume Next
pt.PivotCache.Refresh
If Err <> 0 Then
intStart = InStr(1, Err.Description, “”"”)
IntStop = (InStr(intStart + 1, Err.Description, “”"”))
aPath = Split(Mid(Err.Description, intStart + 1, IntStop – intStart – 1), “\”)
OldPath = LCase(Replace(Mid(Err.Description, intStart + 1, IntStop – intStart – 1), “\” & aPath(UBound(aPath)), “”))
NewPath = LCase(ActiveWorkbook.Path)
Err.Clear
On Error GoTo 0
’Da questo punto in avanti, se c’è un problema è meglio
’Segnalarlo all’utente.
pt.PivotCache.Connection = Replace(LCase(pt.PivotCache.Connection), OldPath, NewPath)
pt.PivotCache.CommandText = Replace(LCase(pt.PivotCache.CommandText), OldPath, NewPath)
pt.PivotCache.Refresh
End If
Next pt
End Sub
Per aggiornare una pivot all’avvio del workbook è sufficiente richiamare la subroutine:
PivotUpdate Worksheets(“MySheet”)
End Sub