Werken Met tabellen in Excel (VBA)

Inleiding

In Werken Met tabellen in Excel is beschreven hoe u de tabel optie van Excel gebruikt. Deze pagina beschrijft hoe u met VBA werkt met tabellen.

Het is gewoon een ListObject…

Aan de VBA kant lijkt er niets nieuw aan de nieuwe tabellen. Ze worden als ListObjects aangeduid, een collectie die is geïntroduceerd met Excel 2003. Maar er zijn significante wijzigingen in dit deel van het objectmodel en daarvan zal een deel hier worden getoond.

Een bereik tot tabel maken

Het converteren van een bereik naar een tabel is simpel:

Sub MaakTabel()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), _
                                , xlYes).Name = "Tabel1"
    ActiveSheet.ListObjects("Tabel1").TabelStyle = "TabelStyleLight2"
End Sub

De opmaak van de tabel wordt bepaald door TabelStyles (tabel stijlen). Een collectie objecten welke deel uitmaken van het Workbook object. Omdat tabelstijlen dus niet onderdeel van de Excel applicatie zijn, gebeuren er dingen die u misschien niet zou verwachten. U kunt onderdelen van een tabelstijl bijvoorbeeld als volgt veranderen:

Sub ChangeTabelStyles()
ActiveWorkbook.TabelStyles(2).TabelStyleElements(xlWholeTabel) _
.Borders(xlEdgeBottom).LineStyle = xlDash
End Sub

Hiermee verandert de lijnstijl van de onderkant van uw tabel in gestreept. Maar let op! Alle tabellen in alle open Excel bestanden met diezelfde tabelstijl veranderen mee. Maar zodra u het bestand opslaat (en de andere bestanden eventueel ook), Excel sluit en het bestand weer opent, dan zijn deze wijzigingen verdwenen! Dit komt omdat de code hierboven een ingebouwde tabelstijl heeft veranderd. Vreemd genoeg is dit dus wel (tijdelijk) toegestaan, ondanks dat de wijzigingen dus niet worden opgeslagen!

Wilt u volledige controle over uw tabelstijlen, dan dient u een bestaande tabelstijl te dupliceren en deze (nu "aangepaste" stijl) te modificeren. Dergelijke aangepaste stijlen maken wél deel uit van het bestand en wijzigingen eraan worden wel opgeslagen.

Alle tabellen tonen

Laten we eens beginnen met het tonen van alle tabellen die in een werkblad te vinden zijn:

Sub VindAlleTabellenOpBlad()
    Dim oSh As Worksheet
    Dim oLo As ListObject
    Set oSh = ActiveSheet
    For Each oLo In oSh.ListObjects
        Application.Goto oLo.Range
        MsgBox "Tabel gevonden: " & oLo.Name & ", " _
                & oLo.Range.Address
    Next
End Sub

Delen van een tabel selecteren

Zeer waarschijnlijk wilt u via VBA wel eens met een specifiek deel van een tabel aan het werk gaan. Hieronder staat een serie voorbeelden die laten zien hoe dat werkt.

Sub DeelVanTabelSelecteren()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    '1: Met het listobject
    With oSh.ListObjects("Tabel1")
        'Toon de naam van uw tabel
        MsgBox .Name
        'Selecteer hele tabel
        .Range.Select
        'Select alleen gegevens van hele tabel
        '(niet de koprij)
        .DataBodyRange.Select
        'Selecteer derde kolom
        .ListColumns(3).Range.Select
        'Selecteer alleen data in eerste kolom
        .ListColumns(1).DataBodyRange.Select
        'Selecteer rij 4(kopregel telt niet mee)
        .ListRows(4).Range.Select
    End With

    '2: Met het range object
    'selecteer hele kolom (alleen data)
    oSh.Range("Tabel1[Column2]").Select
    'selecteer hele kolom(data plus koprij)
    oSh.Range("Tabel1[[#All],[Column1]]").Select
    'Selecteer hele tabel (alleen data)
    oSh.Range("Tabel1").Select
    'selecteer gehele tabel
    oSh.Range("Tabel1[#All]").Select
    'Selecteer één rij
    oSh.Range("A5:F5").Select
End Sub

Misschien is het u opgevallen, dat Excel tabellen ook behandelt alsof het bereiknamen betreft. Dit is precies wat er ook aan de hand is. Zodra u een bereik omzet in een tabel voegt Excel automatisch een bereiknaam toe die de hele tabel omvat. Deze bereiknamen worden door Excel wel op een bijzondere manier behandeld. Zo kunt u bereiknamen die horen bij een tabel niet verwijderen en worden ze automatisch hernoemd als u de naam van een tabel aanpast. Bij verwijderen van een tabel verdwijnt ook de bijbehorende bereiknaam.

Rijen en kolommen invoegen

Een onderdeel waarbij het grootste deel van de functionaliteit al bestond in Excel 2003. Er is een beperkt aantal dingen nieuw, waaronder het "AlwaysInsert" argument bij de ListRows.Add methode:

Sub TabelInvoegVoorbeelden()
'Kolom invoegen op een specifieke plek
    Selection.ListObject.ListColumns.Add Position:=4
'Kolom invoegen rechts van huidige
    Selection.ListObject.ListColumns.Add
'Rij invoegen boven huidige
    Selection.ListObject.ListRows.Add (11)
'Rij invoegen onder huidige (kan alleen onderin de tabel)
    Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub

Als je vervolgens iets moet doen met de nieuwe rij, maak dan een object variabele die naar de nieuwe rij verwijst:

Dim oNewRow As ListRow
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)

Wil je vervolgens iets in de eerste cel van de nieuwe rij schrijven, dan gaat dat als volgt:

oNewRow .Range.Cells(1,1).Value="Waarde voor nieuwe cel"

Commentaar toevoegen aan een tabel

Het is niet direct duidelijk waar in de gebruikersinterface u het commentaar van een tabel zou moeten invoegen of wijzigen, omdat dit in de optie Namen Beheren op de tab "Formules" van het lint moet worden gedaan. Het is wel een erg handige optie, omdat dit commentaar zichtbaar wordt gemaakt op het moment dat u een formule invoert:

AutoAanvullen toont commentaar

In VBA is de syntax:

Sub CommentaarBijTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Voeg commentaar toe aan een tabel
    '(wordt dus gekoppeld aan de bijbehorende bereiknaam)
    oSh.ListObjects("Tabel_NorthWind").Comment = _
    "Deze tabel bevat gegevens uit de NorthWind database"
End Sub
Een tabel omzetten in een normaal bereik

Een tabel omzetten in een normaal bereik

De code hiervoor is erg eenvoudig:

Sub VerwijderTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Maak tabel gewoon bereik
    oSh.ListObjects("Tabel1").Unlist
End Sub

Speciale functies: Sorteren en filteren

Met tabellen krijgen we een hele nieuwe set filter- en sorteer mogelijkheden. Hieronder wordt daarvan slechts een tipje van de sluier opgelicht. Wilt u meer VBA code zien, dan kunt u het beste de macro recorder inschakelen en uw acties uitvoeren. Mogelijkheden omvatten onder andere: het Sorteren op kleur (handig als u bepaalde cellen van uw tabel even een kleurtje heeft gegeven om later iets mee te doen), filteren op de kleur van de tekst etcetera.

Sub SorterenEnFilteren()
    With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Tabel1")
        'Vorige sorteeropties verwijderen
        .Sort.SortFields.Clear
        'Sortering op celkleur instellen
        .Sort.SortFields.Add( _
                Range("Tabel1[[#All],[Column2]]"), _
                xlSortOnCellColor, xlAscending, , _
                xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
        With .Sort
            'Andere sorteeropties instellen
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            'Sorteren
            .Apply
        End With
    End With
    'Filteren op de kleur van de tekst
    ActiveSheet.ListObjects("Tabel1").Range.AutoFilter Field:=2, _
        Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub

De formattering van een cel in de tabel opvragen

Je vraagt je wellicht af waarom deze paragraaf hier staat, je kan toch gewoon cell.Interior.ThemeColor gebruiken om de ThemeColor van een cel in de tabel te bekijken? Nee, helaas niet. Omdat de formattering van een cel in een tabel volledig bepaald wordt door de tabel stijl en de plaats in de tabel moet je deze beide gegevens opvragen en combineren om het juiste formatteringsgedeeldte uit de tabel stijl te halen:

Bepaal waar in de tabel de cel zich bevindt (op eerste rij, in eerste kolom, op totaal rij,...)
Bepaal de tabel instellingen: Staat rij strepen aan, is de eerste rij een koprij, ...
Gebaseerd op deze informatie kan het juiste TableStyleElement worden opgevraagd uit de tabel stijl.

De functie hieronder geeft een TableStyleElement terug dat hoort bij een cel oCell in een tabel oLo:

Function GetStyleElementFromTableCell(oCell As Range, oLo As ListObject) As TableStyleElement
'-------------------------------------------------------------------------
' Procedure : GetStyleElementFromTableCell
' Company   : JKP Application Development Services (c)
' Author    : Jan Karel Pieterse
' Created   : 2-6-2009
' Purpose   : Function to return the proper style element from a cell inside a table
'-------------------------------------------------------------------------
    Dim lRow As Long
    Dim lCol As Long
    'Determine on what row we are inside the table
    lRow = oCell.Row - oLo.DataBodyRange.Cells(1, 1).Row
    lCol = oCell.Column - oLo.DataBodyRange.Cells(1, 1).Column

    With oLo
        If lRow < 0 And .ShowHeaders Then
            'on first row and has header
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlHeaderRow)
        ElseIf .ShowTableStyleFirstColumn And lCol = 0 Then
            'On first column and has first column style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlFirstColumn)
        ElseIf .ShowTableStyleLastColumn And lCol = oLo.Range.Columns.Count - 1 Then
            'On last column and has last col style
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlLastColumn)
        ElseIf lRow = .DataBodyRange.Rows.Count And .ShowTotals Then
            'On last row and has total row
            Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlTotalRow)
        Else
            If .ShowTableStyleColumnStripes And Not .ShowTableStyleRowStripes Then
                'in table, has column stripes
                If lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleRowStripes And Not .ShowTableStyleColumnStripes Then
                'in table, has column stripes
                If lRow Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            ElseIf .ShowTableStyleColumnStripes And .ShowTableStyleRowStripes Then
                If lRow Mod 2 = 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                ElseIf lRow Mod 2 <> 0 And lCol Mod 2 = 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
                ElseIf lRow Mod 2 = 0 And lCol Mod 2 <> 0 Then
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
                Else
                    Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
                End If
            End If
        End If
    End With

End Function

Je kan deze functie als volgt gebruiken:

Sub test()
    Dim oLo As ListObject
    Dim oTSt As TableStyleElement
    Set oLo = ActiveSheet.ListObjects(1)
    Set oTSt = GetStyleElementFromTableCell(ActiveCell, oLo)
    With ActiveCell.Offset(, 8)
        .Interior.ThemeColor = oTSt.Interior.ThemeColor
        .Interior.TintAndShade = oTSt.Interior.TintAndShade
    End With
End Sub

Merk op, dat de functie geen rekening houdt met het gegeven dat je bij een tabel in kan stellen dat er meer dan 1 rij en/of kolom dezelfde formattering moet krijgen, dus bijvoorbeeld dat telkens 2 rijen donker zijn en dan weer twee rijen licht.

Formattering verwijderen van een tabel

Stel dat je net een bereik naar een tabel hebt omgezet, maar het oorspronkelijke bereik had je voorzien van allerlei opmaak zoals randen en opvulkleuren. Tabellen hebben hun eigen tabel stijlen, maar die overschrijven formattering die je zelf hebt gedaan niet. Wat je kunt doen is de Standaard stijl (Zie dit artikel) toepassen op de tabel, maar dat zorgt ervoor dat al je getalsopmaak verdwijnt. Onderstaand macrootje maakt eerst een nieuwe stijl en zet het getalsopmaak gedeelte van die stijl uit. Als die stijl op de tabel wordt toegepast behoud je de getalsopmaak.

Sub RemoveFormattingOfTable()
    Dim oStNormalNoNum As Style
    On Error Resume Next
    Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
    On Error GoTo 0
    If oStNormalNoNum Is Nothing Then
        ActiveWorkbook.Styles.Add "NormalNoNum"
        Set oStNormalNoNum = ActiveWorkbook.Styles("NormalNoNum")
        oStNormalNoNum.IncludeNumber = False
    End If
    With ActiveSheet.ListObjects(1)
        .Range.Style = "NormalNoNum"
        'Now apply tablestyle:
        .TableStyle = "TableStyleLight1"
    End With
    ActiveWorkbook.Styles("NormalNoNum").Delete
End Sub

Conclusie

Natuurlijk is er meer te weten over tabellen en lijsten als het gaat om het objectmodel in VBA. Een hele goede manier om hierover meer te weten te komen is door macro’s op te nemen tijdens het doen van allerlei aanpassingen aan uw tabel.

Vragen, suggesties en opmerkingen

Al het commentaar over deze pagina:

Commentaar van: Marc Bittremieux (5-9-2009 01:05:46) deeplink naar dit commentaar

Ik zoek een mogelijkheid in excel (2007) om rijen en kolommen te transponeren. Maar niet op de klassieke wijze.
Graag wil ik een oorsprongtabel transponeren, met behoud van de oorsprongtabel, waarbij latere wijzigingen in de oorsprongtabel ook automatisch in de getransponeerde tabel wijzigen.
De cellen in de getransponeerde tabel moeten dus geen 'waarden' zijn, maar verwijzingen naar de oorsprongtabel.

Wie heeft hiervoor een vlotte werkwijze?

Marc

Commentaar van: Jan Karel Pieterse (7-9-2009 08:53:41) deeplink naar dit commentaar

Hallo Marc,

Je zou met behulp van geneste functies kunnen werken, waarbij je de functie VERSCHUIVING combineert met de functie VERGELIJKEN. De vergelijken functie gebruik je dan om de juiste rij en kolom op te zoeken in je brongegevens.

Maar ook een draaitabel zou eventueel geschikt kunnen zijn, dit hangt af van de indeling van je gegevens.

Commentaar van: Wim Lucassen (22-6-2010 08:07:04) deeplink naar dit commentaar

Is het mogelijk om in een tabel de totaalrij te tonen en er daarna berekeningen aan toe te voegen, maar niet aan al de cellen. Bijvoorbeeld vanaf kolom "K" t/m kolom "AA" terwijl de tabel begint in de kolom "A".
Alvast hartelijk dank
Groet Wim Lucassen

Commentaar van: Jan Karel Pieterse (24-6-2010 02:30:08) deeplink naar dit commentaar

Hallo Wim,

Ik denk dat je dan de tabel moet uitbreiden met die kolommen, de totaalrij moet namelijk per definitie deel uitmaken van de tabel.

Commentaar van: Piet Meulendijks (1-7-2010 03:38:48) deeplink naar dit commentaar

dag heer Pieterse,

ik heb een regel code die 2007 wel werkt en in 2003 niet:

ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear

hebt u een suggestie om dit in 2003 te laten werken (EN in 2007)

bij voorbaat dank,
Piet Meulendijks, Son

Commentaar van: Jan Karel Pieterse (1-7-2010 04:31:31) deeplink naar dit commentaar

Hallo Piet,

Deze optie is nieuw in 2007 en kan dus in 2003 niet worden gebruikt. Bovendien kent 2007 meer sorteervelden dan 2003. OM een sorteer functie in een macro te krijgen die zowel in 2003 als in 2007 werkt, kan je het beste de functie opnemen met een macro in 2003 en dan die code aanpassen.

Commentaar van: Lucas Cammel (16-8-2011 05:05:03) deeplink naar dit commentaar

Ik krijg navolgende foutmelding maar niet kwijt: De ActiveSheet regel boven [Range("A5").Select] wordt geel aangegeven????

Sub Naar_Postbussen_Anna_Paulowna()
'
' Naar_Postbussen_Anna_Paulowna Macro
'

'
    Sheets("Overzicht Klanten").Select
    ActiveSheet.ListObjects("Tabel3").Range.AutoFilter Field:=5, Criteria1:= _
        Array("Anna Paulowna", "Breezand", "'t Zand"), Operator:=xlFilterValues
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("Postbussen Anna Paulowna").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveSheet.ListObjects("Tabel3106").Range.AutoFilter Field:=16, Criteria1 _
        :="<>"
    Range("A5").Select
End Sub

Commentaar van: Jan Karel Pieterse (22-8-2011 02:11:20) deeplink naar dit commentaar

Hallo Lucas,

Ik vermoed dat het komt omdat het listobject genaamd "Tabel3106" niet bestaat?

Commentaar van: J. Melsen (29-9-2011 08:15:28) deeplink naar dit commentaar

Het lukt me niet met een opgenomen macro, datums te filteren in Excel 2007.
Met de hand filteren gaat het perfect. Ga ik dan de macro opnemen dan lukt het niet. Het ligt niet aan de opmaak van de datums want die is overal het zelfde ingesteld en trouwens met de hand lukt het toch ook.
Zet ik de muiswijzer op de filterpijl(ALS ik de macro heb afgespeeld )dan geeft de tekst onder de pijl een andere opmaak dan werkelijk is.

Wat doe ik verkeerd.

Commentaar van: Jan Karel Pieterse (30-9-2011 02:20:42) deeplink naar dit commentaar

Hallo J. Melsen,

Dit werkt bij mijn Excel 2010:

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveSheet.Range("$A$1:$A$549").AutoFilter Field:=1, Criteria1:= _
"=02-01-2012", Operator:=xlAnd
End Sub

Commentaar van: Marc Vandeweyer (2-1-2012 13:06:38) deeplink naar dit commentaar

Ik krijg maar altijd een (fout 1004) als ik deze macro uitvoer als mijn blad beveiligd is maar als ik de beveiliging uitschakel werkt het perfect!

    With ActiveSheet.ListObjects("Tabel3")
        .Range.AutoFilter Field:=2, Criteria1:="<>"
    End With

Commentaar van: Jan Karel Pieterse (2-1-2012 22:13:01) deeplink naar dit commentaar

hallo marc,

Zet in je macro code die de bladbeveiliging weghaalt:

ActiveSheet.UnProtect Password:="wachtwoord"
'rest van code
ActiveSheet.Protect Password:="wachtwoord"

Commentaar van: Liesbet (14-6-2012 15:49:18) deeplink naar dit commentaar

Geachte,

Ik had graag een tabel gesorteerd in Excel.

Nu is het zo dat ik in iedere rij een nummer heb, doch sommige nummers worden gevolgd door A en B.

Dus

30000
30001
30002A
30002B
30003
enz.

Als ik deze rijen door elkaar haal en daarna op de gewone manier sorteer, dus met Data Sort, komen de nummers met A en B steevast onderaan te staan.

De cijfers hebben steeds dezelfde lengte, dus 5 karakters, ev. gevolgd met A of B.

Heeft iemand een idee hoe ik dit kan oplossen, zonder gebruik te maken van verborgen kolommen?

Dank bij voorbaat.

Groeten,

Liesbet

Commentaar van: Jan Karel Pieterse (14-6-2012 16:00:25) deeplink naar dit commentaar

Hallo Liesbet,

Ik denk dat de codes zonder letter erachter gezien worden als getal en daardoor anders sorteren dan de rest. Probeer eens de getallen (of een paar ervan) om te zetten naar tekst door er een apostrofje voor te zetten. Als dat werkt, dan weet je de oorzaak.

Commentaar van: Arjo (11-11-2013 11:51:31) deeplink naar dit commentaar

wederom dank voor uitleg over excel. Dit is al de zoveelste keer dat ik een artikel van deze site gebruik!

ik heb 1 vraag die ik niet kon vinden:
kan ik middels vba de brongegevens verwijzing van een draaitabel aanpassen?

Ik heb met vba mijn brondata al omgezet naar een tabel.
nu wil ik voor alle draaitabellen in het bestand door vba te gebruiken, de brongegevens aanpassen.

Iedere draaitabel heeft nu als brongegevens het bereik: "'schaduwblad'!A:AA"

maar dat moet worden "tabel1" (ook op werkblad "schaduwblad"

kan daar nog de uitleg van toegevoegd worden aan dit artikel? dus: hoe kan ik middels vba de bronverwijzing van mijn draaitabellen aanpassen?

Commentaar van: Jan Karel Pieterse (11-11-2013 12:16:26) deeplink naar dit commentaar

Ho Arjo,

Leuk te horen dat je hier veel info vind die je gebruiken kan!

Ik zou zeggen, neem eens een macro op terwijl je er eentje aanpast...

Commentaar van: Arjo (11-11-2013 13:01:21) deeplink naar dit commentaar

Hallo,

de suggestie heb ik geprobeerd, maar ik zoek nu nog naar de mogelijkheid om ipv de cache voor 1 pivottable, de cache van alle pivots in een bestand aan te passen met dit script

Commentaar van: Jan Karel Pieterse (11-11-2013 13:13:30) deeplink naar dit commentaar

Hallo Arjo,

Code zoals dit:

Sub Macro1()
    Dim oPc As PivotCache
    For Each oPc In ActiveWorkbook.PivotCaches
        oPc.SourceData = "Tabel1"
    Next
End Sub

Commentaar van: Arjo (11-11-2013 13:32:16) deeplink naar dit commentaar

Dank voor de hulp!

ik had ook al zoiets gevonden inderdaad.
Als ik de code die je geplaatst hebt hierboven uitvoer krijg ik deze fout:
fout 1004:
door de toepassing of door object gedefinieerde fout

als ik dan de foutopsporing start wordt de regel met opc.sourcedata geel gemarkeerd.

Commentaar van: arjo (11-11-2013 14:21:11) deeplink naar dit commentaar

nog een aanvulling: ik heb het script uitgeprobeerd op een willekeurig opgezet bestandje.

Daar werkt het feilloos. Maar bij mijn bedoelde bestand krijg ik een foutmelding. Nu heb ik ontdekt dat als ik in dit willekeurig opgezet bestandje 1 slicer aan meerdere draaitabellen koppel, dat dan het script ook dezelfde fout geeft. het probleem zit daarbij dus denk ik in de gekoppelde slicers. Omdat ik het uiteindelijke script wil gebruiken voor meerdere (veel) templates die allemaal exact hetzelfde zijn qua opbouw, is handmatig slicers ontkoppelen en daarna weer koppelen geen optie. Kan ik dit ook middels vba doen?

iets met

disconnect slicers

change sourcedata

reconnect slicers

?

(waarbij vba onthoudt welke slicer aan welke draaitabel verbonden moet zijn)?

Commentaar van: Jan Karel Pieterse (11-11-2013 15:05:59) deeplink naar dit commentaar

Hallo Arjo,

Dat ligt ook helemaal niet voor de hand :-)

Sub ReDirectPivots()
    Dim oPt As PivotTable
    Dim oSh As Worksheet
    For Each oSh In ActiveWorkbook.Worksheets
        For Each oPt In oSh.PivotTables
            oPt.DataBodyRange.Parent.PivotTableWizard SourceType:=xlDatabase, SourceData:="Table_Northwind2"
        Next
    Next
End Sub

Commentaar van: Arjo (11-11-2013 17:03:47) deeplink naar dit commentaar

WOW!

echt mega bedankt!
(ik zou graag snappen wat er gebeurt... het meeste snap ik... alleen de regel met "oPt.DataBodyRange.Parent ... "
snap ik nog niet...)

Commentaar van: Arjo (12-11-2013 11:06:33) deeplink naar dit commentaar

Ik ontdek net dat niet van alle draaitabellen de datasource wordt aangepast. Daardoor werken de slicers ook niet meer op alle draaitabellen.

ik heb op alle tabbladen 4 draaitabellen staan. Van 1 werkblad zie ik wel alle draaitabellen staan om te linken aan een specifieke slicer maar van de meeste werkbladen staan er 2 of 3 draaitabellen die je kunt linken en ontbreken er dus 1 of 2 waarvan dus blijkt dat daar de data source niet is aangepast door het script.

Commentaar van: Jan Karel Pieterse (12-11-2013 11:07:01) deeplink naar dit commentaar

Hallo Arjo,

Opt.DataBopyRange.Parent is het werkblad (parent) waarop het bereik (DataBodyRange) zich bevind waar de draaitabel (oPt) zich bevind.

Commentaar van: Jan Karel Pieterse (12-11-2013 11:11:03) deeplink naar dit commentaar

Hoi Arjo,

Vreemd, krijg je geen foutmeldingen?

Commentaar van: Patrick Brom (11-3-2015 19:51:55) deeplink naar dit commentaar

Ik ben op zoek naar een oplossing voor de volgende uitdaging, ik heb een Excel bestand (v2010), waar een tabel in staat die gegevens weergeeft via een data connectie uit een Acces database.

Nadat de tabel is geupdated, wil ik de dataconnectie verbreken en verwijderen middels een VBA script, maar tot op heden heb ik niets kunnen vinden om deze uitdaging op te lossen zoals ik wil. Wanneer ik denk de oplossing te hebben, en ik test de sheet, dan wordt alsnog de verbinding herstelt.

Aangezien de gegevens naar een Excel table worden gehaald, zou ik dus de connectie met de tabel moeten verbreken, maar ook de data connectie tussen Excel en de Access database, maar dit is een uitdaging die ik tot dusver niet heb kunnen oplossen

Wellicht heeft u een idee?

Commentaar van: Jan Karel Pieterse (12-3-2015 06:19:09) deeplink naar dit commentaar

Hoi Patrick,

Het is anders heel eenvoudig :-)

Worksheets("HetBlad").ListObjects("DeTabel").QueryTable.Delete

Commentaar van: Patrick Brom (12-3-2015 06:23:58) deeplink naar dit commentaar

Jeetje,
Is dat alles..........:)
Ik ga hem later vandaag testen.
In ieder geval bedankt!

Commentaar van: Martin Bakker (31-3-2015 15:33:03) deeplink naar dit commentaar

Hallo, Ik weet niet of het mogelijk is om via VBA tijdens een ListObject.querytable refresh een 6-tal veldnamen te hernoemen naar bepaalde celwaarden elders in de sheet. Ik heb nu bv in een access query de veldnaam "maand0" staan. Tijdens een refresh moet deze de actuele maandnaam krijgen.

Ik dacht in eerste instantie te werken zoals in excel 2003:
Veldnamen worden niet getoond, enkel de data. In een regel erboven zet ik dan handmatig de veldnamen. In excel 2010 kan je wel de header row uitzetten en zelf veldnamen bedenken maar dan werkt het autofilter niet meer.

Ik hoop dat jullie een oplossing hiervoor weten.

Commentaar van: Jan Karel Pieterse (31-3-2015 15:44:38) deeplink naar dit commentaar

Hallo Martin,

Ik zou via VBA de betreffende kolom NA de refresh hernoemen. Met een formule gaat niet omdat tabelkopregels geen formule mogen bevatten.

Commentaar van: T.H. de Swart (30-5-2015 21:05:21) deeplink naar dit commentaar

Ik wil middels een BOX een cel in een ander bestand selecteren en kopiëren naar het bestand waarin ik deze cel informatie zal gebruiken.
Hoe moet dit in VBA code?

gr. Teus de Swart

Commentaar van: Jan Karel Pieterse (1-6-2015 08:43:08) deeplink naar dit commentaar

Hallo Teus,

Ik begrijp niet helemaal wat je bedoelt, maar kijk eens naar Application.FileDialog in VBA help.

Commentaar van: Ad (19-1-2016 13:53:25) deeplink naar dit commentaar

Hoe kan ik een aantal kolommen selecteren op basis van de koptekst in deze kolommen, de kolom koppen bevatten allemaal een nummer.

Vervolgens wil ik de geselecteerde kolommen kopieeren naar een nieuw werkblad.

Ik krijg diversen excel CSV bestanden met iedere keer andere kolom benamingen en in andere volgorde het enige wat overeenkomt is dat bijvoorbeeld kolom "54" dezelfde soort informatie bevat.

Commentaar van: Jan Karel Pieterse (19-1-2016 14:52:55) deeplink naar dit commentaar

Hoi Ad,

Kan je een voorbeeldregel uit je tekstbestand plaatsen? Liefst twee verschillende?

Commentaar van: Martin Wolf (27-4-2016 11:18:05) deeplink naar dit commentaar

Ik zoek een VBA oplossing voor het volgende:
Op Blad1 heb ik een tabel1 staan en op Blad2 Tabel2. Nu wil ik een bepaalde regel in Tabel1 selecteren en onderaan toevoegen aan Tabel2. De Regel moet daarna ook verwijderd worden uit Tabel1.
Is daar een oplossing voor?

Commentaar van: Jan Karel Pieterse (29-4-2016 11:55:04) deeplink naar dit commentaar

Hallo Martin,

Dat gaat als volgt:

    With Worksheets("Blad2").ListObjects("Tabel2").ListRows.Add
        .Range.Value = Intersect(Selection.EntireRow, Worksheets("Blad1").ListObjects("Tabel1").DataBodyRange).Value
    End With
    Intersect(Selection.EntireRow, Worksheets("Blad1").ListObjects("Tabel1").DataBodyRange).Delete

Commentaar van: Martin (29-4-2016 20:33:58) deeplink naar dit commentaar

Dank Jan Karel,
Het werkt perfect!

Commentaar van: Willem Flutsch (7-10-2017 13:19:43) deeplink naar dit commentaar

Excel 2016.
Ik wil graag van een gegeven maand de dagen, verticaal, automatisch in een cel krijgen, kan dit?
Zo ja, zou iemand me daar op weg willen helpen?

Commentaar van: Jan Karel Pieterse (9-10-2017 09:47:18) deeplink naar dit commentaar

Hoi Willem,

Hiervoor kan je de datum functie gebruiken.
Setl dat het jaartal in cel A1 staat, de maand (als getal) in cel A2, dan kan je in cel B1 de volgende formule plaatsen:
=DATUM(A1;A2;RIJ())

Commentaar van: Hoogeboom (4-12-2017 18:18:31) deeplink naar dit commentaar

Ik wil graag een tabel kolom sorteren op waarden en unieke waarden, bijvoorbeeld: eerst een kolom met cd namen alfabetisch sorteren en vervolgens op unieke waarde. Dit natuurlijk in Vba

Groetjes

Karel

Commentaar van: Jan Karel Pieterse (5-12-2017 15:58:13) deeplink naar dit commentaar

Hoi Karel,

Ik begrijp niet precies wat je wilt, lukt dat niet middels gewoon sorteren?

Commentaar van: Hoogeboom (5-12-2017 18:44:09) deeplink naar dit commentaar

Hallo Jan Karel,

Sorry, mijn fout, ik wil filteren.

Ik dacht dat er voor een tabel in Excel andere opdrachten beschikbaar zijn. Wat ik eigenlijk wil bereiken:
Ik heb een muziek cd tabel in Excel met o.a. de kolommen Artiest, cdnaam en titel, ik wil drie listboxen vullen met unieke waarden uit de genoemde kolommen en vervolgens filteren op een waarde uit een van de listboxen op een ander weekblad.

Groetjes,

Karel

Commentaar van: Jan Karel Pieterse (6-12-2017 14:14:04) deeplink naar dit commentaar

Hoi Karel,

Heb je gekeken naar Slicers?

Commentaar van: Hoogeboom (6-12-2017 16:25:07) deeplink naar dit commentaar

Ik zal er naar kijken,

Bedankt,

Karel

Commentaar van: Ad van Lieshout (24-10-2018 13:26:25) deeplink naar dit commentaar

Ik heb onderstaande VBA code gevonden op het internet en deze werkt perfect in een normale sheet van excel 2007.
Ik wil deze code ook gaan gebruiken in mijn adressenbestand in excel 2007. Ik heb het adressenbestand opgemaakt met een tabel maar nu werkt het maar gedeeltelijk. Kleine letters worden wel hoofdletters maar als ik b.v. intik b.v. 3406ad dan worden de letters ad AD alleen de spatie plaats hij niet. Moet er met tabellen iets aan de VBA code toegevoegd worden? Graag even uw advies c.q. een oplossing.

Alvast bedankt voor uw medewerking.

Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
'de macro start enkel voor kolom I
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
If Len(Target) = 6 Then
If Target Like "####[A-Z][A-Z]" Then _
Target = Left(Target, 4) & " " & UCase(Right(Target, 2))
End If
End Sub

Commentaar van: Jan Karel Pieterse (24-10-2018 13:45:50) deeplink naar dit commentaar

Hoi Ad,

Werkt dit beter?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
    If Len(Target) = 6 Then
        If Target Like "####[a-zA-Z][a-zA-Z]" Then _
         Target = Left(Target, 4) & " " & UCase(Right(Target, 2))
    End If
End Sub

De term Option Compare Text kan je weglaten

Commentaar van: Ad van Lieshout (24-10-2018 14:02:57) deeplink naar dit commentaar

Hallo Jan Karel,

Ik heb het veranderd zoals jij hebt aangegeven incl. het weghalen van de term Option Compare Text maar er veranderd niets, kleine letters zet hij om in hoofdletters alleen zet hij de spatie niet.
Als ik buiten de tabel de Range opgeef dan doet hij het perfect, dus ik denk dat het iets te maken heeft met de opgemaakte tabel.

Maar alvast bedankt voor je snelle reactie.

Commentaar van: Jan Karel Pieterse (24-10-2018 14:22:52) deeplink naar dit commentaar

Hoi Ad,

Bij mij werkte het prima. Kan je je bestand even mailen?

Commentaar van: Gerwin (20-12-2019 15:41:00) deeplink naar dit commentaar

Formule Index gebruiken, alleen vul je ipv rij kolom in en bij kolom idem dito.

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.