Hur man konsoliderar flera Excel-arbetsböcker med VBA

När du arbetar med olika datakällor kan du ofta kämpa med att sammanställa flera arbetsböcker och kalkylblad innan du kommer fram till en sista databit. Föreställ dig en situation där du har några hundra arbetsböcker att kombinera innan du ens kan börja din dag.

Ingen vill tillbringa oändliga timmar med att arbeta på olika källor, öppna varje arbetsbok, kopiera och klistra in data från olika ark, innan du slutligen gör en konsoliderad arbetsbok. Vad händer om ett VBA-makro kan göra detta åt dig?

Med den här guiden kan du skapa din egen Excel VBA-makrokod för att konsolidera flera arbetsböcker, allt på några minuter (om datafilerna är många).

Förutsättningar för att skapa din egen VBA-makrokod

Du behöver en arbetsbok för att innehålla VBA-koden, medan resten av källdataarbetsböckerna är separata. Skapa dessutom en arbetsbok Konsoliderat för att lagra den konsoliderade data från alla dina arbetsböcker.

Skapa en mapp Konsolidering på din föredragna plats för att lagra alla dina källarbetsböcker. När makrot körs, växlar det genom varje arbetsbok som är lagrad i den här mappen, kopierar innehållet från olika ark och placerar det i den konsoliderade arbetsboken.

Skapa din egen Excel VBA-kod

När förutsättningarna är ur vägen är det dags att fördjupa sig i koden och börja hacka på grunderna för att anpassa den till dina krav.

tryck på Alt+F11 tangenten på Excel för att öppna VBA-makrokodredigeraren. Klistra in koden nedan och spara filen som en makroaktiverad arbetsbok (.xlsm förlängning).

Sub openfiles()
'declare the variables used within the VBA code





	



	



	



	



Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long
'disable these functions to enhance code processing
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With





	



	



	



	



'change the path of the folder where your files are going to be saved
MyFolder = InputBox("Enter path of the Consolidation folder") & ""
'define the reference of the folder in a macro variable





	



	



	



	



MyFile = Dir(MyFolder)
'open a loop to cycle through each individual workbook stored in the folder
Do While Len(MyFile) > 0





	



	



	



	



'activate the Consolidation workbook
Windows("Consolidation").Activate
'calculate the last populated row





	



	



	



	



Range("a1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
'open the first workbook within the Consolidation folder
Workbooks.Open Filename:=MyFolder & MyFile





	



	



	



	



Windows(MyFile).Activate
'toggle through each sheet within the workbooks to copy the data
Dim ws As Worksheet
For Each ws In Sheets





	



	



	



	



ws.Activate
ws.AutoFilterMode = False
'ignore the header and copy the data from row 2
If Cells(2, 1) = "" Then GoTo 1
GoTo 10





	



	



	



	



1: Next
10: Range("a2:az20000").Copy
Windows("Consolidation").Activate





	



	



	



	



'paste the copied contents
ActiveSheet.Paste
Windows(MyFile).Activate





	



	



	



	



'close the open workbook once the data is pasted
ActiveWorkbook.Close
'empty the cache to store the value of the next workbook





	



	



	



	



MyFile = Dir()
'open the next file in the folder
Loop





	



	



	



	



'enable the disabled functions for future use
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

VBA-koden förklaras

Den första delen av koden definierar en subrutin som innehåller all din VBA-kod. Definiera subrutinen med sub, följt av kodens namn. Undernamnet kan vara vad som helst; helst bör du behålla ett namn som är relevant för koden du ska skriva.

Excel VBA förstår användarskapade variabler och deras motsvarande datatyper som deklareras med dämpa (dimensionera).

För att öka bearbetningshastigheten för din kod kan du stänga av skärmuppdatering och undertrycka alla varningar, eftersom det saktar ner kodexekveringen.

Användaren kommer att bli tillfrågad om sökvägen till mappen där datafilerna lagras. En slinga skapas för att öppna varje arbetsbok som är lagrad i mappen, kopiera data från varje ark och lägga till den i Konsolidering arbetsbok.

Arbetsboken Konsolidering aktiveras så att Excel VBA kan beräkna den senast ifyllda raden. Den sista cellen i kalkylbladet väljs och den sista raden beräknas i arbetsboken med hjälp av offsetfunktionen. Detta är mycket användbart när makrot börjar lägga till data från källfilerna.

När loopen öppnar den första källfilen tas filtren bort från varje enskilt ark (om de finns), och data från A2 till AZ20000 kommer att kopieras och klistras in i Consolidation-arbetsboken.

Processen upprepas tills alla arbetsboksblad har lagts till i huvudarbetsboken.

Slutligen stängs källfilen när all data har klistrats in. Nästa arbetsbok öppnas så att VBA-makrot kan upprepa samma steg för nästa uppsättning filer.

Slingan är kodad för att köras tills alla filer automatiskt uppdateras i huvudarbetsboken.

Användarbaserade anpassningar

Ibland vill du inte oroa dig för inbyggda uppmaningar, särskilt om du är slutanvändaren. Om du hellre vill hårdkoda sökvägen till konsolideringsmappen i koden kan du ändra den här delen av koden:

MyFolder = InputBox("Enter path of the Consolidation folder") & ""

Till:

MyFolder = “Folder path” & ""

Dessutom kan du också ändra kolumnreferenserna, eftersom steget inte ingår i denna kod. Ersätt bara slutkolumnreferensen med ditt senast ifyllda kolumnvärde (A-Ö, i det här fallet). Du måste komma ihåg att den senast ifyllda raden beräknas via makrokoden, så du behöver bara ändra kolumnreferensen.

För att få ut det mesta av detta makro kan du bara använda det för att konsolidera arbetsböcker i samma format. Om strukturerna är olika kan du inte använda detta VBA-makro.

Konsolidera flera arbetsböcker med Excel VBA-makro

Att skapa och ändra en Excel VBA-kod är relativt enkelt, speciellt om du förstår några av nyanserna i koden. VBA går systematiskt igenom varje kodrad och exekverar den rad för rad.

Om du gör några ändringar i koden måste du se till att du inte ändrar ordningen på koderna, eftersom det kommer att störa kodens exekvering.

Om författaren

Gaurav Siyal (24 artiklar publicerade)

Gaurav Siyal har två års erfarenhet av att skriva, skriva för en rad digitala marknadsföringsföretag och programvarulivscykeldokument.

Mer från Gaurav Siyal

Prenumerera på vårt nyhetsbrev

Gå med i vårt nyhetsbrev för tekniska tips, recensioner, free e-böcker och exklusiva erbjudanden!

Klicka här för att prenumerera