wisselen van bronbestand in excel met draaitabellen/grafieken

[oogstweg 2]

Ik heb een rapportagesetje (diverse draaitabellen en grafieken) gemaakt op basis van een excelblad.
Dit blad bevat financiële gegevens van afdeling 1 in tabelvorm. Het is een export uit een pakket, genaamd 'cijfers juni afdeling 1'. Voor verschillende afdelingen of perioden kan deze export gemaakt worden, dit is dan altijd in dezelfde structuur en een wisselend aantal records.

Nu wil ik het rapportagesetje dat ik voor afdeling 1 heb gemaakt ook gebruiken voor afdeling 2, ik wil dus wisselen van brontabel. Het werkblad voor afdeling 2 is, zoals aangegeven, met exact dezelfde structuur gemaakt als die van afdeling 1.

Ik kan dat doen door de tabel met gegevens afdeling 2 in het rapportagesetje te kopieren/plakken op Blad1, zodat de gegevens van afdeling 1 worden vervangen door die van afdeling 2. Daarbij moet ik rekening houden dat het aantal regels kan afwijken, en dus de extra regels verwijderen of toevoegen bij de definitie van de draaitabellen. Daarna kunnen de draaitabellen hun werk doen.

Ik zocht naar een mogelijkheid om vanuit mijn rapportagesetje een gegevensverbinding te maken met een bron. Ik heb de volgende macro opgenomen en zou halvewege graag handmatig het bronbestand willen selecteren en op het laatst de tabel willen hernoemen naar Tabel1, onafhankelijk van de bestaande naam (die is in onderstaande macro Tabel_afd1_1, maar dat kan wisselen):

Sub ChangeBron()
'
' ChangeBron Macro
'

'
    ActiveSheet.ListObjects("Tabel1").Unlink
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=H:\H Bestanden Gert\Kantooradministratie\afd1.x" _
        , _
        "lsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Datab" _
        , _
        "ase Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global " _
        , _
        "Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;J" _
        , _
        "et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support" _
        , " Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False"), _
        Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("Afd1$")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "H:\H Bestanden Gert\Kantooradministratie\Afd1.xlsx"
        .ListObject.DisplayName = "Tabel_Afd1_1"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.ListObjects("Tabel_Afd1_1").Name = "Tabel1"
    Range("B3").Select
End Sub