Category Archives: Uncategorized

Post or page can not been placed into a specific category, group, or classification

Gegevens ophalen met ADO

ADO betekent “ActiveX Data Objects“. Kun je verder vergeten. Wat kun je er mee? Gegevens ophalen uit een ander (Excel)bestand. Dat andere bestand fungeert dan als een soort database. Wat mij betreft kun je net zo goed het bestand openen en de gegevens die je nodig hebt filteren en vervolgens kopiëren om er verder mee te werken.

Excel bestand is tabellen.xlsx en de data staat op het blad shippers. Zie onderstaand screenshot. Dat bestand kun je zelf aanmaken en dan openen in Excel om het te kunnen lezen.

Wie persé die voorziening wil gebruiken hier twee voorbeelden.
Onderstaande code kopieren en plakken in blanco werkmap. Zorg dat de blanco werkmap op de voorgrond staat en dan de code laten uitvoeren.

VOORBEELD 1

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_1()
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Paste data in activesheet
    ActiveSheet.Range("A2").CopyFromRecordset objGegevensSet
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

VOORBEELD 2

Option Explicit

'***********************************************
'Set reference to:
'Microsoft ActiveX Data Objects Library
'Via: Alt+F11 | Extra | References
'***********************************************

Sub Voorbeeld_ADO_Verbinding_Voorbeeld_2()
    Dim arrMatrix As Variant
    Dim objVerbinding As New ADODB.Connection
    Dim objGegevensSet As New ADODB.Recordset
    Dim strPad As String, strVerbind As String, strSQL As String
    Dim i As Long, j As Long
    
    'Path to file
    strPad = "C:\temp\tabellen.xlsx"
    
    strVerbind = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & strPad & ";HDR=Yes';"
    
    objVerbinding.Open strVerbind
    
    'Tablename is de sheetname in this example [shippers$]
    'Dollarsign is mandatory behind shippers.
    strSQL = "SELECT * From [shippers$]"
        
    objGegevensSet.Open strSQL, objVerbinding
    
    'Load data in Array
    arrMatrix = objGegevensSet.GetRows
    
    'Paste data from array to activesheet
    For i = 0 To UBound(arrMatrix, 2) 'Rows
        For j = 0 To UBound(arrMatrix, 1) 'Columns
            ActiveSheet.Cells(i + 2, j + 1).Value = arrMatrix(j, i)
        Next j
    Next i
    
    'Close dataset
    objGegevensSet.Close
    
    'Close connection
    objVerbinding.Close
End Sub

Cijfers van bepaalde maanden opzoeken

Stel, je hebt verkoopcijfers of iets dergelijks en je wilt bepaalde maanden van een bepaalde verkoper optellen. In dit voorbeeld de maanden Februari en Mei. Tabel staat hier onder.
Let eerst op kolom I. In I1 zet je Id, en in I3 Totaal. In I2 kun je de id van de verkoper typen maar handiger is om daar een keuzelijst van te maken. Als je unieke waarden hebt in kolom A is dat een fluitje van een cent. Kies voor:

Gegevens | Gegevensvalidatie | Gegevensvalidatie | Toestaan > Lijst | Bron > selecteer de waarden in kolom A

Nu kun je gewoon een waarde in I2 kiezen middels de keuzelijst.

Formule gaat in cel I4:

=SOM(SOMMEN.ALS(INDEX($B$2:$G$10;VERGELIJKEN($I$2;$A$2:$A$10;0);0);$B$1:$G$1;{“februari”\”Mei”}))

Let even speciaal op het rode gedeelte in de formule. Daar geef je de maanden op die je wilt tellen, gescheiden door een backslash.Let ook op de Accolades { }

Zoeken, dubbele waarden in Kolom B

Je wilt een item opzoeken maar het probleem is dat er dubbele waarden in kolom B staan. Als je VLOOKUP gebruikt, wordt alleen de 1-ste overeenkomst gevonden. Maar wat als je de 2-de of 3-de overeenkomst wil vinden. De oplossing is dat je een extra kolom toevoegt (Kolom A) en de dubbele items, Kolom B, van een volgnummer voorziet.

Het volgnummer kun je op de volgende manier genereren:
We gaan er van uit dat er maximaal 3 dubbele waarden voorkomen. In het gele gedeelte, Kolom H, vul je de parameters in:
[H1] Type je de waarde die je wilt zoeken. Bijvoorbeeld: ITEM 3
[H2] laat je leeg
[H3] zet je, 1
[H4] zet je, 2
[H5] zet je, 3

In A2 zet je de formule om de volgnummers te genereren.
[A2] =IF(COUNTIF($B$2:$B$14;$H$1)>1;IF(B2=$H$1;COUNT($A$1:A1)+1;””);””)
Vervolgens doorvoeren naar beneden tot A14

Tenslotte,
[H8] =H2
[H9] =IFERROR(INDEX($C$2:$C$14;MATCH(H$2&$H1;MATCH($A$2:$A$14&$B$2:$B$14;);0));””)
Formule doorvoeren naar beneden tot H12.

De truc is eigenlijk dat je het volgnummer en het item samenvoegt tot één zoekwaarde d.m.v. het Ampersand teken. Dat gebeurt in het rood gemerkte gedeelte:
H$2&$H1 wordt dus in feite 1ITEM 3. Hetzelfde gebeurt met de waarden in de kolommen A en B: $A$2:$A$14&$B$2:$B$14

Prijsverloop tonen

[F3]=SUM(IF(A3:A27=F2;IF(ISNUMBER(D3:D27);1)))

[D3]=IF(ISNA(MATCH(C3;$D$2:D2;0));C3;IF(ISNA(MATCH(C3;$D$2:D2;0))*ISNA(MATCH($A3&”|”&$B3;$A$2:A2&”|”&$B$2:B2;0));C3;IF(ISNUMBER(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2));IF(LOOKUP(9,99999999999999E+307;1/($A$2:A2=A3);$C$2:C2)<>C3;C3;”#”);”#”)))

[E5]=IF(ROWS($E$3:E3)<=$F$3;INDEX($B$3:$B$27;SMALL(IF($A$3:A27=$F$2;IF(ISNUMBER($D$3:$D$27);ROW($B$3:$B$27)-ROW($B$3)+1));ROWS($E$3:E3)));””)

[F5]=IF($E5=””;””;INDEX($D$3:$D$27;SMALL(IF($A$3:$A$27=$F$2;IF($B$3:$B$27=$E5;ROW($D$3:$D$27)-ROW($D$3)+1));COUNTIFS($E$5:E5;E5))))

Let op: Alle formules invoeren met Ctrl+Shift+Enter.

[D3], [E5], [F5] doorvoeren naar beneden.

Data van kolommen naar rijen

Een klant heeft meerdere produkten besteld. Het ID van de klant staat in kolom A. Merk op dat dezelfde ID meerdere keren voorkomt.  De produkten staan in kolom B. In kolom C zet je de unieke ID’s.

Formule in D2:

[D2] =IFERROR(INDEX($B$2:$B$13;SMALL(IF($A$2:$A$13=$C2;ROW(A$2:A$13)-ROW(A$2)+1);COLUMNS($D$2:D2)));””)

Invoeren met Ctrl+Shift+Enter
Vervolgens doorvoeren naar rechts en naar beneden.

Snel selecteren

Handige truc:
Je wilt in de lichtgroene cellen dezelfde waarde typen. Duurt lang om alle cellen een voor een te selecteren door de Ctrl-toets ingedrukt te houden, de waarde te tikken en te bevestigen met Ctrl+Enter.

Je kunt ook het gebied A4:M11 selecteren.

Dan F5 | Special | Select Blanks  | OK
Waarde intikken en bevestigen met Ctrl+Enter. Klinkt langer maar is korter.

Opmerkingen van cel in andere cel weergeven

Excel heeft een functie waarmee je allerlei opmerking aan een cel kunt toevoegen. Er verschijnt vervolgens zo’n rood driehoekje rechtsboven in de cel.

Als je de gegevens in die opmerking zichtbaar wil maken, ga je er met de cursor overheen. Stel je wilt de gegevens in de opmerking gebruiken en je hebt 100 opmerkingen verspreid over je werkblad. Dan heb je veel werk.

Deze functie kan de opmerking in een cel plaatsen.
[D5] =getOpmerking(A2)

Function getOpmerking(rngCel) As String
    'Geeft de opmerkingen van een cel weer als tekenreeks
    On Error Resume Next
    'Herberekenen
    Application.Volatile True
    getOpmerking = rngCel.Comment.Text
End Function