donderdag 21 april 2016

Excel: draaitabel baseren op een benoemde "tabel"

Op het gevaar af een open deur in te trappen toch maar even over de draaitabel op basis van een benoemde "tabel". Elke keer als ik mensen vertel dat dat in Excel toch echt de beste manier is, stuit ik in de meeste gevallen op: "nooit eerder gehoord".

Hoe werkt het? Stel we hebben een lijst in Excel. Ik heb hier even de gegevens van de Orderinformatie uit de database Noordenwind gebruikt.


Als we hier op de gewone manier een draaitabel van maken via Invoegen => Draaitabel, zien we:


We zien dat het bereik er letterlijk in staat: Blad1!$A$1:$E$2145. Als we de draaitabel gemaakt hebben en het bereik wordt groter, dan hebben we een probleempje. Ik zie veel mensen dit oplossen door maar te kiezen voor bijvoorbeeld: Blad1!$A:$E. Hoppakee, we pakken de hele kolommen maar.

Dit werkt in zekere zin voor nieuwe rijen, maar als er een kolom bij komt, moeten we toch het bereik gaan uitbreiden.

Hoe werkt het nu als we er eerst een "tabel" van maken? Dit kan trouwens op twee manieren. Allereerst kunnen we op de tab Start kiezen voor Opmaken als tabel. De tweede mogelijkheid is via de tab Invoegen kiezen voor de optie Tabel. In beide gevallen verschijnt (bij de eerste optie in tweede instantie; bij de twee optie direct):


Als we op OK klikken, heeft Excel een benoemde tabel gemaakt. Als we dit voor het eerst doen in een bestand, heeft deze tabel de naam Tabel1 gekregen. We kunnen dit zien door in de tab Start te kiezen voor Formules => Namen beheren.


We kunnen hier ook de naam veranderen in bijvoorbeeld Orderinformatie. Dat houdt de boel overzichtelijk als we met meer tabellen gaan werken. Het bereik kunnen we niet aanpassen. Dit doet Excel voor een tabel al automatisch!

Als we nu weer een draaitabel gaan maken, zien we:


In plaats van Blad1!$A$1:$E$2145 zien we nu de naam Orderinformatie staan. Gaan we door, dan krijgen we een reguliere draaitabel met alle beschikbare rijen en kolommen.

Terug naar onze tabel. Als we daar een kolom Omzet toevoegen met als formule Prijs per eenheid * Hoeveelheid, zien we dat de tabel zich heel anders gedraagt dan regulier Excel. De formule ziet er namelijk als volgt uit:

=[@[Prijs per eenheid]]*[@Hoeveelheid]

Als we op ENTER drukken, wordt de formule ook gelijk tot het einde van de tabel doorgetrokken. Hier hoeven we niet voor te doen.

Terug naar het blad met de draaitabel. Hier zien we onze nieuwe kolom nog niet. Als we met de cursor in de draaitabel gaan staan en we kiezen op de tab Analyseren voor Alles vernieuwen (eventueel klikken op het floepje onder de knop Vernieuwen), dan zal onze nieuwe kolom ook zichtbaar worden! Hetzelfde verhaal geldt voor nieuwe rijen.

De draaitabel zelf heeft trouwens ook een optie Gegevens vernieuwen bij het openen van het bestand. Staat deze optie aan, dan worden eventuele nieuwen kolommen en rijen automatisch aan de draaitabel toegevoegd.

Het werken met Excel tabellen heeft vanaf versie 2013 nog andere voordelen:

  • berekenen van unieke aantallen.
  • maken van draaitabellen over meerdere tabellen.
Beide onderwerpen heb ik besproken in eerdere blogs:
Klik hier en hier.

Voor verder Excel tips klik hier.

dinsdag 19 april 2016

Access: VBA script voor overzicht van tabellen en kolommen van een database naar Excel bestand

Op de SQL Server kun je met een vrij simpele SQL query een overzicht maken van tabellen, kolommen en informatie als datatype etc.

SELECT T.table_name,column_name, data_type, T.TABLE_TYPE, character_maximum_length, ordinal_position, is_nullable
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
WHERE T.TABLE_TYPE='BASE TABLE'  OR T.TABLE_TYPE='VIEW'
ORDER BY T.table_name,ordinal_position,column_name

De uitkomst kun je dan bijvoorbeeld kopi√ęren naar een Excel bestand waar je dan simpel selecties kunt doen.

In Access bestaat zoiets niet. Wil je daar een overzicht hebben van tabellen en kolommen dan kun je een rapport uitdraaien. Dat levert evenwel geen mooie lijst op. Dan komt VBA in beeld. Na enig speurwerk op internet en de nodige aanpassingen en toevoegingen door mij kom ik tot het volgende script:

Sub ListTablesADOX()
    Dim Conn As New ADODB.Connection
    Dim strConn  As String
    
    'Open connection you want To get database objects
    strConn = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\data\accessontwikkeling\noordenwindb.mdb"

    'Conn.Open strConn 'access
  
    Dim objApp As Object
    Dim exlExcel As Object
    Dim lngRij As Long
    Set objApp = CreateObject("Excel.Application")
    Set exlExcel = objApp.workbooks.Add
  
    With exlExcel.sheets(1)
        .Range("A1").Offset(0, 0) = "Tabelnaam"
        .Range("A1").Offset(0, 1) = "Tabeltype"
        .Range("A1").Offset(0, 2) = "Veldnaam"
        .Range("A1").Offset(0, 3) = "Typeomschrijving"
        .Range("A1").Offset(0, 4) = "Typenummer"
        .Range("A1").Offset(0, 5) = "Attribuutomschrijving"
        .Range("A1").Offset(0, 6) = "Attribuutnummer"
        .Range("A1").Offset(0, 7) = "Grootte"
        '.Range("A1").Offset(0, 6) = "Beschrijving"
        '.Range("A1").Offset(0, 7) = "Aantal records"
        '.Range("A1").Offset(0, 8) = "Primary key"
    End With
  
    'Create catalog object
    Dim Catalog As New ADOX.Catalog
    Set Catalog.ActiveConnection = Conn
    
    'List tables And columns
    Dim Table As ADOX.Table, Column As ADOX.Column
    For Each Table In Catalog.Tables
        If Table.Type <> "SYSTEM TABLE" Then 'Access
            With exlExcel.sheets(1)
                For Each Column In Table.Columns
                    lngRij = lngRij + 1
                    .Range("A1").Offset(lngRij, 0) = Table.Name
                    .Range("A1").Offset(lngRij, 1) = Table.Type
                    .Range("A1").Offset(lngRij, 2) = Column.Name
                    .Range("A1").Offset(lngRij, 3) = VeldtypeOmschrX(Column.Type) 'access
                    .Range("A1").Offset(lngRij, 4) = Column.Type
                    .Range("A1").Offset(lngRij, 5) = VeldBeschrijvingX(Column.Attributes)
                    .Range("A1").Offset(lngRij, 6) = Column.Attributes
                    .Range("A1").Offset(lngRij, 7) = Column.DefinedSize
                Next
            End With
        End If
    Next

    objApp.Visible = True
    Set objApp = Nothing
    Set exlExcel = Nothing

    Conn.Close
    Set Conn = Nothing

End Sub

In het script verwijs ik nog naar twee zelfgemaakte functies:

Function VeldtypeOmschrX(veldtype As Long) As String
    Select Case veldtype
        Case 2
            VeldtypeOmschrX = "numeriek, geheel getal"
        Case 3
            VeldtypeOmschrX = "numeriek, lange integer"
        Case 4
            VeldtypeOmschrX = "numeriek, enkele precisie"
        Case 5
            VeldtypeOmschrX = "numeriek, dubbele precisie"
        Case 11
            VeldtypeOmschrX = "ja/nee"
        Case 16
            VeldtypeOmschrX = "numeriek byte"
        Case 72
            VeldtypeOmschrX = "numeriek, replicaId"
        Case 131
            VeldtypeOmschrX = "numeriek, decimaal"
        Case 135
            VeldtypeOmschrX = "datum/tijd"
        Case 202
            VeldtypeOmschrX = "korte tekst"
        Case 203
            VeldtypeOmschrX = "lange tekst"
        Case 205
            VeldtypeOmschrX = "OLE object"
    End Select

End Function

en nummer 2:

Function VeldBeschrijvingX(lngAttribuut As Long) As String
    Select Case lngAttribuut
        Case 0
            VeldBeschrijvingX = "tekst, not null"
        Case 1
            VeldBeschrijvingX = "numeriek of tekst(1), not null"
        Case 2
            VeldBeschrijvingX = "tekst, null"
        Case 3
            VeldBeschrijvingX = "numeriek of tekst(1),null"
    End Select

End Function

Het script levert in Excel voor de database Noordenwind de volgende tabel op:


vrijdag 8 april 2016

Friesland: bijzondere grafzerk uit Harlingen

In de stad Harlingen is onlangs een bijzondere grafzerk tevoorschijn gekomen. De zerk bevond zich in de tuin van Zeepziederstraat 6. Deze steen zal oorspronkelijk wel in de Harlinger Westerkerk hebben gelegen. Deze kerk is in 1898 gesloopt.

Helemaal duidelijk is het nog niet. Archivaris Jeanine Otten schrijft:

In 1834 noteerde Freerk Dirks Fontein alle grafschriften in de Westerkerk, maar het grafschrift van Hamen Synes Nauta zat daar wonderlijk genoeg niet bij. Ik heb in de begraafboeken van Harlingen ook geen vermelding gevonden. Dat is wel een raadsel.

Het is een bijzonder zware grafzerk met een dikte van 23 cm. Verder is de zerk breed 1,42 meter. en hoog 2,03 meter. Gelet op de niet leesbare vage letters in het middenveld is het een hergebruikte zerk. De Vereniging Oud Harlingen zoekt nu naar een plek in de stad om de zerk daar in het zicht te herplaatsen.

Melle Koopmans en Meindert Tangerman van de Grêfskriftekommisje hebben de tekst grotendeels weten te ontcijferen:

Ano 1677 den 27 october is gestorven ... Harmen Sinnes Nauta ... ter admiraliteit ...n... des...  oud 69 iaer en hier begraven

Anno 1690 den 11 juny is gestorven de eerbare huisvrou [Hesseltie Doeckes] ... Harmen Sinnes ... der admiraliteit en burgemr deser stede oud 70 1/4 (?) iaer en is hier begraven

Anno 1682 den 29 januari is in den heere [gerust]  de eerbare Baukien Harmens Nauta huisfrou van de heer Gayis van Boelens dr. en secretaris van Won[seradeel oud 25] iaer en leit hier met haer soontie begraven


Foto: Melle Koopmans

Wie waren genoemde personen? Harmen Sienes Nauta werd op 13 maart 1608 gedoopt als zoon van de Dokkumer Siene Harmens en Iebeltje Romkes. Later wordt hij genoemd als vroedsman en burgemeester van Harlingen. Ook is hij equipagemeester ter admiraliteit. 

Hij trouwt op 15 maart 1635 met de dan vijftienjarige Hesseltje Doekes, vermoedelijk een dochter van Doeke Gerrits en Baukje Andries uit Leeuwarden.

Hun dochter Baukje Harmens Nauta wordt gedoopt op 11 november 1656 te Harlingen. Zij trouwt op 15 februari 1681 met Gaius van Boelens. Zij is waarschijnlijk in het kraambed overleden. Van het genoemde zoontje vinden we ook geen naam en doopdatum.

Ter ere van hun huwelijk is er een bundel Friese en Nederlandse gedichtenbundel van 13 pagina's uitgebracht, wat wel zeer bijzonder genoemd mag worden.



Opmerkelijk genoeg staan er in deze bundel ook twee gedichten die in het zogenaamde Midfries geschreven zijn.


Gaius van Boelens wordt als Gaatse gedoopt op 13 maart 1661 te Leeuwarden, als zoon van Koert van Boelens en Agatha van Geldorp.

In 1676 wordt hij ingeschreven als student te Franeker. Hij promoveert daar in 1679 op achttienjarige leeftijd. Vervolgens wordt hij gemeentesecretaris van Wonseradeel. Na het overlijden van zijn eerste vrouw hertrouwt hij op 24 juni 1683 met Adriana Stansius. Net als zijn eerste vrouw overlijdt ook hij jong. Hij sterft op zevenentwintigjarige leeftijd. Op 21 januari 1689 wordt hij begraven in de Westerkerk te Leeuwarden.