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.

Option Explicit

‘ 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:

Private Sub Workbook_Open()
  PivotUpdate Worksheets(“MySheet”)
End Sub