Dit is de derde blogpost uit een reeks van zeven blogposts die dieper ingaan 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 naar het doelbestand overbrengen
- Een reeks bestanden openen en data overbrengen naar het doelbestand
- Gegevens opslaan in een recordset
- 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 getoond hoe je 1 bestand opent en gegevens naar je doelbestand overbrengt. Deze blogpost kan als een introductie beschouwd worden voor de volgende posts in deze reeks.
Je hebt voldoende aan een databestand met enkele kolommen data zoals je ziet in onderstaand voorbeeld. Het aantal rijen kan je ook zelf bepalen.
Om alles in goede banen te leiden gebruiken we objectvariabelen die telkens naar de werkboeken en werkbladen verwijzen die we tijdens de overdracht van de data nodig hebben.
De 2 types objecten die we voornamelijk gaan gebruiken zijn ‘WorkBook’ en ‘WorkSheet’.
We hebben er van elk 2 nodig. 1 voor het hoofdbestand (doelbestand) en 1 voor het bronbestand dat de data bevat.
Deze variabele zetten we als volgt op.
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
De koppeling van ons initiële bestand maken we eerst in orde.
Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Worksheets(“Database”)
Het bestand dat de data bevat, gaan we nu openen en toekennen aan de variabelen.
Set wbData = Workbooks.Open(“C:\Users\Pibo\Data.xlsx”)
Set wsData = wbData.worksheets(1)
Laten we er nu van uitgaan dat het datagebied in het bronbestand volledig naar het doelbestand gekopieerd moet worden. Door gebruik te maken van de objecten die we gedefinieerd hebben, hoeven we ons geen zorgen te maken over welk werkboek of welk werkblad actief is.
De ‘copy’ methode bevat een argument ‘destination’ zodat je geen selecties hoeft te maken vooraleer je de gegevens plakt.
wsData.Cells(1, 1).CurrentRegion.Copy wsMaster.Cells(1, 1)
Alle data van het brongebied staan nu in het doelgebied. We hoeven enkel het doelbestand nog te sluiten. Hierbij is het ook belangrijk dat de gebruikte variabelen netjes worden opgekuist.
wbdata.Close False
De parameter ‘SaveChanges’ zet je op ‘False’ aangezien er geen wijzigingen in het bronbestand zijn aangebracht.
Om af te sluiten kuis je de variabelen nog op. Je hebt de verwijzing naar het bestand niet langer nodig. Om te vermijden dat je de gebruikte ruimte verder meedraagt in de code ga je als volgt te werk.
Set wbData = nothing
De volledige code ziet er dan zo uit:
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Set wbMaster = ActiveWorkbook
Set wsMaster = wbMaster.Worksheets(“Database”)
Set wbData = Workbooks.Open(“C:\Users\MyName\Data.xlsx”)
Set wsData = wbData.worksheets(1)
wsData.Cells(1, 1).CurrentRegion.Copy wsMaster.Cells(1, 1)
wbdata.Close False
Set wbData = nothing
Deze methode is de meest eenvoudige om een bestand te openen en er data uit te halen.
Opmerking: Een nadeel van deze methode is dat de uitvoering van de code telkens de tijd vergt die je ook zou nodig hebben om het bestand zelf te openen. Hoe groter het bestand, hoe langer je code nodig heeft om uit te voeren.
Opmerking: Probeer je databestand zo snel mogelijk terug te sluiten. Eens de data in je doelbestand staan, heb je geen nood meer aan het bronbestand. Op die manier hou je het bestand ook niet geblokkeerd voor andere gebruikers.
In de volgende blog gebruiken we dezelfde methode om een reeks bestanden te openen en telkens de data aan ons doelbestand toe te voegen. Later in deze reeks zullen we data uit een reeks bestanden halen zonder dat we de bestanden fysiek gaan openen.
Volgende blogpost: Een reeks bestanden openen en data naar het doelbestand overbrengen (4/7).
Ben je deze reeks blogposts aan het volgen en heb je het gevoel dat je een grondige opfrisbeurt kan gebruiken? Volg dan zeker onze opleiding “Excel: VBA Programmatie”.
