Šī apmācība parādīs, kā strādāt ar tabulām un ListObjects VBA.
VBA tabulas un sarakstu objekti
Tabulas ir viena no Excel noderīgākajām un spēcīgākajām funkcijām, šajā apmācībā mēs apskatīsim, kā izmantot VBA, lai izveidotu tabulu, pievienotu tabulai vienkāršu kārtošanu, filtrētu tabulu un veiktu citus ar tabulu saistītus uzdevumus.
Izveidojiet tabulu ar VBA
Izmantojot metodi ListObjects.Add, darblapai var pievienot tabulu, pamatojoties uz šīs darblapas diapazonu. Mums ir diapazons, kas parādīts ($ A $ 1: $ B $ 8) darblapā ar nosaukumu Sheet1.
Šis kods jūsu darblapai pievienos tabulu ar nosaukumu Table1, pamatojoties uz diapazonu ($ A $ 1: $ B $ 8), izmantojot noklusējuma tabulas stilu:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlJā) .Name = _"1. tabula"Beigu apakš |
Rezultāts ir šāds:
Kolonnas ievietošana tabulas beigās ar VBA
Varat izmantot metodi ListColumns.Add, lai tabulas beigās pievienotu kolonnu. Tālāk ir parādīta mūsu tabula ar nosaukumu Table1.
Tabulai varat pievienot kolonnu, izmantojot šādu kodu, kas vienmēr pievienos kolonnu tabulas beigām:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddBeigu apakš |
Rezultāts ir šāds:
Rindas ievietošana tabulas apakšā ar VBA
Varat izmantot metodi ListRows.Add, lai tabulas apakšdaļai pievienotu rindu. Tālāk ir parādīta mūsu tabula ar nosaukumu Table1.
Šis kods vienmēr pievienos rindu tabulas apakšdaļai.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("1. tabula"). ListRows.AddBeigu apakš |
Rezultāts ir šāds:
Vienkāršas kārtošanas pievienošana ar VBA
Tabulu var kārtot ar VBA. Tālāk ir parādīta mūsu tabula ar nosaukumu Table1, un mēs varam izmantot VBA, lai kārtotu pārdošanas kolonnu no zemākās uz augstāko.
Šis kods kārtos kolonnu Pārdošana augošā secībā.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Diapazons ("1. tabula [[#galvenes], [pārdošana]]"). AtlasietActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Atslēga: = Diapazons ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xl Augoši, DataOption: = xlSortNormalAr ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Kārtot.Galva = xlJā.MatchCase = Nepareizi.Orientation = xlTopToBottom.SortMethod = xlPinYin.PiesakiesBeigt arBeigu apakš |
Rezultāts ir šāds:
Filtrējiet tabulu, izmantojot VBA
Varat arī filtrēt Excel tabulu, izmantojot VBA. Mums ir mūsu tabula ar nosaukumu Table1, un mēs vēlētos filtrēt tabulu tā, lai tiktu parādīti tikai pārdošanas apjomi, kas pārsniedz 1500.
Mēs varam izmantot automātiskā filtra metodi, kurai ir pieci izvēles parametri. Tā kā mēs vēlētos filtrēt kolonnu Pārdošana, kas ir otrā sleja, mēs iestatījām lauku uz 2, un mēs izmantojam operatora parametru xlAnd, kas tiek izmantots datumiem un skaitļiem.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", operators: = xlAndBeigu apakš |
Rezultāts ir šāds:
Notīriet filtru, izmantojot ShowAllData metodi VBA
Lai notīrītu filtru, varat piekļūt darblapas klases ShowAllData metodei. Ja tas ir tabulas filtrs, kuru vēlaties notīrīt, vispirms tabulā ir jāizvēlas šūna, ko varat izdarīt VBA.
ShowAllData metode radīs kļūdu, ja neizmantosit nosacīto loģiku, lai pārbaudītu, vai darblapā ir lietots filtrs. Šis kods parāda, kā to izdarīt:
| 123456789 | Sub ClearingTheFilter ()Diapazons ("1. tabula [[#galvenes], [pārdošana]]"). AtlasietJa ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True TadActiveSheet.ShowAllDataBeigas JaBeigu apakš |
Notīriet visus filtrus no Excel tabulas
Jūs varat piekļūt ListObject klases ShowAllData metodei, vispirms neizvēloties tabulas šūnu. Šis kods parāda, kā to izdarīt:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataBeigu apakš |
Rindas dzēšana ar VBA
Rindu tabulas datu bāzē var izdzēst, izmantojot metodi ListRows.Delete. Izmantojot rindas numuru, jums jānorāda, kura rinda. Mums ir šāda tabula ar nosaukumu Table1.
Pieņemsim, ka vēlaties dzēst savu tabulas datu bāzes otro rindu, šāds kods ļautu jums to izdarīt:
| 12345 | Sub Delete ARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteBeigu apakš |
Rezultāts ir šāds:
Kolonnas dzēšana, izmantojot VBA
Varat izdzēst kolonnu no tabulas, izmantojot metodi ListColumns.Delete. Tālāk ir parādīta šāda tabula ar nosaukumu Table1:
Lai izdzēstu pirmo kolonnu, izmantojiet šādu kodu:
| 12345 | ApakšdzēsšanaAkolonna ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteBeigu apakš |
Rezultāts ir šāds:
Tabulas konvertēšana atpakaļ diapazonā VBA
Izmantojot VBA, varat pārvērst tabulu normālā diapazonā. Šis kods parāda, kā tabulu ar nosaukumu Table1 pārvērst diapazonā:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistBeigu apakš |
Joslas kolonnu pievienošana un formatēšana visām darblapas tabulām, izmantojot VBA
Jūs varat piekļūt visām darblapas tabulām, izmantojot kolekciju ListObjects. Zemāk esošajā lapā mums ir divas tabulas, un mēs vēlamies abām tabulām uzreiz pievienot joslu ar kolonnām un mainīt tabulu datu sadaļas fontu uz treknrakstu, izmantojot VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl kā ListObjectDim sht kā darblapaIestatīt sht = ThisWorkbook.ActiveSheetPar katru tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Patiesatbl.DataBodyRange.Font.Bold = PatiesaNākamais tblBeigu apakš |
Rezultāts ir šāds:
Tabulas izveide programmā Access VBA, izmantojot DoCmd.RunSQL
Viens no galvenajiem veidiem, kā izveidot tabulu programmā Access VBA, ir izmantot DoCmd.RunSQL metodi, lai palaistu darbības vaicājumu ar SQL priekšrakstu.
Mūsu parauga veidlapā ir poga, un, noklikšķinot uz pogas, mēs vēlamies izveidot tabulu ar nosaukumu ProductsTable ar diviem laukiem vai kolonnām, viens būtu primārās atslēgas lauks ProduktiID, bet otrs - lauks Pārdošana.
Lai izveidotu šo tabulu, mēs izmantosim šādu kodu:
| 123456 | Privāts apakš cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Beigu apakš |
Rezultāts ir šāds:
Tabulas filtrēšana programmā Access, izmantojot VBA
Varat arī filtrēt tabulu programmā Access, izmantojot metodi DoCmd.ApplyFilter. Mūsu vienkāršā tabula, kas parādīta zemāk Access, tiek saukta par produktu tabulu.
Mēs vēlētos nospiest šo pogu mūsu veidlapā un pēc tam redzēt tikai pārdošanas apjomu, kas pārsniedz 1500.
Tātad, lai to izdarītu, mēs izmantosim šādu kodu:
| 1234567 | Privāts apakš cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Pārdošana]> 1500"Beigu apakš |
Rezultāts ir šāds:
