Tabele VBA in ListObjects

Ta vadnica bo pokazala, kako delati s tabelami in ListObjects v VBA.

Tabele VBA in ListObjects

Tabele so ena izmed najbolj uporabnih in zmogljivih Excelovih funkcij, v tej vadnici bomo preučili, kako uporabiti VBA za ustvarjanje tabele, dodati preprosto razvrščanje v tabelo, filtrirati tabelo in izvesti druga opravila, povezana s tabelo.

Ustvarite tabelo z VBA

Metoda ListObjects.Add lahko doda tabelo na delovni list na podlagi obsega na tem delovnem listu. Obseg, prikazan v ($ A $ 1: $ B $ 8), imamo na delovnem listu Sheet1.

Naslednja koda bo dodala tabelo, imenovano Table1, na vaš delovni list glede na obseg ($ A $ 1: $ B $ 8) z uporabo privzetega sloga tabele:

123456

Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlDa). Ime = _"Tabela 1"End Sub

Rezultat je:

Vstavljanje stolpca na koncu tabele z VBA

Za dodajanje stolpca na konec tabele lahko uporabite metodo ListColumns.Add. Spodaj je prikazana tabela z imenom Tabela1.

V tabelo lahko dodate stolpec z naslednjo kodo, ki bo vedno dodala stolpec na konec tabele:

12345

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

Rezultat je:

Vstavljanje vrstice na dnu mize z VBA

Z metodo ListRows.Add lahko dodate vrstico na dno tabele. Spodaj je prikazana tabela z imenom Tabela1.

Naslednja koda bo vedno dodala vrstico na dno tabele.

12345

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

Rezultat je:

Dodajanje preproste sorte z VBA

Tabelo lahko razvrstite z VBA. Spodaj je prikazana tabela, imenovana Table1, s pomočjo VBA pa lahko prodajni stolpec razvrstimo od najnižjega do najvišjega.

Naslednja koda bo stolpec Prodaja razvrstila po naraščajočem vrstnem redu.

12345678910111213141516171819

Sub SimpleSortOnTheTable ()Obseg ("Tabela1 [[#glave], [Prodaja]]"). IzberiteActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Ključ: = Obseg ("Tabela1 [[#Vse], [Prodaja]]"), SortOn: = xlSortOnValues, Vrstni red: = _xlNaraščajoče, možnost podatkov: = xlSortNormalZ ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.Glavica = xlDa.MatchCase = Neresnično.Orientation = xlTopToBottom.SortMethod = xlPinYin.UveljaviKončaj sEnd Sub

Rezultat je:

Filtrirajte tabelo z VBA

Tabelo Excel lahko filtrirate tudi z uporabo VBA. Imamo svojo tabelo z imenom Table1 in tabelo želimo filtrirati tako, da je prikazana samo prodaja, večja od 1500.

Lahko uporabimo metodo samodejnega filtriranja, ki ima pet neobveznih parametrov. Ker želimo filtrirati stolpec Prodaja, ki je drugi stolpec, smo polje nastavili na 2 in uporabili operacijski parameter xlAnd, ki se uporablja za datume in številke.

123456

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

Rezultat je:

Počistite filter z metodo ShowAllData v VBA

Dostopate lahko do metode ShowAllData razreda delovnega lista, da počistite filter. Če želite počistiti filtre tabele, morate najprej izbrati celico v tabeli, kar lahko storite v VBA.

Metoda ShowAllData bo ustvarila napako, če ne uporabimo pogojne logike, da bi preverili, ali je bil na delovnem listu uporabljen filter. Naslednja koda vam pokaže, kako to storite:

123456789

Sub ClearingTheFilter ()Obseg ("Tabela1 [[#glave], [Prodaja]]"). IzberiteČe je ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True PotemActiveSheet.ShowAllDataKonec ČeEnd Sub

Počisti vse filtre iz Excelove tabele

Dostopate lahko do metode ShowAllData razreda ListObject, ne da bi morali najprej izbrati celico v tabeli. Naslednja koda vam pokaže, kako to storite:

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

Brisanje vrstice z VBA

Vrstico v podatkovnem telesu svoje tabele lahko izbrišete z metodo ListRows.Delete. S številko vrstice morate določiti, katero vrstico. Imamo naslednjo tabelo, imenovano Tabela1.

Recimo, da ste želeli izbrisati drugo vrstico v podatkovnem telesu svoje tabele, to vam bo omogočila naslednja koda:

12345

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

Rezultat je:

Brisanje stolpca z VBA

Stolpec iz tabele lahko izbrišete z metodo ListColumns.Delete. Spodaj je prikazana tabela z imenom Tabela1:

Če želite izbrisati prvi stolpec, uporabite naslednjo kodo:

12345

PodizbrišiA stolpec ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteEnd Sub

Rezultat je:

Pretvarjanje tabele nazaj v obseg v VBA

Tabelo lahko pretvorite v normalno območje z uporabo VBA. Naslednja koda prikazuje, kako pretvorite tabelo z imenom Table1 nazaj v obseg:

12345

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

Dodajanje pasovnih stolpcev in oblikovanje vsem tabelam na delovnem listu z uporabo VBA

Do zbirk ListObjects lahko dostopate do vseh tabel na svojem delovnem listu. Na spodnjem listu imamo dve tabeli in želimo obema tabelama dodati pasovni stolpec hkrati in s pomočjo VBA spremeniti pisavo podatkovnega dela obeh tabel v krepko.

12345678910111213

Sub AddingBandedColumns ()Zatemni tbl kot ListObjectZatemni kot delovni listSet sht = ThisWorkbook.ActiveSheetZa vsak tbl V sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = ResNaslednja tblEnd Sub

Rezultat je:

Ustvarjanje tabele v Accessu v VBA z uporabo DoCmd.RunSQL

Eden glavnih načinov za ustvarjanje tabele v Accessu v VBA je uporaba metode DoCmd.RunSQL za izvajanje poizvedbe dejanja z stavkom SQL.

Na vzorčnem obrazcu imamo gumb in ko kliknemo gumb, želimo ustvariti tabelo z imenom ProductsTable z dvema poljima ali stolpcema, eno bi bilo polje primarnega ključa, imenovano ProductsID, drugo pa polje, ki se imenuje Prodaja.

Za izdelavo te tabele bi uporabili naslednjo kodo:

123456

Zasebna podrejena cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(PRIMARNI KLJUČ INTEGERA ID -ja izdelka, prodajno celo število);"End Sub

Rezultat je:

Filtriranje tabele v Accessu z uporabo VBA

Tabelo v Accessu lahko filtrirate tudi z metodo DoCmd.ApplyFilter. Spodaj v Accessu imamo preprosto tabelo, imenovano ProductsTable.

Ta gumb želimo pritisniti na obrazec in nato videti samo prodajo, ki je večja od 1500.

Zato bi za to uporabili naslednjo kodo:

1234567

Zasebna podrejena cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Prodaja]> 1500"End Sub

Rezultat je: