VBA táblázatok és ListObjects

Ez az oktatóanyag bemutatja, hogyan kell dolgozni a táblákkal és a ListObjects -ekkel a VBA -ban.

VBA táblázatok és ListObjects

A táblázatok az Excel egyik leghasznosabb és legerősebb funkciója, ebben az oktatóanyagban áttekintjük, hogyan használhatjuk a VBA-t egy táblázat létrehozásához, egy egyszerű rendezés hozzáadásához a táblázathoz, egy táblázat szűrése és más, a táblával kapcsolatos feladatok elvégzése.

Hozzon létre egy táblázatot a VBA segítségével

A ListObjects.Add metódus hozzáadhat egy táblázatot a munkalaphoz, a munkalap egy tartománya alapján. Megvan a tartomány ($ A $ 1: $ B $ 8) a Sheet1 nevű munkalapon.

A következő kód hozzáad egy táblázatot Table1 néven a munkalaphoz, a tartomány ($ A $ 1: $ B $ 8) alapján az alapértelmezett táblázatstílus használatával:

123456

Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Asztal 1"End Sub

Az eredmény:

Oszlop beszúrása a táblázat végére VBA -val

A ListColumns.Add metódussal oszlopot adhat hozzá a táblázat végéhez. Az alábbiakban bemutatjuk a Table1 nevű táblázatunkat.

A következő kód használatával adhat hozzá oszlopot a táblázatához, amely mindig hozzáad egy oszlopot a táblázat végéhez:

12345

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

Az eredmény:

Sor beszúrása a táblázat aljára VBA -val

A ListRows.Add módszerrel adhat hozzá egy sort a táblázat aljához. Az alábbiakban bemutatjuk a Table1 nevű táblázatunkat.

A következő kód mindig hozzáad egy sort a táblázat aljához.

12345

Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("1. táblázat"). ListRows.AddEnd Sub

Az eredmény:

Egyszerű rendezés hozzáadása VBA -val

Táblázatot rendezhet a VBA segítségével. Az alábbiakban bemutatjuk a Table1 nevű táblázatunkat, és a VBA segítségével rendezhetjük az értékesítési oszlopot a legalacsonyabbtól a legmagasabbig.

A következő kód növekvő sorrendben rendezi az Értékesítés oszlopot.

12345678910111213141516171819

Sub SimpleSortOnTheTable ()Tartomány ("1. táblázat [[#fejlécek], [Értékesítés]]"). Válassza kiActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Kulcs: = Tartomány ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlNövekvő, DataOption: = xlSortNormalActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Rendezés.Header = xlIgen.MatchCase = Hamis.Orientation = xlTopToBottom.SortMethod = xlPinYin.AlkalmazVége ezzelEnd Sub

Az eredmény:

Táblázat szűrése VBA -val

Az Excel táblázatot a VBA segítségével is szűrheti. Megvan a táblázatunk Table1 néven, és azt szeretnénk szűrni, hogy csak az 1500 -nál nagyobb eladások jelenjenek meg.

Használhatjuk az Autofilter módszert, amelynek öt opcionális paramétere van. Mivel az Eladás oszlopot szeretnénk szűrni, amely a második oszlop, a mezőt 2 -re állítjuk, és az xlAnd operátor paramétert használjuk, amelyet a dátumokhoz és számokhoz használunk.

123456

Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", operátor: = xlAndEnd Sub

Az eredmény:

Törölje a szűrőt a ShowAllData módszerrel a VBA -ban

A szűrő törléséhez elérheti a Munkalap osztály ShowAllData metódusát. Ha egy táblázat szűrőjét szeretné törölni, akkor először ki kell választania egy cellát a táblázatban, amit megtehet a VBA -ban.

A ShowAllData metódus hibát generál, ha valaki nem használ feltételes logikát annak ellenőrzésére, hogy a munkalapon alkalmaztak -e szűrőt. A következő kód megmutatja, hogyan kell ezt megtenni:

123456789

Sub ClearingTheFilter ()Tartomány ("1. táblázat [[#fejlécek], [Értékesítés]]"). Válassza kiHa ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = Igaz akkorActiveSheet.ShowAllDataVége HaEnd Sub

Törölje az összes szűrőt egy Excel táblázatból

A ListObject osztály ShowAllData módszerét anélkül érheti el, hogy először ki kellene választania egy cellát a táblázatban. A következő kód megmutatja, hogyan kell ezt megtenni:

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

Egy sor törlése VBA -val

A ListRows.Delete metódussal törölhet egy sort a táblázat adatbázisában. A sorszám segítségével meg kell adnia, melyik sor. Megvan a következő táblázat: Table1.

Tegyük fel, hogy törölni szeretné a táblázat második sorát a táblázat adatbázisában, a következő kód lehetővé teszi ezt:

12345

Sub Törlés ARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub

Az eredmény:

Oszlop törlése VBA -val

A ListColumns.Delete metódussal törölhet egy oszlopot a táblázatából. Az alábbi táblázat az alábbi táblázatot tartalmazza:

Az első oszlop törléséhez használja a következő kódot:

12345

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

Az eredmény:

Táblázat visszaállítása tartományba a VBA -ban

A táblázatot a normál tartományba alakíthatja vissza a VBA használatával. A következő kód bemutatja, hogyan lehet egy Table1 nevű táblázatot visszaállítani egy tartományba:

12345

Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub

Sávos oszlopok hozzáadása és formázása a munkalap összes táblájához VBA használatával

A munkalap összes tábláját a ListObjects gyűjtemény használatával érheti el. Az alábbi lapon két táblázat található, és egy sávos oszlopot szeretnénk egyszerre hozzáadni mindkét táblázathoz, és mindkét táblázat adatrészének betűtípusát félkövérre változtatni a VBA használatával.

12345678910111213

Sub AddingBandedColumns ()Dim tbl ListObject -kéntDim sht munkalapkéntSet sht = ThisWorkbook.ActiveSheetMinden tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Igaztbl.DataBodyRange.Font.Bold = IgazKövetkező tblEnd Sub

Az eredmény:

Táblázat létrehozása az Access programban a VBA -ban a DoCmd.RunSQL használatával

Táblázat létrehozásának egyik fő módja az Access -ben a VBA -ban a DoCmd.RunSQL metódus használatával futtatható lekérdezés SQL utasítással.

A mintaűrlapon van egy gomb, és amikor rákattintunk a gombra, szeretnénk létrehozni egy táblázatot, amely két mezővel vagy oszloppal rendelkezik, a ProductsTable néven, az egyik az elsődleges kulcsmező, a ProductsID, a másik az Értékesítés mező.

A táblázat létrehozásához a következő kódot használjuk:

123456

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

Az eredmény:

Táblázat szűrése az Access -ben a VBA használatával

A DoCmd.ApplyFilter módszerrel is szűrhet táblázatot az Accessben. Van egy egyszerű táblázatunk az Access alatt, a ProductsTable néven.

Szeretnénk megnyomni ezt a gombot az űrlapunkon, majd csak az 1500 -nál nagyobb értékesítéseket látni.

Tehát a következő kódot használjuk ehhez:

1234567

Privát al cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Értékesítés]> 1500"End Sub

Az eredmény: