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

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

s VBA můžete dělat spoustu věcí s objektem sešitu – 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.

tak začněme.

Pokud máte zájem o učení VBA jednoduchým způsobem, podívejte se na mé online školení Excel VBA.

odkazování sešitu pomocí VBA

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

použití názvů sešitů

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

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

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

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

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

Pokud si nejste jisti, jaký název použít, Použijte nápovědu Průzkumníka projektu.

Pokud chcete aktivovat sešitu a vyberte konkrétní buňku v listu v sešitu, musíte dát celou adresu buňky (včetně Sešitu a Listu jméno).

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

výše uvedený kód nejprve aktivuje Sheet1 v příkladech.sešit xlsx a poté vybere buňku A1 v listu.

často uvidíte kód, kde je vytvořen odkaz na pracovní list nebo buňku/rozsah bez odkazu na sešit. K tomu dochází, když odkazujete na pracovní 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).

pomocí indexových čísel

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

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

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

výše uvedený kód používá MsgBox – což je funkce, která zobrazí okno zprávy s zadaný text/hodnota (což je název sešitu v tomto případě).

Jedním z problémů jsem často s pomocí indexu čísla Sešitů je, že nikdy nevíte, který z nich je první sešit a který je druhý, a tak dále. Pro jistotu byste museli spustit kód, jak je uvedeno výše, nebo něco podobného, abyste mohli procházet otevřenými sešity a znát jejich Indexové číslo.

Excel považuje sešit otevřel první indexové číslo jako 1, a další jako 2 a tak dále.

i přes tuto nevýhodu může být použití indexových čísel užitečné. Pokud například chcete procházet všechny otevřené sešity a uložit všechny, můžete použít čísla indexu. V tomto případě, protože chcete, aby se to stalo všem sešitkům, nejste znepokojeni jejich individuálními čísly indexů.

níže uvedený kód by prošel 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 IntegerWbCount = Workbooks.CountFor i = WbCount To 1 Step -1If Workbooks(i).Name <> ThisWorkbook.Name ThenWorkbooks(i).CloseEnd IfNext iEnd Sub

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

používá podmínku IF ke kontrole, zda je název sešitu stejný jako název sešitu, kde je kód spuštěn.

Pokud se nejedná o shodu, uzavře sešit a přesune se na další.

Všimněte si, že jsme spustili smyčku z WbCount na 1 s krokem -1. To se provádí stejně 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žití ActiveWorkbook

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

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

Sub ActiveWorkbookName()MsgBox ActiveWorkbook.NameEnd 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 tohoto.

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

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

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

pomocí ThisWorkbook

ThisWorkbook odkazuje na sešit, kde se kód provádí.

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

‚ThisWorkbook‘ můžete ukládat pravidelné makra (podobné těm, které jsme add-in moduly), stejně jako událostní procedury. Postup události je něco, co se spouští na základě události-například poklepání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.

například, pokud jste double-klikněte na objekt ThisWorkbook v Project Explorer a zkopírovat a vložit níže uvedený kód, zobrazí se adresa buňky, vždy, když jste double-klikněte na libovolné buňky v celém sešitu.

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

Zatímco ThisWorkbook hlavní roli je ukládat proceduru události, můžete také používat to, aby odkazovat na sešit, kde kód je prováděn.

níže uvedený kód vrátí název sešitu, ve kterém je kód spuštěn.

Sub ThisWorkbookName()MsgBox ThisWorkbook.NameEnd Sub

výhodou použití ThisWorkbook (přes ActiveWorkbook) je, že by se odkazují na stejný sešit (ten, který má kód v ní) ve všech případech. Takže pokud 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.

Sub CreateNewWorkbook()Workbooks.AddEnd Sub

Když přidáte nový sešit, stane se aktivním 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()Workbooks.AddMsgBox ActiveWorkbook.NameEnd Sub

Otevřete Sešit pomocí VBA

můžete použít VBA otevřít konkrétní sešit, když víte, cestu k souboru sešitu.

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

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

V případě, že existuje soubor do výchozí složky, což je složka, kde VBA uloží nové soubory ve výchozím nastavení, pak stačí zadat název sešitu – aniž by celou cestu.

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

V případě, že 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.

pod kód by zkontrolovat umístění souboru a pokud neexistuje, zobrazí vlastní zprávu (není chybové hlášení):

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

můžete také použít v dialogovém okně Otevřít vyberte soubor, který chcete otevřít.

Sub OpenWorkbook()On Error Resume NextDim FilePath As StringFilePath = Application.GetOpenFilenameWorkbooks.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. Sešit.Otevřít 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, FilePath se stane False. Abychom se v tomto případě vyhnuli chybě, použili jsme příkaz „On Error Resume Next“.

Ukládání Sešitu

Pro aktivní sešit uložit, použijte kód níže:

Sub SaveWorkbook()ActiveWorkbook.SaveEnd Sub

Tento kód funguje pro sešitů, které již byly uloženy dříve. Také, protože sešit obsahuje výše uvedené makro, pokud nebyl uložen jako .xlsm (nebo .XLS) soubor, ztratíte makro, když jej otevřete další.

Pokud ukládáte sešit pro první čas, to vám ukáže prompt, jak je uvedeno níže:

Při ukládání poprvé, to je lepší použít ‚uložit jako‘ volba.

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

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

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

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

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

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

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

Uložení Všech Otevřených Sešitech

Pokud máte více než jeden sešit a chcete uložit všechny sešity, můžete použít následující kód:

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

výše uvedené uloží všechny sešity, včetně těch, které nebyly nikdy uloženy. Sešity, které nebyly dříve 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íže uvedený kód:

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

uložit a zavřít Všechny Sešity

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

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

výše uvedený kód by zavřít 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ž jsem pracoval s komplexní data a dashboard v aplikaci Excel sešity, často jsem vytvořit různé verze mé sešity. To je užitečné v případě, že se něco pokazí s mým aktuálním sešitem. Měl bych alespoň kopii uloženou s jiným názvem (a ztratil bych práci, kterou jsem udělal po vytvoření kopie).

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

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

výše uvedený kód uloží 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 různé kopie uložené při každém spuštění tohoto kódu. Důvod, proč je to důležité, je, že pokud udělám neúmyslnou chybu a spustím toto makro, ušetří práci s chybami. A já bych neměl přístup k práci, 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ává 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 Filename:="C:\Users\sumit\Desktop\BackupCopy" & Format(Now(), "dd-mm-yy-hh-mm-ss-AMPM") & ".xlsm"End Sub

výše uvedený kód vytvoří kopii při každém spuštění tohoto makra a přidat datum/čas razítko do sešitu jméno.

Vytvořit Nový Sešit pro Každý List

V některých případech může 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 oddělení moudré zprávy a chcete rozdělit tyto do jednotlivých sešitů, takže můžete poslat tyto jednotlivé sešity vedoucí oddělení.

Zde je kód, který vytvoří sešit pro každý list, dát mu stejné jméno jako list, a uložit jej do určené složky.

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

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

kód prochází každým pracovním listem (pomocí pro každou další smyčku) a vytvoří pro něj sešit. Používá také metodu kopírování objektu listu k vytvoření kopie listu v novém sešitu.

Všimněte si, že jsem použil příkaz SET přiřadit ‚wb‘ proměnné pro každý nový sešit je vytvořen kód.

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

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

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

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

po přiřazení sešitu k proměnné jsou všechny vlastnosti sešitu zpřístupněny také proměnné.

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

Všimněte si, že prvním krokem v kódu je deklarovat‘ wb ‚ jako proměnnou 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é novému sešitu, který přidáváme. Po dokončení tohoto přiřazení můžeme pomocí proměnné wb uložit sešit (nebo s ním udělat cokoli jiného).

Looping prostřednictvím otevřených sešitů

již jsme viděli několik příkladů kódů výše, které používaly looping v kódu.

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

Předpokládejme, že chcete uložte a zavřete všechny otevřené sešity, s výjimkou jednoho s kódem, pak můžete použít níže kód:

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

výše uvedený kód používá Pro Každou smyčku projít každý sešit na Sešity kolekce. Chcete-li to provést, 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 neodpovídá názvu sešitu, který má kód, je po uložení jeho obsahu uzavřen.

toho lze také dosáhnout jinou smyčkou, jak je znázorněno níže:

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

výše uvedený kód používá smyčku pro další k uzavření všech sešitů kromě té, která má v sobě kód. V tomto případě nemusíme 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 pro další k procházení každého sešitu. V tomto případě také používáme Indexové číslo k odkazu na sešity.

Všimněte si, že ve výše uvedeném kódu jsme smyčku z WbCount na 1 s krokem -1. To je potřeba jako u každé smyčky, sešit se uzavře a počet sešitů se sníží o 1.

Chyba při Práci se Sešitem Objekt (Run-time error ‚9‘)

Jedním z nejčastějších chyb se můžete setkat při práci se sešity je – Run-time Error ‚9‘ – Index mimo rozsah.

Obecně platí, že chyby VBA nejsou příliš informativní a často nechávají na vás, abyste zjistili, co se pokazilo.

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

• sešit, který jste se snaží přístup neexistuje. Pokud se například pokouším získat přístup k pátému sešitu pomocí sešitů(5) a jsou otevřeny pouze sešity 4, dostanu tuto chybu.
• Pokud používáte nesprávný název pro odkaz na sešit. Například, pokud je název sešitu příklady.xlsx a použít příklad.xlsx. pak vám ukáže tuto chybu.
• Pokud jste sešit neuložili a použijete příponu, zobrazí se tato chyba. Pokud je například název sešitu Book1 a používáte název Book1.xlsx bez uložení, dostanete tuto chybu.
• sešit, ke kterému se pokoušíte přistupovat, je uzavřen.

Získat Seznam Všech Otevřených Sešitech

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

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

výše uvedený kód přidá nový list a pak vypíše jména všech otevřených sešitech.

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

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

Otevřít Zadaný Sešitu poklepáním na Buňku

Pokud máte seznam cest souborů pro sešity aplikace Excel, můžete použít níže uvedený kód, jednoduše double-klikněte na buňku s cestou k souboru a otevře sešit.

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

Tento kód by byl umístěn v okně kódu ThisWorkbook.

Chcete-li to provést:

• Poklepejte na objekt ThisWorkbook v Průzkumníku projektu. Všimněte si, že objekt ThisWorkbook by měl být v sešitu, kde chcete tuto funkci.
• zkopírujte a vložte výše uvedený kód.

Nyní, pokud máte přesnou cestu, soubory, které chcete otevřít, můžete to udělat jednoduše dvojitým kliknutím na soubor cesta a VBA by okamžitě otevřít sešit.

kam umístit kód VBA

zajímá Vás, kde kód VBA jde do sešitu aplikace Excel?

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

zde jsou kroky k tomu:

1. přejděte na kartu Vývojář.
2. klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
3. v podokně Průzkumníka projektu v editoru VB klepněte pravým tlačítkem myši na libovolný objekt sešitu, do kterého chcete kód vložit. Pokud Průzkumník projektu nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektu.
4. přejděte na Vložit a klikněte na modul. Tím se vloží objekt modulu pro váš sešit.
5. zkopírujte a vložte kód do okna modulu.

Může se Vám Líbit Následující aplikace Excel VBA Návody:

• Jak na Záznam Makra v Excelu.
• vytvoření uživatelem definované funkce v aplikaci Excel.
• jak vytvořit a používat doplněk v aplikaci Excel.
• jak obnovit makra umístěním do osobního makra sešitu.
• Získejte seznam názvů souborů ze složky v aplikaci Excel (s a bez VBA).
• Jak používat funkci Excel VBA InStr (s praktickými příklady).
• jak třídit Data v aplikaci Excel pomocí VBA (průvodce krok za krokem).