Tabulky VBA a ListObjects

Tento tutoriál předvede, jak pracovat s tabulkami a ListObjects ve VBA.

Tabulky VBA a ListObjects

Tabulky jsou jednou z nejužitečnějších a nejvýkonnějších funkcí Excelu. V tomto tutoriálu si probereme, jak pomocí VBA vytvořit tabulku, přidat do tabulky jednoduché řazení, filtrovat tabulku a provádět další úkoly související s tabulkou.

Vytvořte tabulku pomocí VBA

Metoda ListObjects.Add může přidat tabulku do listu na základě rozsahu v tomto listu. Máme rozsah zobrazený v ($ A $ 1: $ B $ 8) na listu s názvem Sheet1.

Následující kód přidá do vašeho listu tabulku s názvem Table1 na základě rozsahu ($ A $ 1: $ B $ 8) pomocí výchozího stylu tabulky:

123456

Dílčí CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Stůl 1"End Sub

Výsledek je:

Vložení sloupce na konec tabulky pomocí VBA

Chcete -li přidat sloupec na konec tabulky, můžete použít metodu ListColumns.Add. Naše tabulka s názvem Tabulka 1 je zobrazena níže.

Do tabulky můžete přidat sloupec pomocí následujícího kódu, který vždy přidá sloupec na konec tabulky:

12345

Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddEnd Sub

Výsledek je:

Vložení řádku na konec tabulky pomocí VBA

Pomocí metody ListRows.Add můžete přidat řádek do spodní části tabulky. Naše tabulka s názvem Tabulka 1 je zobrazena níže.

Následující kód vždy přidá řádek do spodní části tabulky.

12345

Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabulka1"). ListRows.AddEnd Sub

Výsledek je:

Přidání jednoduchého řazení pomocí VBA

Tabulku můžete třídit pomocí VBA. Naše tabulka s názvem Tabulka1 je zobrazena níže a pomocí VBA můžeme třídit sloupec prodeje od nejnižší po nejvyšší.

Následující kód seřadí sloupec Prodej ve vzestupném pořadí.

12345678910111213141516171819

Sub SimpleSortOnTheTable ()Rozsah ("Tabulka1 [[#záhlaví], [Prodej]]"). VyberteActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Klíč: = Rozsah ("Tabulka1 [[#Vše], [Prodej]]"), SortOn: = xlSortOnValues, Objednávka: = _xlAscending, DataOption: = xlSortNormalS ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Seřadit.Header = xlAno.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplikovatKonec sEnd Sub

Výsledek je:

Filtrování tabulky pomocí VBA

Tabulku aplikace Excel můžete také filtrovat pomocí jazyka VBA. Máme naši tabulku s názvem Tabulka1 a chtěli bychom tabulku filtrovat tak, aby se zobrazovaly pouze tržby vyšší než 1 500.

Můžeme použít metodu Autofilter, která má pět volitelných parametrů. Protože bychom chtěli filtrovat sloupec Prodej, což je druhý sloupec, nastavíme pole na 2 a použijeme parametr operátoru xlAnd, který se používá pro data a čísla.

123456

Dílčí jednoduchý filtr ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", operátor: = xlAndEnd Sub

Výsledek je:

Vymažte filtr metodou ShowAllData ve VBA

Chcete -li vymazat filtr, můžete přistupovat k metodě ShowAllData třídy listu. Pokud se jedná o filtry tabulky, které chcete vymazat, musíte nejprve vybrat buňku v tabulce, což můžete provést ve VBA.

Metoda ShowAllData vygeneruje chybu, pokud člověk nepoužívá podmíněnou logiku, aby zkontroloval, zda byl v listu použit filtr. Následující kód ukazuje, jak to provést:

123456789

Dílčí zúčtováníTheFilter ()Rozsah ("Tabulka1 [[#záhlaví], [Prodej]]"). VybertePokud ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataKonec IfEnd Sub

Vymažte všechny filtry z tabulky aplikace Excel

Můžete přistupovat k metodě ShowAllData třídy ListObject, aniž byste museli nejprve vybrat buňku v tabulce. Následující kód ukazuje, jak to provést:

123	Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub

Odstranění řádku pomocí VBA

Můžete odstranit řádek v databázi vaší tabulky pomocí metody ListRows.Delete. Pomocí čísla řádku musíte určit, který řádek. Máme následující tabulku s názvem Tabulka1.

Řekněme, že jste chtěli odstranit druhý řádek v databázi vaší tabulky, následující kód vám to umožní:

12345

Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub

Výsledek je:

Odstranění sloupce pomocí VBA

Sloupec můžete z tabulky odstranit pomocí metody ListColumns.Delete. Následující tabulka s názvem Tabulka1 je uvedena níže:

Chcete -li odstranit první sloupec, použijte následující kód:

12345

Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteEnd Sub

Výsledek je:

Převod tabulky zpět na rozsah ve VBA

Tabulku můžete převést zpět na normální rozsah pomocí VBA. Následující kód ukazuje, jak převést tabulku s názvem Table1 zpět na rozsah:

12345

Dílčí převodATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub

Přidání pruhovaných sloupců a formátování do všech tabulek v listu pomocí VBA

Ke všem tabulkám v listu můžete přistupovat pomocí kolekce ListObjects. V níže uvedeném listu máme dvě tabulky a chtěli bychom přidat pruhovaný sloupec do obou tabulek najednou a pomocí VBA změnit písmo datové části obou tabulek na tučné.

12345678910111213

SubAddingBandedColumns ()Dim tbl jako ListObjectDim sht As jako pracovní listNastavit sht = ThisWorkbook.ActiveSheetPro každý tbl v sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TrueDalší tblEnd Sub

Výsledek je:

Vytvoření tabulky v Accessu ve VBA pomocí DoCmd.RunSQL

Jedním z hlavních způsobů, jak vytvořit tabulku v Accessu ve VBA, je použití metody DoCmd.RunSQL ke spuštění akčního dotazu pomocí příkazu SQL.

Na ukázkovém formuláři máme tlačítko a když na tlačítko klikneme, rádi bychom vytvořili tabulku s názvem ProductsTable se dvěma poli nebo sloupci, jedno by bylo pole primárního klíče s názvem ProductsID a druhé pole s názvem Prodej.

K vytvoření této tabulky bychom použili následující kód:

123456

Private Sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"End Sub

Výsledek je:

Filtrování tabulky v Accessu pomocí VBA

Tabulku můžete také filtrovat v Accessu pomocí metody DoCmd.ApplyFilter. Naše jednoduchá tabulka je uvedena níže v Accessu s názvem ProductsTable.

Chtěli bychom stisknout toto tlačítko na našem formuláři a poté zobrazit pouze tržby vyšší než 1 500.

K tomu bychom použili následující kód:

1234567

Soukromý sub cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Prodej]> 1500"End Sub

Výsledek je: