Dit is blogpost 4 uit een reeks van 7 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 overbrengen naar het doelbestand
- 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
Deze blogpost is een aanpassing van het voorbeeld uit de vorige post waar we 1 bestand gebruikten om data naar een doelbestand over te zetten. Deze keer gaan we data uit verschillende bestanden halen. We moeten dus naar een folder verwijzen. De foldernaam kan in de code opgenomen worden of via een dialoog door de eindgebruiker geselecteerd worden.
Als eerste zullen we bespreken hoe je meerdere bestanden uit een folder achter elkaar opent, na het overzetten van de gegevens de bestanden weer netjes sluit en de gebruikte object variabelen opkuist. Daarna besteden we aandacht aan het overbrengen van de data waarvan we de basis in blogpost 3 hebben besproken.
We starten met het selecteren van de folder waarin de bestanden die we gaan openen staan verzameld. Het eerste voorbeeld zal gebaseerd zijn op een vaste foldernaam. Het tweede voorbeeld geeft de gebruiker van de code de mogelijkheid om zelf een folder aan te duiden.
Je start met het aanmaken van 3 variabelen. 1 voor de folder, 1 voor de naam van het bestand dat je telkens opent en 1 waarin je het werkboek stockeert.
Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook
Via de ingebouwde ‘Dir’ functie kan je een bestand of een folder naar een variabele overbrengen. Als de folder geen gevraagde inhoud bevat, zal een lege tekststring terug gegeven worden.
myfolder = “C:\Users\Pibo”
myFile = Dir(myfolder & “*.xlsx”)
In dit voorbeeld wordt gebruik gemaakt van het wildcard karakter ‘*’. Tijdens het uitvoeren van deze opdracht zal het eerste bestand dat aan de vraag voldoet naar de variabele overgebracht worden. Let erop dat dit nog maar enkel de naam van het bestand is. Je gebruikt deze variabele om later het bestand te openen.
Als je dit in een lus inbouwt, ziet dat er als volgt uit.
Do While myFile <> “”
Set wbData = Workbooks.Open(myfolder & “\” & myFile)
‘VBA Coding
wbData.Close False
Set wbData = Nothing
‘VBA Coding
myFile = Dir
Loop
De opdracht ‘myFile = Dir’ zal het volgende bestand uit de folder teruggeven aan de variabele, vergelijkbaar met het ‘Next’ commando in een ‘for-next’ lus. De lus stopt zodra er geen bestand meer overblijft (myfile = “”).
Tip: Probeer het geopende bestand zo snel mogelijk terug te sluiten. Als je bewerkingen op de data wil uitvoeren, is het beter dat je de data eerst naar het doelbestand kopieert, dan je databestand sluit en dan de bewerkingen in het doelbestand uitvoert.
Met behulp van 3 extra variabelen en de uitleg die je in de vorige blog in deze reeks kan vinden, maken we de code compleet. In onderstaand voorbeeld kopiëren we alle data uit het geopende bestand naar het doelbestand. We moeten daarvoor ook telkens het juiste rijnummer bepalen om de nieuwe data in te kopiëren.
Sub OpenMultipleFiles()
Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Dim wbData As Workbook
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim lngRow As Long
Application.ScreenUpdating = False
Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Worksheets(1)
myfolder = “C:\Users\Pibo”
myFile = Dir(myfolder & “\*.xlsx”)
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
Do While myFile <> “”
Set wbData = Workbooks.Open(myfolder & “\” & myFile)
wbData.Sheets(1).Cells(1, 1).CurrentRegion.Copy wsSource.Cells(lngRow, 1)
wbData.Close False
Set wbData = Nothing
lngRow = wsSource.Cells(1, 1).CurrentRegion.Rows.Count + 1
myFile = Dir
Loop
Set wsSource = Nothing
Application.ScreenUpdating = True
End Sub
In dit voorbeeld heb ik 14 identieke bestanden geopend met elk 264 cellen data. Bovenstaande code heeft ongeveer 6 seconden nodig op mijn toestel. De bestanden hebben een grootte van 9 Kb. De meeste tijd gaat verloren aan het fysiek openen van de bestanden. Dat duurt in het voorbeeld even lang als het openen van het bestand zelf. En dat gebeurt 14 keer.
In de volgende blogpost van deze reeks leren we hoe we data in een ‘recordset’ kunnen bijhouden. De daarop volgende blogpost toont dan hoe je via zo’n ‘recordset’ data uit een bestand kan halen zonder het bestand fysiek te openen.
Extra: Als je geen vaste folder in je code kan opnemen en je de keuze aan de gebruiker wil overlaten, kan je onderstaand voorbeeld toepassen.
Dim fldr As FileDialog
Dim myfolder As String
Dim myFile As String
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = “Select a Folder”
.AllowMultiSelect = False
.InitialFileName = “C:\Users\Pibo”
If .Show <> -1 Then Exit Sub
myfolder = .SelectedItems(1)
End With
myFile = Dir(myfolder & “\*.xlsb”)
De ‘FileDialogFolderPicker’ is een dialoogvenster gelijkaardig aan ‘File Open’ en dient specifiek om een folder te selecteren. De gebruiker duidt een folder aan (niet openen) en bevestigt met ‘ok‘.
De andere eigenschappen in de code:
- ‘AllowMultiselect’ : False geeft aan dat er slechts 1 folder gekozen mag worden.
- ‘InitialFileName’ : Verwijst naar de folder die in het dialoogvenster getoond wordt.
- ‘Show’ : Als de gebruiker beslist om op ‘annuleren’ te klikken is show = true.
- ‘SelectedItems(1)’ : De naam van het eerste item van de selectie (hier maar 1 folder) wordt in de variabele ‘myfolder’ opgeslagen.
Volgende blogpost: Gegevens in een recordset opslaan (5/7).
Heb je na het lezen van deze blogpost de Excel- of zelfs de Power BI-microbe te pakken? Bekijk dan zeker ons aanbod aan Excel en Power BI opleidingen
