Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Bestand crasht Excel!!

Red mijn werkmap!
De beste tool voor Excel bestanden met problemen.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Excel Tabellen (VBA)
This page in English

Werken Met tabellen in Excel 2013, 2010 en 2007 (VBA)

Dit artikel is ook op Microsoft Office Online gepubliceerd:

Working with Excel tables in Visual Basic for Applications (VBA)

Inleiding

In Werken Met tabellen in Excel 2013, 2010 en 2007 is beschreven hoe u de tabel optie van deze Excel versies 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, begint met dezelfde code als in Excel 2003:

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

Maar het nieuws is direct hierboven al zichtbaar: 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()
    'Werkt niet in 2003
    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

Dit stukje code werkt exact hetzelfde in Excel 2003, dus hier is niets nieuws onder de zon (tenminste, behalve dan dat in de gebruikersinterface die ListObjects in Excel 2013, 2010 en 2007 dus "Tabellen" worden genoemd).

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. Het commentaar in de code geeft aan welke delen in Excel 2003 niet werken.

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
        'Werkt niet in 2003
        .ListColumns(1).DataBodyRange.Select
        'Selecteer rij 4(kopregel telt niet mee)
        .ListRows(4).Range.Select
    End With
   
    'Werkt niet in 2003
    '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 2013, 2010 en 2007 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.

QueryTabellen

Een andere verandering is de Querytables collectie. In oudere versies van Excel maakt deze collectie deel uit van de objecten onder het "Worksheet" object. Dit is niet langer het geval. Onderstaand code zal dan ook niet werken in Excel 2007, 2010 of 2013:

Sub QueryTableDemo()
     Dim oQT As QueryTable
     For Each oQT In ActiveSheet.QueryTables
         MsgBox oQT.Connection
     Next
End Sub

De manier om dit in Excel 2007-2013 te doen is:

Sub QueryTableDemo()
     Dim oLo As ListObject
     For Each oLo In ActiveSheet.ListObjects
         If oLo.SourceType = xlSrcQuery Then
             MsgBox oLo.QueryTable.Connection
         End If
     Next
End Sub

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)
'Werkt niet in 2003
'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

Dit is iets dat in Excel 2003 nog niet mogelijk was. Het is direct gekoppeld aan het gegeven dat een tabel gekoppeld is aan een bereiknaam. Het is niet direct duidelijk waar in de gebruikersinterface u dit commentaar 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, zie figuur 17AutoAanvullen toont commentaar
Figuur 17: commentaar wordt getoond bij formule auto-aanvullen.

In VBA is de syntax:

Sub CommentaarBijTabel()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    'Werkt niet in 2003
    '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 en kent dezelfde syntax als in Excel 2003:

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 Excel 2013, 2010 en 2007 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. De onderstaande code werkt uiteraard alleen in Excel 2007 en hoger. De lijst optie in Excel 2003 kent slechts de standaard autofilter en sorteer mogelijkheden zoals die in essentie al in Excel 5 aanwezig waren.

Sub SorterenEnFilteren()
'Werkt niet in 2003
    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
    'Alleen de oude autofilter dingen werken in Excel 2003
    '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:

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:


Comment by: Marc Bittremieux (9/5/2009 1:05:46 AM)

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

 


Comment by: Jan Karel Pieterse (9/7/2009 8:53:41 AM)

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.

 


Comment by: Wim Lucassen (6/22/2010 8:07:04 AM)

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

 


Comment by: Jan Karel Pieterse (6/24/2010 2:30:08 AM)

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.

 


Comment by: Piet Meulendijks (7/1/2010 3:38:48 AM)

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

 


Comment by: Jan Karel Pieterse (7/1/2010 4:31:31 AM)

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.

 


Comment by: Lucas Cammel (8/16/2011 5:05:03 AM)

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

 


Comment by: Jan Karel Pieterse (8/22/2011 2:11:20 AM)

Hallo Lucas,

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

 


Comment by: J. Melsen (9/29/2011 8:15:28 AM)

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.

 


Comment by: Jan Karel Pieterse (9/30/2011 2:20:42 AM)

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

 


Comment by: Marc Vandeweyer (1/2/2012 1:06:38 PM)

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

 


Comment by: Jan Karel Pieterse (1/2/2012 10:13:01 PM)

hallo marc,

Zet in je macro code die de bladbeveiliging weghaalt:

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

 


Comment by: Liesbet (6/14/2012 3:49:18 PM)

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


 


Comment by: Jan Karel Pieterse (6/14/2012 4:00:25 PM)

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.

 


Comment by: Arjo (11/11/2013 11:51:31 AM)

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?



 


Comment by: Jan Karel Pieterse (11/11/2013 12:16:26 PM)

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...

 


Comment by: Arjo (11/11/2013 1:01:21 PM)

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

 


Comment by: Jan Karel Pieterse (11/11/2013 1:13:30 PM)

Hallo Arjo,

Code zoals dit:

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

 


Comment by: Arjo (11/11/2013 1:32:16 PM)

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.

 


Comment by: arjo (11/11/2013 2:21:11 PM)

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)?

 


Comment by: Jan Karel Pieterse (11/11/2013 3:05:59 PM)

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

 


Comment by: Arjo (11/11/2013 5:03:47 PM)

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...)

 


Comment by: Arjo (11/12/2013 11:06:33 AM)

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.



 


Comment by: Jan Karel Pieterse (11/12/2013 11:07:01 AM)

Hallo Arjo,

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

 


Comment by: Jan Karel Pieterse (11/12/2013 11:11:03 AM)

Hoi Arjo,

Vreemd, krijg je geen foutmeldingen?

 


Comment by: Patrick Brom (3/11/2015 7:51:55 PM)

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?

 


Comment by: Jan Karel Pieterse (3/12/2015 6:19:09 AM)

Hoi Patrick,

Het is anders heel eenvoudig :-)

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

 


Comment by: Patrick Brom (3/12/2015 6:23:58 AM)

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

 


Comment by: Martin Bakker (3/31/2015 3:33:03 PM)

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.

 


Comment by: Jan Karel Pieterse (3/31/2015 3:44:38 PM)

Hallo Martin,

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

 


Comment by: T.H. de Swart (5/30/2015 9:05:21 PM)

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

 


Comment by: Jan Karel Pieterse (6/1/2015 8:43:08 AM)

Hallo Teus,

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

 


Comment by: Ad (1/19/2016 1:53:25 PM)

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.

 


Comment by: Jan Karel Pieterse (1/19/2016 2:52:55 PM)

Hoi Ad,

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

 


Comment by: Martin Wolf (4/27/2016 11:18:05 AM)

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?

 


Comment by: Jan Karel Pieterse (4/29/2016 11:55:04 AM)

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

 


Comment by: Martin (4/29/2016 8:33:58 PM)

Dank Jan Karel,
Het werkt perfect!

 


Comment by: Willem Flutsch (10/7/2017 1:19:43 PM)

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?

 


Comment by: Jan Karel Pieterse (10/9/2017 9:47:18 AM)

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())

 


Comment by: Hoogeboom (12/4/2017 6:18:31 PM)

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

 


Comment by: Jan Karel Pieterse (12/5/2017 3:58:13 PM)

Hoi Karel,

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

 


Comment by: Hoogeboom (12/5/2017 6:44:09 PM)

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

 


Comment by: Jan Karel Pieterse (12/6/2017 2:14:04 PM)

Hoi Karel,

Heb je gekeken naar Slicers?

 


Comment by: Hoogeboom (12/6/2017 4:25:07 PM)

Ik zal er naar kijken,

Bedankt,

Karel

 


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: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].