Dit is blogpost nummer 5 van de reeks die dieper ingaat op een aantal technieken die datatransformatie in Excel VBA kunnen versnellen.
- Kopiëren van cellen met VBA
- Gegevens toevoegen aan een tabel
- Een bestand openen en data overbrengen naar het doelbestand
- Een reeks bestanden openen en data overbrengen naar het doelbestand
- Gegevens in een recordset opslaan
- Gegevens uit meerdere bestanden uitlezen met een recordset zonder de bestanden te openen
- Een recordset maken, gebaseerd op data uit 2 verschillende worksheets
In deze blogpost wordt de techniek besproken die gebruikt kan worden om data in een ‘recordset’ op te slaan. Een ‘recordset’ is een virtuele matrix van rijen en kolommen die ondersteund wordt door een aantal technieken om door de ‘recordset’ te navigeren. Met een recordset creëer je dus een virtuele tabel in het geheugen.
De techniek van ‘recordsets’ is vooral bekend in database omgevingen en is bijvoorbeeld in MS Access onmisbaar tijdens het programmeren. Recordsets worden gevuld met SQL queries. Een eenvoudig voorbeeld hiervan is ‘Select * from Table’ of in Excel ‘Select * from Range’.
In deze blogpost wordt een toepassing van een recordset getoond maar het is niet de bedoeling om de mogelijkheden van ‘recordsets’ in detail te behandelen.
De structuur van een recordset ziet er als volgt uit.
‘BeginOfFile’ (BOF) en ‘EndOfFile’ (EOF) zijn aanduidingen die aangeven of je vóór het eerste of na het laatste record in de recordset staat. Als je gegevens in een recordset opslaat en beiden zijn ‘true’, is je recordset leeg.
Iedere rij bestaat uit meerdere kolommen die afhankelijk zijn van de gegevensstructuur die je aan de set hebt toegevoegd.
Er bestaan 2 types ‘recordsets’ die je kan gebruiken. Deze keuze is in principe afhankelijk van het soort database waarvan je gebruikmaakt. Vaak wordt die bepaald door de persoonlijke voorkeur van de ontwikkelaar die ermee gaat werken.
Je kan gebruikmaken van een ‘recordset’ van het type ADO of DAO. De werkwijze om te navigeren, data te lezen of data aan te passen zijn bij beiden nagenoeg identiek. In deze blogpost gebruiken we ADO.
Recordsets zijn een alternatief voor het ophalen van gegevens uit een ander bestand of werkblad. Je kan data opslaan in een recordset en onmiddellijk je databestand afsluiten. Daarna voer je bewerkingen of analyses uit op de recordset.
Opmerking: Een recordset haalt data uit een bestand. Enkel op dat moment is het bestand open. Dat heeft als voordeel dat je het bestand niet verder hoeft op te volgen. Het bestand zal niet in de ‘taskmanager’ getoond worden en als ontwikkelaar heb je ook geen code nodig om het bestand te sluiten.
In deze blog worden enkele voorbeelden getoond waarbij een recordset wordt toegepast om data te verzamelen en te kopiëren. In het eerste voorbeeld openen we een grote file met 5000 rijen en 100 kolommen.
Voor je kan starten met je code moet je eerst een referentie naar de ADO bibliotheek leggen. (Microsoft ActiveX Data Objects 6.1 Library)
Dat doe je in de VBA interface op de volgende manier:
Je vindt de referentie alfabetisch terug in de lijst. Na de selectie en bij het opnieuw openen van het ‘references’ scherm zal de selectie boven in het scherm vermeld staan.
De VBA code ziet er als volgt uit:
Sub CopyWithADODB()
‘ Reference to: Microsoft ActiveX Data Objects 6.1 Library
‘5000 Rows & 10000 Columns (File size 12,3 MB Type:Binary)
Dim myConnection As String
Dim RS As ADODB.Recordset
Dim mySQL As String
Dim strPath As String
Application.ScreenUpdating = False
strPath = “C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData.xlsb”
myConnection = “Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=” & strPath & “;Extended Properties=Excel 12.0”
mySQL = “SELECT * FROM [Sheet1$A1: CV5000]”
Set RS = New ADODB.Recordset
RS.Open mySQL, myConnection, adOpenUnspecified, adLockUnspecified
Range(“A1”).CopyFromRecordset RS
RS.Close
Set RS = Nothing
Application.ScreenUpdating = True
End Sub
Bij het schrijven van deze code kan je heel wat obstakels tegenkomen. Het versienummer van Excel (hier 12.0) moet correct zijn en de schrijfwijze van het SQL-commando mag geen fouten bevatten. De connectie kan een hele reeks parameters vermelden, wat ook sneller foutmeldingen geeft. Het is beter om zonder deze parameters te starten en ze stelselmatig toe te voegen terwijl je verder test.
Als alternatief van een vast gebied kan je ook een heel werkblad inlezen:
‘mySQL = “SELECT * FROM [Sheet1$]”
De methode ‘CopyFromRecordset’ plakt de totale inhoud van de recordset in de aangegeven cel.
In het volgende voorbeeld voeren we dezelfde actie uit door gebruik te maken van de standaard methode voor het openen van een bestand via workbooks.open. Het uitvoeren van deze code duurt even lang als de werkwijze met de recordset. Maar bij een herhaling duurt de uitvoering slechts 3 seconden. Waarschijnlijk is dit een trucje van Excel omdat de grafische opbouw nog in het geheugen zit. Sluit je Excel en voer je de code opnieuw uit, dan zal je de eerste keer opnieuw 12 seconden nodig hebben.
Je zou kunnen concluderen dat beide mogelijkheden even lang duren. Toch zal de werkwijze met een recordset net een beetje sneller zijn. Beide methodes zullen het bestand openen maar de Excel ‘Workbooks.open’ opdracht zal ook het grafische werk op zich moeten nemen. Bij het openen van meerdere bestanden via de recordset-methode zal de tijdswinst gradueel toenemen.
Sub OpenFileCopyToDestination()
Dim wbData As Workbook
Dim wbMain As Workbook
Debug.Print Now
Set wbMain = ActiveWorkbook
Application.ScreenUpdating = False
Set wbData = Workbooks.Open(“C:\Xylos\BLOG\SPEED_BLOG_EXCEL\ALotOfData2.xlsb”)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wbMain.Sheets(“Sheet1”).Cells(1, 1)
wbData.Close False
Application.ScreenUpdating = True
Debug.Print Now
End Sub
In de volgende blogpost zullen we dezelfde techniek gebruiken om via een lus meerdere bestanden te openen en opnieuw de data naar het doelbestand over te brengen.
Volgende blogpost: Gegevens uit meerdere bestanden met een recordset uitlezen zonder de bestanden te openen (6/7).
Ben je deze reeks blogposts aan het volgen en heb je het gevoel dat je Excel-kennis een grondige opfrisbeurt? Volg dan zeker één van onze open Excel-opleidingen.
