i den här handledningen kommer jag att täcka hur man arbetar med arbetsböcker i Excel med VBA.

i Excel är en arbetsbok ett objekt som ingår i samlingen arbetsböcker. I en arbetsbok har du olika objekt som kalkylblad, diagramblad, celler och intervall, diagramobjekt, former etc.

med VBA kan du göra många saker med ett arbetsboksobjekt-till exempel öppna en specifik arbetsbok, spara och stäng arbetsböcker, skapa nya arbetsböcker, ändra arbetsbokens egenskaper etc.

så låt oss komma igång.

Om du är intresserad av att lära dig VBA på det enkla sättet, kolla in min Online Excel VBA-utbildning.

referera till en arbetsbok med VBA

det finns olika sätt att hänvisa till ett Arbetsboksobjekt i VBA. Metoden du väljer beror på vad du vill göra. I det här avsnittet kommer jag att täcka de olika sätten att hänvisa till en arbetsbok tillsammans med några exempelkoder.

använda arbetsbokens namn

Om du har det exakta namnet på arbetsboken som du vill hänvisa till kan du använda namnet i koden.

låt oss börja med ett enkelt exempel.

Om du har två arbetsböcker öppna och du vill aktivera arbetsboken med namnet – exempel.xlsx, du kan använda nedanstående kod:

Sub ActivateWorkbook()Workbooks("Examples.xlsx").ActivateEnd Sub

Observera att du måste använda filnamnet tillsammans med tillägget om filen har sparats. Om det inte har sparats kan du använda namnet utan filtillägget.

om du inte är säker på vilket namn du ska använda, ta hjälp av Project Explorer.

om du vill aktivera en arbetsbok och välja en specifik cell i ett kalkylblad i den arbetsboken måste du ange hela adressen till cellen (inklusive arbetsboken och kalkylbladets namn).

Sub ActivateWorkbook()Workbooks("Examples.xlsx").Worksheets("Sheet1").ActivateRange("A1").SelectEnd Sub

ovanstående kod aktiverar först Ark1 i exemplen.XLSX arbetsbok och väljer sedan cell A1 i arket.

du ser ofta en kod där en referens till ett kalkylblad eller en cell / intervall görs utan att referera till arbetsboken. Detta händer när du hänvisar till kalkylbladet/intervallen i samma arbetsbok som har koden i den och också är den aktiva arbetsboken. I vissa fall måste du dock ange arbetsboken för att se till att koden fungerar (mer om detta i avsnittet ThisWorkbook).

använda indexnummer

Du kan också hänvisa till arbetsböckerna baserat på deras indexnummer.

Om du till exempel har tre arbetsböcker öppna visar följande kod namnen på de tre arbetsböckerna i en meddelanderuta (en i taget).

Sub WorkbookName()MsgBox Workbooks(1).NameMsgBox Workbooks(2).NameMsgBox Workbooks(3).NameEnd Sub

ovanstående kod använder MsgBox – vilket är en funktion som visar en meddelanderuta med angiven text / värde (vilket är arbetsbokens namn i det här fallet).

en av de problem jag ofta har med att använda indexnummer med arbetsböcker är att du aldrig vet vilken som är den första arbetsboken och vilken som är den andra och så vidare. För att vara säker måste du köra koden som visas ovan eller något liknande slinga genom de öppna arbetsböckerna och känna till deras indexnummer.

Excel behandlar arbetsboken som öppnades först för att ha indexnumret som 1 och nästa som 2 och så vidare.

trots denna nackdel kan det vara praktiskt att använda indexnummer. Om du till exempel vill slinga igenom alla öppna arbetsböcker och spara alla kan du använda indexnumren. I det här fallet, eftersom du vill att detta ska hända med alla arbetsböcker, är du inte orolig för deras individuella indexnummer.

nedanstående kod skulle slinga igenom alla öppna arbetsböcker och stänga alla utom arbetsboken som har denna VBA-kod.

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).CloseEnd IfNext iEnd Sub

ovanstående kod räknar antalet öppna arbetsböcker och går sedan igenom alla arbetsböcker med hjälp av för varje slinga.

den använder IF-villkoret för att kontrollera om namnet på arbetsboken är detsamma som i arbetsboken där koden körs.

om det inte är en matchning stänger den arbetsboken och flyttar till nästa.

Observera att vi har kört slingan från WbCount till 1 med ett steg på -1. Detta görs som med varje slinga minskar antalet öppna arbetsböcker.

Dettaarbetsbok behandlas i detalj i det senare avsnittet.

använda ActiveWorkbook

ActiveWorkbook, som namnet antyder, hänvisar till arbetsboken som är aktiv.

nedanstående kod visar namnet på den aktiva arbetsboken.

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameEnd Sub

När du använder VBA för att aktivera en annan arbetsbok börjar ActiveWorkbook-delen i VBA efter det att hänvisa till den aktiverade arbetsboken.

Här är ett exempel på detta.

om du har en arbetsbok aktiv och du sätter in följande kod i den och kör den, skulle den först visa namnet på arbetsboken som har koden och sedan namnet på exemplen.xlsx (som aktiveras av koden).

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameWorkbooks("Examples.xlsx").ActivateMsgBox ActiveWorkbook.NameEnd Sub

Observera att när du skapar en ny arbetsbok med VBA blir den nyskapade arbetsboken automatiskt den aktiva arbetsboken.

använda ThisWorkbook

ThisWorkbook hänvisar till arbetsboken där koden körs.

varje arbetsbok skulle ha ETT ThisWorkbook-objekt som en del av det (synligt i Projektutforskaren).

’ThisWorkbook’ kan lagra vanliga makron (liknande de som vi lägger till moduler) samt händelseprocedurer. En händelseprocedur är något som utlöses baserat på en händelse – som att dubbelklicka på en cell eller spara en arbetsbok eller aktivera ett kalkylblad.

varje händelseprocedur som du sparar i denna ’ThisWorkbook’ skulle vara tillgänglig i hela arbetsboken, jämfört med de händelser på arknivå som endast är begränsade till de specifika bladen.

om du till exempel dubbelklickar på ThisWorkbook-objektet i Projektutforskaren och kopierar och klistrar in koden nedan visas celladressen när du dubbelklickar på någon av cellerna i hela arbetsboken.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)MsgBox Target.AddressEnd Sub

medan Thisworkbooks huvudroll är att lagra händelseproceduren kan du också använda den för att hänvisa till arbetsboken där koden körs.

nedanstående KOD Returnerar namnet på arbetsboken där koden körs.

Sub ThisWorkbookName()MsgBox ThisWorkbook.NameEnd Sub

fördelen med att använda ThisWorkbook (över ActiveWorkbook) är att den skulle hänvisa till samma arbetsbok (den som har koden i den) i alla fall. Så om du använder en VBA-kod för att lägga till en ny arbetsbok, skulle ActiveWorkbook ändras, men ThisWorkbook skulle fortfarande hänvisa till den som har koden.

skapa ett nytt Arbetsboksobjekt

följande kod skapar en ny arbetsbok.

Sub CreateNewWorkbook()Workbooks.AddEnd Sub

När du lägger till en ny arbetsbok blir den den aktiva arbetsboken.

följande kod lägger till en ny arbetsbok och visar sedan namnet på den arbetsboken (vilket skulle vara standardnamnet Book1-typ).

Sub CreateNewWorkbook()Workbooks.AddMsgBox ActiveWorkbook.NameEnd Sub

öppna en arbetsbok med VBA

Du kan använda VBA för att öppna en specifik arbetsbok när du känner till sökvägen till arbetsboken.

nedanstående kod öppnar arbetsboken-exempel.xlsx som finns i mappen Dokument på mitt system.

Sub OpenWorkbook()Workbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")End Sub

om filen finns i standardmappen, som är mappen där VBA sparar nya filer som standard, kan du bara ange arbetsbokens namn – utan hela sökvägen.

Sub OpenWorkbook()Workbooks.Open ("Examples.xlsx")End Sub

om arbetsboken som du försöker öppna inte finns visas ett fel.

för att undvika detta fel kan du lägga till några rader i din kod för att först kontrollera om filen finns eller inte och om den finns, försök sedan öppna den.

nedanstående kod skulle kontrollera filplatsen och om den inte finns kommer den att visa ett anpassat meddelande (inte felmeddelandet):

Sub OpenWorkbook()If Dir("C:\Users\sumit\Documents\Examples.xlsx") <> "" ThenWorkbooks.Open ("C:\Users\sumit\Documents\Examples.xlsx")ElseMsgBox "The file doesn't exist"End IfEnd Sub

Du kan också använda dialogrutan Öppna för att välja filen som du vill öppna.

Sub OpenWorkbook()On Error Resume NextDim FilePath As StringFilePath = Application.GetOpenFilenameWorkbooks.Open (FilePath)End Sub

ovanstående kod öppnar dialogrutan Öppna. När du väljer en fil som du vill öppna tilldelar den filsökvägen till filepath-variabeln. Arbetsbok.Öppna använder sedan filvägen för att öppna filen.

om användaren inte öppnar en fil och klickar på Avbryt-knappen blir FilePath falsk. För att undvika att få ett fel i det här fallet har vi använt uttalandet ’On Error Resume Next’.

spara en arbetsbok

för att spara den aktiva arbetsboken, använd koden nedan:

Sub SaveWorkbook()ActiveWorkbook.SaveEnd Sub

den här koden fungerar för arbetsböcker som redan har sparats tidigare. Också, eftersom arbetsboken innehåller ovanstående Makro, om det inte har sparats som en .xlsm (eller .xls) fil, kommer du att förlora makrot när du öppnar den nästa.

om du sparar arbetsboken för första gången visas en prompt som visas nedan:

När du sparar för första gången är det bättre att använda alternativet ”Saveas”.

nedanstående kod skulle spara den aktiva arbetsboken som en .xlsm-fil på standardplatsen (som är dokumentmappen i mitt system).

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

om du vill att filen ska sparas på en viss plats måste du nämna det i Filnamnsvärdet. Nedanstående kod sparar filen på mitt skrivbord.

Sub SaveWorkbook()ActiveWorkbook.SaveAs Filename:="C:\Users\sumit\Desktop\Test.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

om du vill att användaren ska få möjlighet att välja platsen för att spara filen kan du använda Ring dialogrutan Saveas. Koden nedan visar dialogrutan Saveas och låter användaren välja platsen där filen ska sparas.

Sub SaveWorkbook()Dim FilePath As StringFilePath = Application.GetSaveAsFilenameActiveWorkbook.SaveAs Filename:=FilePath & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabledEnd Sub

Observera att istället för att använda filformat:=xlOpenXMLWorkbookMacroEnabled kan du också använda filformat:=52, där 52 är koden xlOpenXMLWorkbookMacroEnabled.

spara alla öppna arbetsböcker

om du har mer än en arbetsbok öppen och du vill spara alla arbetsböcker kan du använda koden nedan:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In Workbookswb.SaveNext wbEnd Sub

ovanstående sparar alla arbetsböcker, inklusive de som aldrig har sparats. Arbetsböckerna som inte har sparats tidigare skulle sparas på standardplatsen.

om du bara vill spara de arbetsböcker som tidigare har sparats kan du använda koden nedan:

Sub SaveAllWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Path <> "" Thenwb.SaveEnd IfNext wbEnd Sub

spara och stänga alla arbetsböcker

om du vill stänga alla arbetsböcker, förutom arbetsboken som har den aktuella koden i den, kan du använda koden nedan:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

ovanstående kod skulle stänga alla arbetsböcker (förutom arbetsboken som har koden-ThisWorkbook). Om det finns ändringar i dessa arbetsböcker sparas ändringarna. Om det finns en arbetsbok som aldrig har sparats visas dialogrutan Spara som.

spara en kopia av arbetsboken (med tidsstämpel)

När jag arbetar med komplexa data och instrumentpanel i Excel-arbetsböcker skapar jag ofta olika versioner av mina arbetsböcker. Detta är användbart om något går fel med min nuvarande arbetsbok. Jag skulle åtminstone ha en kopia av den sparad med ett annat namn (och jag skulle bara förlora det arbete jag gjorde efter att ha skapat en kopia).

här är VBA-koden som skapar en kopia av din arbetsbok och sparar den på den angivna platsen.

Sub CreateaCopyofWorkbook()ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy.xlsm"End Sub

ovanstående kod skulle spara en kopia av din arbetsbok varje gång du kör detta makro.

även om detta fungerar bra, skulle jag känna mig mer bekväm om jag hade olika kopior sparade när jag kör den här koden. Anledningen till att detta är viktigt är att om jag gör ett oavsiktligt misstag och kör detta makro, kommer det att spara arbetet med misstagen. Och jag skulle inte ha tillgång till arbetet innan jag gjorde misstaget.

för att hantera sådana situationer kan du använda nedanstående kod som sparar en ny kopia av arbetet varje gång du sparar det. Och det lägger också till ett datum och tidsstämpel som en del av arbetsbokens namn. Detta kan hjälpa dig att spåra eventuella misstag du gjorde eftersom du aldrig förlorar någon av de tidigare skapade säkerhetskopiorna.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)ThisWorkbook.SaveCopyAs Filename:="C:\Users\sumit\Desktop\BackupCopy" & Format(Now(), "dd-mm-yy-hh-mm-ss-AMPM") & ".xlsm"End Sub

ovanstående kod skulle skapa en kopia varje gång du kör detta makro och lägga till ett datum / tidsstämpel i arbetsbokens namn.

skapa en ny arbetsbok för varje kalkylblad

i vissa fall kan du ha en arbetsbok som har flera kalkylblad och du vill skapa en arbetsbok för varje kalkylblad.

detta kan vara fallet när du har månatliga/kvartalsrapporter i en enda arbetsbok och du vill dela upp dessa i en arbetsbok för varje kalkylblad.

eller, om du har avdelnings kloka rapporter och du vill dela upp dessa i enskilda arbetsböcker så att du kan skicka dessa enskilda arbetsböcker till avdelningscheferna.

här är koden som skapar en arbetsbok för varje kalkylblad, ger den samma namn som i kalkylbladet och sparar den i den angivna mappen.

Sub CreateWorkbookforWorksheets()Dim ws As WorksheetDim wb As WorkbookFor Each ws In ThisWorkbook.WorksheetsSet wb = Workbooks.Addws.Copy Before:=wb.Sheets(1)Application.DisplayAlerts = Falsewb.Sheets(2).DeleteApplication.DisplayAlerts = Truewb.SaveAs "C:\Users\sumit\Desktop\Test\" & ws.Name & ".xlsx"wb.CloseNext wsEnd Sub

i ovanstående kod har vi använt två variabler ” ws ” och ”wb”.

koden går igenom varje kalkylblad (med för varje nästa slinga) och skapar en arbetsbok för den. Den använder också kopieringsmetoden för kalkylbladsobjektet för att skapa en kopia av kalkylbladet i den nya arbetsboken.

Observera att jag har använt SET-satsen för att tilldela WB-variabeln till en ny arbetsbok som skapas av koden.

Du kan använda den här tekniken för att tilldela ett arbetsboksobjekt till en variabel. Detta behandlas i nästa avsnitt.

tilldela Arbetsboksobjekt till en variabel

i VBA kan du tilldela ett objekt till en variabel och sedan använda variabeln för att referera till det objektet.

till exempel, i nedanstående kod använder jag VBA för att lägga till en ny arbetsbok och sedan tilldela den arbetsboken till variabeln wb. För att göra detta måste jag använda SET-uttalandet.

När jag har tilldelat arbetsboken till variabeln görs alla egenskaper i arbetsboken också tillgängliga för variabeln.

Sub AssigntoVariable()Dim wb As WorkbookSet wb = Workbooks.Addwb.SaveAs Filename:="C:\Users\sumit\Desktop\Examples.xlsx"End Sub

Observera att det första steget i koden är att deklarera ” wb ” som en arbetsbokstyp variabel. Detta berättar för VBA att den här variabeln kan hålla arbetsboksobjektet.

nästa uttalande använder SET för att tilldela variabeln till den nya arbetsboken som vi lägger till. När denna uppgift är klar kan vi använda WB-variabeln för att spara arbetsboken (eller göra något annat med den).

Looping genom öppna arbetsböcker

Vi har redan sett några exempel koder ovan som används looping i koden.

i det här avsnittet kommer jag att förklara olika sätt att slinga genom öppna arbetsböcker med VBA.

anta att du vill spara och stänga alla öppna arbetsböcker, förutom den med koden i den, kan du använda koden nedan:

Sub CloseandSaveWorkbooks()Dim wb As WorkbookFor Each wb In WorkbooksIf wb.Name <> ThisWorkbook.Name Thenwb.Close SaveChanges:=TrueEnd IfNext wbEnd Sub

ovanstående kod använder för varje slinga för att gå igenom varje arbetsbok i arbetsböckerna. För att göra detta måste vi först deklarera ’wb’ som variabeln arbetsbokstyp.

i varje loopcykel analyseras varje arbetsboksnamn och om det inte matchar namnet på arbetsboken som har koden stängs det efter att innehållet har sparats.

detsamma kan också uppnås med en annan slinga som visas nedan:

Sub CloseWorkbooks()Dim WbCount As IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).Close SaveChanges:=TrueEnd IfNext iEnd Sub

ovanstående kod använder för nästa slinga för att stänga alla arbetsböcker utom den som har koden i den. I det här fallet behöver vi inte deklarera en arbetsboksvariabel, utan istället måste vi räkna det totala antalet öppna arbetsböcker. När vi har räkningen använder vi för nästa slinga för att gå igenom varje arbetsbok. Vi använder också indexnumret för att hänvisa till arbetsböckerna i det här fallet.

Observera att i ovanstående kod slingrar vi från WbCount till 1 med steg -1. Detta behövs som med varje slinga, arbetsboken stängs och antalet arbetsböcker minskar med 1.

fel när du arbetar med Arbetsboksobjektet (Run-time error ’9’)

ett av de vanligaste felen du kan stöta på när du arbetar med arbetsböcker är – Run-time Error ’9’ – Subscript out of range.

generellt är VBA-fel inte särskilt informativa och lämnar det ofta till dig att ta reda på vad som gick fel.

här är några av de möjliga orsakerna som kan leda till detta fel:

• arbetsboken som du försöker komma åt finns inte. Om jag till exempel försöker komma åt den femte arbetsboken med arbetsböcker(5), och det bara finns 4 arbetsböcker öppna, Får jag det här felet.
• Om du använder fel namn för att hänvisa till arbetsboken. Till exempel om ditt arbetsboksnamn är exempel.xlsx och du använder exempel.xlsx. då kommer det att visa dig detta fel.
• Om du inte har sparat en arbetsbok och du använder tillägget får du det här felet. Om arbetsbokens namn till exempel är Book1 och du använder namnet Book1.xlsx utan att spara det får du det här felet.
• arbetsboken du försöker komma åt är stängd.

hämta en lista över alla öppna arbetsböcker

om du vill få en lista över alla öppna arbetsböcker i den aktuella arbetsboken (arbetsboken där du kör koden) kan du använda koden nedan:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).NameNext iEnd Sub

ovanstående kod lägger till ett nytt kalkylblad och listar sedan namnet på alla öppna arbetsböcker.

Om du också vill få deras filväg kan du använda koden nedan:

Sub GetWorkbookNames()Dim wbcount As Integerwbcount = Workbooks.CountThisWorkbook.Worksheets.AddActiveSheet.Range("A1").ActivateFor i = 1 To wbcountRange("A1").Offset(i - 1, 0).Value = Workbooks(i).Path & "\" & Workbooks(i).NameNext iEnd Sub

öppna den angivna arbetsboken genom att dubbelklicka på cellen

Om du har en lista med filvägar för Excel-arbetsböcker kan du använda koden nedan för att helt enkelt dubbelklicka på cellen med filvägen och den öppnar den arbetsboken.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)Workbooks.Open Target.ValueEnd Sub

denna kod placeras i ThisWorkbook-kodfönstret.

För att göra detta:

• dubbelklicka på ThisWorkbook-objektet i projektutforskaren. Observera att ThisWorkbook-objektet ska finnas i arbetsboken där du vill ha den här funktionen.
• kopiera och klistra in ovanstående kod.

Nu, om du har den exakta sökvägen för de filer som du vill öppna, kan du göra det genom att helt enkelt dubbelklicka på filvägen och VBA skulle omedelbart öppna den arbetsboken.

Var ska man sätta VBA-koden

undrar var VBA-koden går i din Excel-arbetsbok?

Excel har en VBA-backend som heter VBA editor. Du måste kopiera och klistra in koden i fönstret VB Editor module code.

här är stegen för att göra detta:

1. gå till fliken Utvecklare.
2. Klicka på alternativet Visual Basic. Detta öppnar VB-redigeraren i backend.
3. i fönstret Project Explorer i Vb-redigeraren högerklickar du på något objekt för arbetsboken där du vill infoga koden. Om du inte ser Projektutforskaren gå till fliken Visa och klicka på Projektutforskaren.
4. gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
5. kopiera och klistra in koden i modulfönstret.

Du kanske också gillar följande Excel VBA-handledning:

• hur man spelar in ett makro i Excel.
• skapa en användardefinierad funktion i Excel.
• hur man skapar och använder tillägg i Excel.
• så här återställer du makron genom att placera den i den personliga Makroarbetsboken.
• hämta listan över filnamn från en mapp i Excel (med och utan VBA).
• hur man använder Excel VBA InStr funktion (med praktiska exempel).
• så här sorterar du Data i Excel med VBA (en steg-för-steg-Guide).