Ik kreeg een telefoontje van een vriend die meerdere Excel-bestanden wilde combineren in één Excel-werkmap. Hij had veel bestanden in een map en hij wilde alle werkbladen van alle werkmappen in één enkele werkmap krijgen.
Hoewel dit handmatig kan worden gedaan, zou het tijdrovend en foutgevoelig zijn.
Een eenvoudige VBA-code kan dit echter in een paar seconden doen.
Combineer meerdere Excel-bestanden in één bestand
Hier is de code die meerdere Excel-werkmappen in een opgegeven map kan combineren in één Excel-werkmap:
Sub ConslidateWorkbooks() 'Gemaakt door Sumit Bansal van https://trumpexcel.com Dim FolderPath As String Dim Filename As String Dim Sheet As Worksheet Application.ScreenUpdating = False FolderPath = Environ("userprofile") & "DesktopTest" Bestandsnaam = Dir( FolderPath & "*.xls*") Do While Filename "" Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True voor elk blad in ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Volgende bladwerkmappen (Bestandsnaam).Sluit Bestandsnaam = Dir() Loop Application.ScreenUpdating = True End Sub
Hoe deze code te gebruiken?
Dit zijn de stappen om deze code te gebruiken:
- Zet alle Excel-bestanden die u wilt combineren in een map. Voor deze tutorial heb ik een map gemaakt met de naam Test en zes bestanden erin (4 Excel-werkmappen en 1 Power Point en Word elk).
- Open een nieuwe Excel-werkmap.
- Druk op ALT + F11 (of ga naar Ontwikkelaar -> Code -> Visual Basic). Hiermee wordt de Visual Basic-editor geopend.
- Klik in de VB Editor, in de Project Editor, met de rechtermuisknop op een van de objecten voor de werkmap en ga naar Invoegen -> Module. Hiermee wordt een module voor de werkmap ingevoegd.
- Dubbelklik op de module. Het opent het codevenster aan de rechterkant.
- Kopieer en plak de bovenstaande code in het codevenster.
- In de code moet u de volgende regel code wijzigen:
FolderPath = Omgeving ("gebruikersprofiel") & "DesktopTest"Wijzig in deze regel het gedeelte tussen dubbele aanhalingstekens (gemarkeerd in oranje) met de locatie van de map waarin u de bestanden hebt die u wilt combineren. In de hierboven gebruikte code bevindt de map zich op het bureaublad. Als u het op een andere locatie heeft, geeft u dat pad hier op.
- Plaats de cursor ergens in de code en klik op de groene afspeelknop in de Toolbar-opties (of druk op de F5-toets).
Hiermee wordt de code uitgevoerd en worden alle werkbladen van alle Excel-bestanden in de map geconsolideerd in een enkele werkmap.
Hoe werkt deze code?
- De code gebruikt de DIR-functie om de bestandsnamen uit de opgegeven map te halen.
- De volgende regel wijst de eerste Excel-bestandsnaam toe aan de variabele 'Bestandsnaam'.
Bestandsnaam = Dir(Mappad & “*.xls*”) - Vervolgens wordt de Do While-lus gebruikt om te controleren of alle bestanden zijn afgedekt.
- Binnen de 'Do While'-lus wordt de 'For Each'-lus gebruikt om alle werkbladen te kopiëren naar de werkmap waarin we de code uitvoeren.
- Aan het einde van de Do Loop wordt de volgende regel code gebruikt: Bestandsnaam = Dir(). Het wijst de volgende Excel-bestandsnaam toe aan de variabele Bestandsnaam en de lus begint opnieuw.
- Wanneer alle bestanden bedekt zijn, retourneert de DIR-functie een lege tekenreeks, dat is wanneer de lus eindigt.
Hier is een uitleg van de DIR-functie in de MSDN-bibliotheek:
Dir geeft de eerste bestandsnaam terug die overeenkomt met padnaam. Om extra bestandsnamen te krijgen die overeenkomen met padnaam, bel Dir weer zonder argumenten. Als er geen bestandsnamen meer overeenkomen, Dir retourneert een tekenreeks met lengte nul ("").
Heb je ooit zoiets geprobeerd met VBA? Deel wat je hebt gedaan en we kunnen er allemaal van leren.
Bespaar gekke hoeveelheid tijd met VBA. Bekijk de Excel VBA CURSUS.
