Använda arbetsbokobjekt i Excel VBA (Öppna, Stäng, Spara, Ställ in)

I denna handledning kommer jag att täcka hur man arbetar med arbetsböcker i Excel med VBA.

I Excel är en "Workbook" ett objekt som är en del av "Workbooks" -samlingen. I en arbetsbok har du olika objekt som kalkylblad, diagramark, celler och intervall, diagramobjekt, former etc.

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

Så låt oss börja.

Alla koder jag nämner i den här självstudien måste placeras i Visual Basic Editor. Gå till avsnittet "Var ska du placera VBA -koden" för att veta hur det fungerar.

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

Hänvisning till en arbetsbok med VBA

Det finns olika sätt att hänvisa till ett arbetsbokobjekt i VBA. Metoden du väljer beror på vad du vill få gjort. 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 kan du använda koden nedan:

Sub ActivateWorkbook () Workbooks ("Exempel.xlsx"). Aktivera End 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 markera 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 ("Exempel.xlsx"). Arbetsblad ("Sheet1"). Aktivera Range ("A1"). Välj End Sub

Ovanstående kod aktiverar först Sheet1 i arbetsboken Exemplar. Xlsx och väljer sedan cell A1 i arket.

Du kommer ofta att se 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/intervallet i samma arbetsbok som har koden i den och också är den aktiva arbetsboken. Men i vissa fall måste du 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) .Name MsgBox Workbooks (2) .Name MsgBox Workbooks (3) .Name End Sub Sub

Ovanstående kod använder MsgBox - som är en funktion som visar en meddelanderuta med den angivna texten/värdet (vilket är arbetsbokens namn i det här fallet).

Ett av problemen 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 för att gå igenom 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 bläddra 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.

Koden nedan skulle gå igenom alla öppna arbetsböcker och stänga alla utom arbetsboken som har denna VBA -kod.

Sub CloseWorkbooks () Dim WbCount As Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name Then Workbooks (i) .Close End If Next i End 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 det för den arbetsbok där koden körs.

Om det inte är en matchning stänger den arbetsboken och går vidare 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 loop, antalet öppna arbetsböcker minskar.

Denna arbetsbok behandlas i detalj i det senare avsnittet.

Använda ActiveWorkbook

ActiveWorkbook, som namnet antyder, refererar till den arbetsbok som är aktiv.

Koden nedan visar namnet på den aktiva arbetsboken.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Avsluta Sub

När du använder VBA för att aktivera en annan arbetsbok kommer 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å den arbetsbok som har koden och sedan namnet på Exempel. Xlsx (som aktiveras av koden).

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Workbooks ("Exempel.xlsx"). Aktivera MsgBox ActiveWorkbook.Name Avsluta sub

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

Använda denna arbetsbok

ThisWorkbook refererar till arbetsboken där koden körs.

Varje arbetsbok skulle ha ett ThisWorkbook -objekt som en del av den (synlig i Project Explorer).

"ThisWorkbook" kan lagra vanliga makron (liknande dem som vi lägger till moduler) samt händelseprocedurer. Ett händelseförfarande är något som utlöses baserat på en händelse - till exempel att dubbelklicka på en cell, eller spara en arbetsbok eller aktivera ett kalkylblad.

Alla händelseförfaranden som du sparar i denna 'ThisWorkbook' skulle vara tillgängliga i hela arbetsboken, jämfört med händelser på arknivå som endast är begränsade till de specifika arken.

Om du till exempel dubbelklickar på ThisWorkbook-objektet i Project Explorer och kopierar och klistrar in koden nedan, kommer den att visa 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.Address End Sub

Medan ThisWorkbooks huvudroll är att lagra händelseförfarande, kan du också använda den för att hänvisa till arbetsboken där koden körs.

Koden nedan skulle returnera namnet på den arbetsbok där koden körs.

Sub ThisWorkbookName () MsgBox ThisWorkbook.Name Avsluta Sub

Fördelen med att använda ThisWorkbook (över ActiveWorkbook) är att den skulle referera 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 arbetsbokobjekt

Följande kod skapar en ny arbetsbok.

Sub CreateNewWorkbook () Workbooks.Add End Sub

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

Följande kod kommer att lägga till en ny arbetsbok och sedan visa dig namnet på den arbetsboken (vilket skulle vara standardnamnet för bok1 -typ).

Sub CreateNewWorkbook () Workbooks.Add MsgBox ActiveWorkbook.Name End Sub

Öppna en arbetsbok med VBA

Du kan använda VBA för att öppna en specifik arbetsbok när du känner till arbetsvägens filväg.

Koden nedan öppnar arbetsboken - Exempel. Xlsx som finns i mappen Dokument på mitt system.

Sub OpenWorkbook () Workbooks.Open ("C: \ Users \ sumit \ Documents \ Example.xlsx") Avsluta 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 ("Exempel.xlsx") Slut Sub

Om arbetsboken som du försöker öppna inte finns ser du ett fel.

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

Koden nedan kontrollerar filplatsen och om den inte finns kommer det att visa ett anpassat meddelande (inte felmeddelandet):

Sub OpenWorkbook () If Dir ("C: \ Users \ sumit \ Documents \ Example.xlsx") "" Then Workbooks.Open ("C: \ Users \ sumit \ Documents \ Example.xlsx") Else MsgBox "Filen gör inte finns inte "End If End Sub

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

Sub OpenWorkbook () Vid fel Återuppta Nästa dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub

Ovanstående kod öppnar dialogrutan Öppna. När du väljer en fil som du vill öppna tilldelas filvägen till FilePath -variabeln. Workbooks.Open använder sedan sökvägen för att öppna filen.

Om användaren inte öppnar en fil och klickar på knappen Avbryt blir FilePath False. För att undvika att få ett fel i det här fallet har vi använt uttalandet "Vid felåterupptagning nästa".

Relaterad: Lär dig allt om felhantering i Excel VBA

Sparar en arbetsbok

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

Sub SaveWorkbook () ActiveWorkbook.Save End Sub

Denna kod fungerar för de arbetsböcker som redan har sparats tidigare. Eftersom arbetsboken innehåller makrot ovan, om du inte har sparat den som en .xlsm -fil (eller .xls), förlorar du makrot när du öppnar det nästa.

Om du sparar arbetsboken för första gången visar den en uppmaning enligt nedan:

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

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

Sub SaveWorkbook () ActiveWorkbook.SaveAs Filnamn: = "Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Om du vill att filen ska sparas på en specifik plats måste du nämna det i filnamnvärdet. Koden nedan sparar filen på mitt skrivbord.

Sub SaveWorkbook () ActiveWorkbook.SaveAs Filnamn: = "C: \ Users \ sumit \ Desktop \ Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Om du vill att användaren ska få möjlighet att välja plats för att spara filen kan du använda 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 String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filnamn: = FilePath & ".xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

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

Sparar 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 som arbetsbok för varje wb i arbetsböcker wb.Save nästa wb End Sub

Ovanstående sparar alla arbetsböcker, inklusive de som aldrig har sparats. Arbetsböckerna som inte har sparats tidigare 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 som arbetsbok för varje wb i arbetsböcker Om wb.Path "" Sedan wb.Save End If Next wb End 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 som arbetsbok för varje wb i arbetsböcker Om wb.Name ThisWorkbook.Name Sedan wb.Close SaveChanges: = True End If Next wb End Sub

Koden ovan stänger alla arbetsböcker (förutom arbetsboken som har koden - ThisWorkbook). Om det finns ändringar i dessa arbetsböcker, skulle ändringarna sparas. 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 arbetet 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 Filnamn: = "C: \ Users \ sumit \ Desktop \ BackupCopy.xlsm" Avsluta sub

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

Även om det här fungerar bra skulle jag känna mig mer bekväm om jag hade sparat olika kopior 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 verket innan jag gjorde misstaget.

För att hantera sådana situationer kan du använda koden nedan som sparar en ny kopia av verket varje gång du sparar det. Och det lägger också till ett datum och en tidsstämpel som en del av arbetsbokens namn. Detta kan hjälpa dig att spåra alla 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 Filname: = "C: \ Users \ sumit \ Desktop \ BackupCopy" & Format (Now (), "dd-mm-åå-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 en datum-/tidsstämpel till 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ånads-/kvartalsrapporter i en enda arbetsbok och du vill dela upp dem i en arbetsbok för varje kalkylblad.

Eller om du har avdelningsvisa rapporter och du vill dela upp dem i enskilda arbetsböcker så att du kan skicka dessa individuella arbetsböcker till avdelningscheferna.

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

Sub CreateWorkbookforWorksheets () Dim ws As Worksheet Dim wb As Workbook För varje ws i ThisWorkbook.Worksheets Set wb = Workbooks.Add ws.Copy Before: = wb.Sheets (1) Application.DisplayAlerts = False wb.Sheets (2) .Delete Application.DisplayAlerts = True wb.SaveAs "C: \ Users \ sumit \ Desktop \ Test \" & ws.Name & ".xlsx" wb.Close Next ws End Sub

I koden ovan har vi använt två variabler 'ws' och 'wb'.

Koden går igenom varje kalkylblad (med hjälp av For Each Next loop) och skapar en arbetsbok för den. Den använder också kopieringsmetoden för kalkylbladobjektet för att skapa en kopia av kalkylbladet i den nya arbetsboken.

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

Du kan använda denna teknik för att tilldela en arbetsbokobjekt till en variabel. Detta behandlas i nästa avsnitt.

Tilldela arbetsbokobjekt till en variabel

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

Till exempel, i koden nedan använder jag VBA för att lägga till en ny arbetsbok och tilldela sedan arbetsboken till variabeln wb. För att göra detta måste jag använda SET -satsen.

När jag har tilldelat variabeln arbetsboken blir alla arbetsbokens egenskaper också tillgängliga för variabeln.

Sub AssigntoVariable () Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filnamn: = "C: \ Users \ sumit \ Desktop \ Exempel.xlsx" Slut Sub

Observera att det första steget i koden är att deklarera 'wb' som en variabel för arbetsbokstyp. Detta berättar för VBA att denna variabel kan hålla arbetsbokobjektet.

Nästa sats använder SET för att tilldela variabeln till den nya arbetsboken som vi lägger till. När den här uppgiften är klar kan vi använda variabeln wb för att spara arbetsboken (eller göra något annat med den).

Looping genom öppna arbetsböcker

Vi har redan sett några exempelkoder ovan som använde looping i koden.

I det här avsnittet kommer jag att förklara olika sätt att gå igenom öppna arbetsböcker med VBA.

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

Sub CloseandSaveWorkbooks () Dim wb som arbetsbok för varje wb i arbetsböcker Om wb.Name ThisWorkbook.Name Sedan wb.Close SaveChanges: = True End If Next wb End Sub

Koden ovan använder slingan För varje för att gå igenom varje arbetsbok i arbetsboksamlingen. För att göra detta måste vi först deklarera 'wb' som variabel för arbetsbokstyp.

I varje loopcykel analyseras varje arbetsboknamn 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 Integer WbCount = Workbooks.Count For i = WbCount To 1 Step -1 If Workbooks (i) .Name ThisWorkbook.Name Then Workbooks (i) .Close SaveChanges: = True End If Next i End Sub

Ovanstående kod använder For Next -slingan 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 arbetsbokvariabel, men istället måste vi räkna det totala antalet öppna arbetsböcker. När vi har räkningen använder vi For Next -slingan för att gå igenom varje arbetsbok. Vi använder också indexnumret för att referera till arbetsböckerna i det här fallet.

Observera att i koden ovan, loopar vi från WbCount till 1 med steg -1. Detta behövs som med varje loop, arbetsboken stängs och antalet arbetsböcker minskar med 1.

Fel vid arbete med arbetsbokobjektet (körtidsfel '9')

Ett av de vanligaste felen du kan stöta på när du arbetar med arbetsböcker är - Run -time Error ‘9’ - Prenumeration utanför intervallet.

I allmänhet är VBA -fel inte särskilt informativa och lämnar det ofta åt 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. Till exempel, om jag försöker komma åt den femte arbetsboken med hjälp av arbetsböcker (5), och det bara är fyra arbetsböcker öppna, får jag det här felet.
• Om du använder ett fel namn för att referera till arbetsboken. Till exempel om ditt arbetsboknamn är Exempel.xlsx och du använder Exempel.xlsx. då visar det dig detta fel.
• Om du inte har sparat en arbetsbok och använder tillägget får du det här felet. Om ditt arbetsboknamn 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.

Få 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 Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Aktivera För i = 1 Till wbcount Range ("A1"). Offset (i - 1, 0). Value = Workbooks (i) .Name Next i End Sub

Koden ovan lägger till ett nytt kalkylblad och listar sedan namnet på alla öppna arbetsböcker.

Om du också vill få deras sökväg kan du använda koden nedan:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Aktivera För i = 1 Till wbcount Range ("A1"). Offset (i - 1, 0). Value = Workbooks (i) .Path & "\" & Workbooks (i) .Name Next i End Sub

Öppna den specificerade 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 så öppnas den arbetsboken.

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

Denna kod skulle placeras i ThisWorkbook -kodfönstret.

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 till filerna som du vill öppna, kan du göra det genom att helt enkelt dubbelklicka på filens sökväg och VBA skulle omedelbart öppna den arbetsboken.

Var ska man lägga VBA -koden

Undrar du vart VBA -koden går i din Excel -arbetsbok?

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

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 Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden. Om du inte ser Project Explorer går du till fliken View och klickar på Project Explorer.
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 -självstudier:

• 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 det i den personliga makro -arbetsboken.
• Få 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).