✅ Použití objektu sešitu v aplikaci Excel VBA (otevřít, zavřít, uložit, nastavit)

Obsah

Odkazování na sešit pomocí VBA
Vytvoření nového objektu sešitu
Otevřete sešit pomocí VBA
Uložení sešitu
Uložení všech otevřených sešitů
Ukládání a zavírání všech sešitů
Uložit kopii sešitu (s časovým razítkem)
Vytvořte nový sešit pro každý list
Přiřadit objekt sešitu proměnné
Smyčka prostřednictvím otevřených sešitů
Chyba při práci s objektem sešitu (chyba při spuštění „9“)
Získejte seznam všech otevřených sešitů
Otevřete zadaný sešit poklepáním na buňku
Kam vložit kód VBA

V tomto tutoriálu se budu zabývat tím, jak pracovat se sešity v Excelu pomocí VBA.

V Excelu je „Sešit“ objekt, který je součástí kolekce „Sešity“. V sešitu máte různé objekty, jako jsou listy, listy grafů, buňky a rozsahy, objekty grafu, tvary atd.

S VBA můžete s objektem sešitu dělat spoustu věcí - například otevřít konkrétní sešit, uložit a zavřít sešity, vytvořit nové sešity, změnit vlastnosti sešitu atd.

Začněme tedy.

Všechny kódy, které zmiňuji v tomto kurzu, je třeba umístit do editoru jazyka Visual Basic. Chcete -li zjistit, jak funguje, přejděte do části „Kam umístit kód VBA“.

Pokud máte zájem naučit se VBA jednoduchým způsobem, podívejte se na můj Online školení Excel VBA.

Odkazování na sešit pomocí VBA

Existují různé způsoby, jak odkazovat na objekt sešitu ve VBA. Metoda, kterou zvolíte, bude záviset na tom, co chcete udělat. V této části se budu zabývat různými způsoby odkazování na sešit spolu s některými ukázkovými kódy.

Používání jmen sešitů

Pokud máte přesný název sešitu, na který chcete odkazovat, můžete název použít v kódu.

Začněme jednoduchým příkladem.

Pokud máte otevřené dva sešity a chcete sešit aktivovat pod názvem - examples.xlsx, můžete použít níže uvedený kód:

Sub ActivateWorkbook () Sešity ("examples.xlsx"). Aktivujte End Sub

Pokud byl soubor uložen, musíte použít název souboru společně s příponou. Pokud nebyl uložen, můžete název použít bez přípony souboru.

Pokud si nejste jisti, jaké jméno použít, požádejte o pomoc Průzkumníka projektů.

Pokud chcete aktivovat sešit a vybrat konkrétní buňku v listu v tomto sešitu, musíte zadat celou adresu buňky (včetně sešitu a názvu listu).

Sub ActivateWorkbook () Sešity ("Příklady.xlsx"). Pracovní listy ("List1"). Aktivujte rozsah ("A1"). Vyberte Konec Sub

Výše uvedený kód nejprve aktivuje List1 v sešitu Example.xlsx a poté vybere buňku A1 v listu.

Často uvidíte kód, kde je proveden odkaz na list nebo buňku/rozsah bez odkazu na sešit. K tomu dochází, když odkazujete na list/rozsahy ve stejném sešitu, který obsahuje kód a je také aktivním sešitem. V některých případech však musíte zadat sešit, abyste se ujistili, že kód funguje (více o tom v sekci ThisWorkbook).

Používání indexových čísel

Sešity můžete také odkazovat na základě jejich indexového čísla.

Pokud máte například otevřené tři sešity, následující kód by vám ukázal názvy tří sešitů v okně se zprávou (po jednom).

Sub WorkbookName () MsgBox Workbooks (1) .Name MsgBox Workbooks (2) .Name MsgBox Workbooks (3) .Name End Sub

Výše uvedený kód používá MsgBox - což je funkce, která zobrazuje okno se zadaným textem/hodnotou (což je v tomto případě název sešitu).

Jeden z problémů, které mám často při používání indexových čísel se sešity, je ten, že nikdy nevíte, který je první sešit a který druhý a tak dále. Chcete -li si být jisti, budete muset spustit kód, jak je uvedeno výše, nebo něco podobného, abyste mohli procházet otevřené sešity a znát jejich indexové číslo.

Excel zachází se sešitem otevřeným jako první tak, aby číslo indexu bylo 1, a další jako 2 atd.

I přes tuto nevýhodu může používání čísel indexů přijít vhod. Pokud například chcete procházet všechny otevřené sešity a uložit všechny, můžete použít čísla rejstříku. V tomto případě, protože chcete, aby se to stalo všem sešitům, vás nezajímají jejich jednotlivá čísla indexů.

Níže uvedený kód by procházel všemi otevřenými sešity a zavřel všechny kromě sešitu, který má tento kód VBA.

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

Výše uvedený kód počítá počet otevřených sešitů a poté prochází všechny sešity pomocí smyčky Pro každý.

Pomocí podmínky IF kontroluje, zda je název sešitu stejný jako název sešitu, kde je kód spuštěn.

Pokud to není shoda, zavře sešit a přejde k dalšímu.

Všimněte si, že jsme smyčku spustili z WbCount na 1 s krokem -1. To se provádí jako u každé smyčky, počet otevřených sešitů klesá.

Tato pracovní kniha je podrobně popsána v pozdější části.

Používání ActiveWorkbook

ActiveWorkbook, jak název napovídá, odkazuje na aktivní sešit.

Níže uvedený kód by vám ukázal název aktivního sešitu.

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name End Sub

Když použijete VBA k aktivaci jiného sešitu, část ActiveWorkbook ve VBA poté začne odkazovat na aktivovaný sešit.

Zde je příklad.

Pokud máte aktivní sešit a vložíte do něj následující kód a spustíte jej, zobrazí se nejprve název sešitu, který má kód, a poté název Example.xlsx (který se aktivuje kódem).

Sub ActiveWorkbookName () MsgBox ActiveWorkbook.Name Sešity ("examples.xlsx"). Aktivujte MsgBox ActiveWorkbook.Name Konec Sub

Všimněte si toho, že když vytvoříte nový sešit pomocí VBA, tento nově vytvořený sešit se automaticky stane aktivním sešitem.

Používání této pracovní knihy

ThisWorkbook odkazuje na sešit, kde se kód spouští.

Každý sešit by měl jako součást objekt ThisWorkbook (viditelný v Průzkumníku projektů).

„ThisWorkbook“ může ukládat běžná makra (podobná těm, která přidáváme do modulů) i procedury událostí. Procedura události je něco, co se spouští na základě události - například dvojitým kliknutím na buňku nebo uložením sešitu nebo aktivací listu.

Jakýkoli postup události, který uložíte do této „ThisWorkbook“, bude k dispozici v celém sešitu ve srovnání s událostmi na úrovni listu, které jsou omezeny pouze na konkrétní listy.

Pokud například dvakrát kliknete na objekt ThisWorkbook v Průzkumníku projektů a zkopírujete do něj níže uvedený kód, zobrazí se adresa buňky vždy, když dvakrát kliknete na některou z buněk v celém sešitu.

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

Zatímco hlavní rolí ThisWorkbook je ukládat procedury událostí, můžete ji také použít k odkazu na sešit, kde se kód spouští.

Níže uvedený kód by vrátil název sešitu, ve kterém se kód spouští.

Sub ThisWorkbookName () MsgBox ThisWorkbook.Name End Sub

Výhodou používání ThisWorkbook (přes ActiveWorkbook) je, že ve všech případech bude odkazovat na stejný sešit (ten, který obsahuje kód). Pokud tedy použijete kód VBA k přidání nového sešitu, ActiveWorkbook by se změnil, ale ThisWorkbook by stále odkazoval na ten, který má kód.

Vytvoření nového objektu sešitu

Následující kód vytvoří nový sešit.

Dílčí sešity CreateNewWorkbook (). Přidat End Sub

Když přidáte nový sešit, stane se z něj aktivní sešit.

Následující kód přidá nový sešit a poté vám ukáže název tohoto sešitu (což by byl výchozí název typu Book1).

Sub CreateNewWorkbook () sešity. Přidat MsgBox ActiveWorkbook.Name Konec Sub

Otevřete sešit pomocí VBA

Pokud znáte cestu k sešitu, můžete použít VBA k otevření konkrétního sešitu.

Níže uvedený kód otevře sešit - examples.xlsx, který je v mém systému ve složce Dokumenty.

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

V případě, že soubor existuje ve výchozí složce, což je složka, kam VBA standardně ukládá nové soubory, pak stačí zadat název sešitu - bez celé cesty.

Dílčí sešity OpenWorkbook (). Otevřít („Příklady.xlsx“) Konec Sub

Pokud sešit, který se pokoušíte otevřít, neexistuje, zobrazí se chyba.

Chcete -li se této chybě vyhnout, můžete do kódu přidat několik řádků, abyste nejprve zkontrolovali, zda soubor existuje nebo ne, a pokud existuje, zkuste jej otevřít.

Níže uvedený kód zkontroluje umístění souboru a pokud neexistuje, zobrazí vlastní zprávu (nikoli chybovou zprávu):

Sub OpenWorkbook () If Dir ("C: \ Users \ sumit \ Documents \ examples.xlsx") "" Then Workbooks.Open ("C: \ Users \ sumit \ Documents \ examples.xlsx") Else MsgBox "Soubor ne neexistuje "End If End Sub

Můžete také použít dialogové okno Otevřít k výběru souboru, který chcete otevřít.

Sub OpenWorkbook () On Error Resume Next Dim FilePath As String FilePath = Application.GetOpenFilename Workbooks.Open (FilePath) End Sub

Výše uvedený kód otevře dialogové okno Otevřít. Když vyberete soubor, který chcete otevřít, přiřadí cestu k souboru proměnné FilePath. Workbooks.Open pak použije cestu k souboru k otevření souboru.

V případě, že uživatel neotevře soubor a klikne na tlačítko Storno, stane se FilePath False. Abychom se v tomto případě vyhnuli chybě, použili jsme prohlášení „Při chybě pokračovat dále“.

Příbuzný: Zjistěte vše o zpracování chyb v aplikaci Excel VBA

Uložení sešitu

Chcete -li uložit aktivní sešit, použijte níže uvedený kód:

Sub SaveWorkbook () ActiveWorkbook.Save End Sub

Tento kód funguje pro sešity, které již byly uloženy dříve. Protože sešit obsahuje výše uvedené makro, pokud nebylo uloženo jako soubor .xlsm (nebo .xls), při dalším otevření makro ztratíte.

Pokud sešit ukládáte poprvé, zobrazí se výzva, jak je uvedeno níže:

Při prvním ukládání je lepší použít možnost „Saveas“.

Níže uvedený kód by uložil aktivní sešit jako soubor .xlsm do výchozího umístění (což je složka dokumentu v mém systému).

Sub SaveWorkbook () ActiveWorkbook.SaveAs Název souboru: = "Test.xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Pokud chcete, aby byl soubor uložen na určitém místě, musíte to zmínit v hodnotě Název souboru. Níže uvedený kód uloží soubor na moji plochu.

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

Pokud chcete, aby měl uživatel možnost vybrat umístění pro uložení souboru, můžete použít volání dialogového okna Saveas. Níže uvedený kód ukazuje dialogové okno Saveas a umožňuje uživateli vybrat umístění, kam má být soubor uložen.

Sub SaveWorkbook () Dim FilePath As String FilePath = Application.GetSaveAsFilename ActiveWorkbook.SaveAs Filename: = FilePath & ".xlsm", FileFormat: = xlOpenXMLWorkbookMacroEnabled End Sub

Všimněte si, že namísto použití FileFormat: = xlOpenXMLWorkbookMacroEnabled můžete také použít FileFormat: = 52, kde 52 je kód xlOpenXMLWorkbookMacroEnabled.

Uložení všech otevřených sešitů

Pokud máte otevřených více než jeden sešit a chcete uložit všechny sešity, můžete použít níže uvedený kód:

Sub SaveAllWorkbooks () Dim wb as Workbook for each wb in Workbooks wb.Save Next wb End Sub

Výše uvedené uloží všechny sešity, včetně těch, které nikdy nebyly uloženy. Sešity, které dříve nebyly uloženy, se uloží do výchozího umístění.

Pokud chcete uložit pouze ty sešity, které byly dříve uloženy, můžete použít následující kód:

Sub SaveAllWorkbooks () Dim wb as Workbook for each wb in Workbooks If wb.Path "" Then wb.Save End If Next wb End Sub

Ukládání a zavírání všech sešitů

Pokud chcete zavřít všechny sešity, kromě sešitu, který obsahuje aktuální kód, můžete použít níže uvedený kód:

Sub CloseandSaveWorkbooks () Dim wb as Workbook for each wb in Workbooks If wb.Name ThisWorkbook.Name Then wb.Close SaveChanges: = True End If Next wb End Sub

Výše uvedený kód by zavřel všechny sešity (kromě sešitu, který má kód - ThisWorkbook). V případě, že v těchto sešitech dojde ke změnám, budou změny uloženy. V případě, že existuje sešit, který nebyl nikdy uložen, zobrazí se dialogové okno Uložit jako.

Uložit kopii sešitu (s časovým razítkem)

Když pracuji se složitými daty a řídicím panelem v sešitech aplikace Excel, často vytvářím různé verze svých sešitů. To je užitečné v případě, že se s mým aktuálním sešitem něco pokazí. Nechal bych si alespoň uložit jeho kopii pod jiným názvem (a o práci, kterou jsem udělal, bych přišel až po vytvoření kopie).

Zde je kód VBA, který vytvoří kopii vašeho sešitu a uloží jej na určené místo.

Sub CreateaCopyofWorkbook () ThisWorkbook.SaveCopyAs Název souboru: = "C: \ Users \ sumit \ Desktop \ BackupCopy.xlsm" End Sub

Výše uvedený kód by uložil kopii sešitu při každém spuštění tohoto makra.

I když to funguje skvěle, cítil bych se pohodlněji, kdybych měl při spuštění tohoto kódu uloženy různé kopie. Důvod, proč je to důležité, je ten, že pokud udělám nechtěnou chybu a spustím toto makro, ušetří to práci s chybami. A já bych neměl přístup k dílu, než jsem udělal chybu.

K řešení takových situací můžete použít níže uvedený kód, který při každém uložení uloží novou kopii díla. A také přidá datum a časové razítko jako součást názvu sešitu. To vám pomůže sledovat jakoukoli chybu, kterou jste udělali, protože nikdy neztratíte žádnou z dříve vytvořených záloh.

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

Výše uvedený kód by vytvořil kopii při každém spuštění tohoto makra a přidal k názvu sešitu razítko data/času.

Vytvořte nový sešit pro každý list

V některých případech můžete mít sešit, který má více listů, a chcete vytvořit sešit pro každý list.

To může být případ, kdy máte měsíční/čtvrtletní zprávy v jednom sešitu a chcete je rozdělit do jednoho sešitu pro každý list.

Nebo pokud máte přehledy jednotlivých oddělení a chcete je rozdělit do jednotlivých sešitů, abyste mohli tyto jednotlivé sešity odeslat vedoucím oddělení.

Zde je kód, který vytvoří sešit pro každý list, přidělí mu stejný název jako název listu a uloží jej do zadané složky.

Dílčí CreateWorkbookforWorksheets () Dim ws As Worksheet Dim wb As Workbook For each ws In 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

Ve výše uvedeném kódu jsme použili dvě proměnné „ws“ a „wb“.

Kód projde každým listem (pomocí cyklu For Every Next) a vytvoří pro něj sešit. Také používá metodu kopírování objektu listu k vytvoření kopie listu v novém sešitu.

Všimněte si toho, že jsem použil příkaz SET k přiřazení proměnné „wb“ jakémukoli novému sešitu, který je vytvořen kódem.

Tuto techniku můžete použít k přiřazení objektu sešitu k proměnné. To je popsáno v další části.

Přiřadit objekt sešitu proměnné

Ve VBA můžete k proměnné přiřadit objekt a poté pomocí proměnné na tento objekt odkazovat.

Například v níže uvedeném kódu používám VBA k přidání nového sešitu a jeho přiřazení k proměnné wb. K tomu musím použít příkaz SET.

Jakmile přiřadím sešit k proměnné, všechny vlastnosti sešitu budou k dispozici také proměnné.

Sub AssigntoVariable () Dim wb As Workbook Set wb = Workbooks.Add wb.SaveAs Filename: = "C: \ Users \ sumit \ Desktop \ examples.xlsx" End Sub

Všimněte si toho, že prvním krokem v kódu je deklarace „wb“ jako proměnné typu sešitu. To říká VBA, že tato proměnná může obsahovat objekt sešitu.

Následující příkaz používá SET k přiřazení proměnné k novému sešitu, který přidáváme. Jakmile je toto přiřazení hotové, můžeme použít proměnnou wb k uložení sešitu (nebo s ním dělat cokoli jiného).

Smyčka prostřednictvím otevřených sešitů

Už jsme viděli několik příkladů kódů výše, které používají smyčku v kódu.

V této části vysvětlím různé způsoby, jak procházet otevřené sešity pomocí VBA.

Předpokládejme, že chcete uložit a zavřít všechny otevřené sešity, kromě toho, který obsahuje kód, pak můžete použít níže uvedený kód:

Sub CloseandSaveWorkbooks () Dim wb as Workbook for each wb in Workbooks If wb.Name ThisWorkbook.Name Then wb.Close SaveChanges: = True End If Next wb End Sub

Výše uvedený kód používá smyčku For Každý k procházení každého sešitu v kolekci sešity. K tomu musíme nejprve deklarovat „wb“ jako proměnnou typu sešitu.

V každém cyklu smyčky je analyzován každý název sešitu a pokud se neshoduje s názvem sešitu, který má kód, je po uložení jeho obsahu uzavřen.

Totéž lze také dosáhnout s jinou smyčkou, jak je uvedeno níže:

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

Výše uvedený kód používá smyčku For Next k zavření všech sešitů kromě toho, který obsahuje kód. V tomto případě nepotřebujeme deklarovat proměnnou sešitu, ale místo toho musíme spočítat celkový počet otevřených sešitů. Když máme počet, použijeme smyčku For Next k procházení každého sešitu. V tomto případě také odkazujeme na sešity pomocí indexového čísla.

Všimněte si, že ve výše uvedeném kódu jsme smyčku z WbCount na 1 s krokem -1. Je to nutné, protože s každou smyčkou se sešit zavře a počet sešitů se sníží o 1.

Chyba při práci s objektem sešitu (chyba při spuštění „9“)

Jednou z nejčastějších chyb, se kterými se můžete setkat při práci se sešity, je - Chyba při spuštění „9“ - dolní index mimo rozsah.

Chyby VBA obecně nejsou příliš informativní a často nechávají na vás, abyste zjistili, co se stalo.

Zde jsou některé z možných důvodů, které mohou vést k této chybě:

Sešit, ke kterému se pokoušíte získat přístup, neexistuje. Pokud se například pokouším o přístup k pátému sešitu pomocí sešitů (5) a jsou otevřené pouze 4 sešity, zobrazí se tato chyba.
Pokud do sešitu používáte nesprávný název. Pokud je například název vašeho sešitu Example.xlsx a použijete Example.xlsx. pak vám ukáže tuto chybu.
Pokud jste sešit neuložili a používáte rozšíření, zobrazí se tato chyba. Pokud je například název vašeho sešitu Book1 a použijete název Book1.xlsx, aniž byste jej uložili, zobrazí se tato chyba.
Sešit, ke kterému se pokoušíte získat přístup, je zavřený.

Získejte seznam všech otevřených sešitů

Pokud chcete získat seznam všech otevřených sešitů v aktuálním sešitu (sešit, kde kód spouštíte), můžete použít níže uvedený kód:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate for i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Hodnota = Sešity (i). Název Další i Konec Pod

Výše uvedený kód přidá nový list a poté vypíše název všech otevřených sešitů.

Pokud chcete získat také cestu k souboru, můžete použít níže uvedený kód:

Sub GetWorkbookNames () Dim wbcount As Integer wbcount = Workbooks.Count ThisWorkbook.Worksheets.Add ActiveSheet.Range ("A1"). Activate for i = 1 To wbcount Range ("A1"). Offset (i - 1, 0). Hodnota = Sešity (i). Cesta & "\" & Sešity (i). Název Další i Konec Sub

Otevřete zadaný sešit poklepáním na buňku

Pokud máte seznam sešitů pro sešity aplikace Excel, můžete pomocí níže uvedeného kódu jednoduše dvakrát kliknout na buňku s cestou k souboru a tento sešit se otevře.

Soukromý dílčí sešit_SheetBeforeDoubleClick (ByVal Sh jako objekt, ByVal cíl jako rozsah, Zrušit jako booleovské) sešity. Otevřít cíl. Hodnota Konec Sub

Tento kód by byl umístěn do okna kódu ThisWorkbook.

Udělat toto:

Dvakrát klikněte na objekt ThisWorkbook v průzkumníku projektu. Objekt ThisWorkbook by měl být v sešitu, kde chcete tuto funkci.
Zkopírujte a vložte výše uvedený kód.

Pokud máte přesnou cestu k souborům, které chcete otevřít, můžete to udělat jednoduchým dvojitým kliknutím na cestu k souboru a VBA by sešit okamžitě otevřel.

Kam vložit kód VBA

Zajímá vás, kam jde kód VBA do vašeho sešitu v Excelu?

Excel má VBA backend nazvaný VBA editor. Musíte zkopírovat a vložit kód do okna kódu modulu VB Editor.

Zde jsou kroky, jak toho dosáhnout:

Přejděte na kartu Vývojář.
Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud Průzkumníka nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
Zkopírujte a vložte kód do okna modulu.