VBA lentelės ir sąrašo objektai

Ši pamoka parodys, kaip dirbti su lentelėmis ir „ListObjects“ VBA.

VBA lentelės ir sąrašo objektai

Lentelės yra viena iš naudingiausių ir galingiausių „Excel“ funkcijų, šioje pamokoje apžvelgsime, kaip naudojant VBA sukurti lentelę, pridėti paprastą rūšiavimą prie lentelės, filtruoti lentelę ir atlikti kitas su lentele susijusias užduotis.

Sukurkite lentelę su VBA

„ListObjects.Add“ metodas gali pridėti lentelę prie darbalapio, atsižvelgdamas į to darbalapio diapazoną. Mes turime diapazoną, rodomą ($ A $ 1: $ B $ 8) darbalapyje, pavadintame „Sheet1“.

Šis kodas pridės lentelę, pavadintą Table1, į jūsų darbalapį pagal diapazoną ($ A $ 1: $ B $ 8), naudojant numatytąjį lentelės stilių:

123456

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

Rezultatas yra toks:

Stulpelio įterpimas lentelės pabaigoje su VBA

Norėdami pridėti stulpelį prie lentelės pabaigos, galite naudoti metodą ListColumns.Add. Turime lentelę, pavadintą „Table1“, parodyta žemiau.

Galite pridėti stulpelį prie lentelės naudodami šį kodą, kuris visada pridės stulpelį prie lentelės pabaigos:

12345

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

Rezultatas yra toks:

Eilutės įterpimas lentelės apačioje su VBA

Norėdami pridėti eilutę prie lentelės apačios, galite naudoti metodą „ListRows.Add“. Turime lentelę, pavadintą „Table1“, parodyta žemiau.

Šis kodas visada pridės eilutę prie lentelės apačios.

12345

Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Table1"). ListRows.AddPabaiga Sub

Rezultatas yra toks:

Paprasto rūšiavimo pridėjimas naudojant VBA

Lentelę galite rūšiuoti naudodami VBA. Turime žemiau pateiktą lentelę, pavadintą 1 lentelė, ir mes galime naudoti VBA, norėdami rūšiuoti pardavimo stulpelį nuo mažiausio iki aukščiausio.

Šis kodas stulpelį „Pardavimas“ surūšiuos didėjančia tvarka.

12345678910111213141516171819

Sub SimpleSortOnTheTable ()Diapazonas („1 lentelė [[#antraštės], [pardavimai]]“). PasirinkiteActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Raktas: = Diapazonas („1 lentelė [[##], [Pardavimas]]“), „Rūšiuoti“: = xlSortOnValues, tvarka: = _xlAscending, DataOption: = xlSortNormalSu ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Rūšiuoti.Viršelis = xlTaip.MatchCase = Netiesa.Orientation = xlTopToBottom.SortMethod = xlPinYin.TaikytiPabaiga suPabaiga Sub

Rezultatas yra toks:

Filtruokite lentelę naudodami VBA

Taip pat galite filtruoti „Excel“ lentelę naudodami VBA. Turime lentelę, vadinamą 1 lentele, ir norėtume filtruoti lentelę, kad būtų rodomi tik didesni nei 1500 pardavimai.

Galime naudoti automatinio filtro metodą, kuris turi penkis pasirenkamus parametrus. Kadangi norime filtruoti pardavimo stulpelį, kuris yra antrasis stulpelis, nustatome lauką į 2 ir naudojame xlAnd operatoriaus parametrą, kuris naudojamas datoms ir skaičiams.

123456

Paprastas filtras ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", operatorius: = xlAndPabaiga Sub

Rezultatas yra toks:

Išvalykite filtrą naudodami ShowAllData metodą VBA

Norėdami išvalyti filtrą, galite pasiekti darbalapio klasės ShowAllData metodą. Jei norite išvalyti lentelės filtrą (-us), pirmiausia turite pasirinkti lentelės langelį, kurį galite padaryti VBA.

„ShowAllData“ metodas sukels klaidą, jei nenaudosite sąlyginės logikos, kad patikrintumėte, ar darbalapyje buvo pritaikytas filtras. Šis kodas parodo, kaip tai padaryti:

123456789

„Sub ClearingTheFilter“ ()Diapazonas („1 lentelė [[#antraštės], [pardavimai]]“). PasirinkiteJei ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True TadaActiveSheet.ShowAllDataPabaiga JeiPabaiga Sub

Išvalykite visus filtrus iš „Excel“ lentelės

Galite pasiekti „ShowOllData“ klasės „ListObject“ metodą, prieš tai nepasirinkę lentelės langelio. Šis kodas parodo, kaip tai padaryti:

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

Eilutės ištrynimas naudojant VBA

Lentelės duomenų bazėje galite ištrinti eilutę naudodami „ListRows.Delete“ metodą. Turite nurodyti eilutę naudodami eilutės numerį. Turime šią lentelę, pavadintą 1 lentelė.

Tarkime, kad norėjote ištrinti antrąją lentelės duomenų bazės eilutę, šis kodas jums leis tai padaryti:

12345

SubtrukasARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .IštrintiPabaiga Sub

Rezultatas yra toks:

Stulpelio ištrynimas naudojant VBA

Stulpelį iš lentelės galite ištrinti naudodami ListColumns.Delete metodą. Turime toliau pateiktą lentelę, pavadintą 1 lentelė:

Norėdami ištrinti pirmąjį stulpelį, naudokite šį kodą:

12345

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

Rezultatas yra toks:

Lentelės konvertavimas į diapazoną VBA

Naudodami VBA, galite konvertuoti lentelę į įprastą diapazoną. Šis kodas parodo, kaip lentelę, pavadintą Table1, paversti į diapazoną:

12345

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

Juostinių stulpelių pridėjimas ir formatavimas visose darbalapio lentelėse naudojant VBA

Galite pasiekti visas darbalapio lenteles naudodami „ListObjects“ kolekciją. Žemiau esančiame lape turime dvi lenteles ir norime iš karto prie abiejų lentelių pridėti juostinį stulpelį ir abiejų lentelių duomenų skyriaus šriftą pakeisti paryškintu, naudojant VBA.

12345678910111213

Sub AddingBandedColumns ()Dim tbl kaip ListObjectDim sht kaip darbalapisNustatykite sht = ThisWorkbook.ActiveSheetUž kiekvieną tbl In sht.ListObjectstbl.ShowTableStyleColumnStripes = Tiesatbl.DataBodyRange.Font.Bold = TiesaKitas tblPabaiga Sub

Rezultatas yra toks:

Lentelės kūrimas „Access“ VBA naudojant „DoCmd.RunSQL“

Vienas iš pagrindinių būdų, kaip sukurti lentelę „Access“ VBA, yra naudojant „DoCmd.RunSQL“ metodą, norint paleisti veiksmo užklausą naudojant SQL sakinį.

Mūsų pavyzdinėje formoje yra mygtukas ir spustelėję mygtuką norėtume sukurti lentelę „ProductsTable“ su dviem laukais arba stulpeliais, vienas būtų pirminio rakto laukas, pavadintas „ProductsID“, o kitas - laukas „Pardavimas“.

Norėdami sukurti šią lentelę, naudosime šį kodą:

123456

Privatus antrinis cmdCreateProductsTable_Click ()„DoCmd.RunSQL“ „CREATE TABLE ProductsTable“ _& "(PRODUKTINIS INTEGERIO PAGRINDINIS RAKTAS, pardavimų sveikasis skaičius);"Pabaiga Sub

Rezultatas yra toks:

Lentelės filtravimas prieigoje naudojant VBA

Taip pat galite filtruoti lentelę programoje „Access“ naudodami „DoCmd.ApplyFilter“ metodą. Turime paprastą lentelę, parodytą „Access“, pavadintą „ProductsTable“.

Norėtume paspausti šį mygtuką mūsų formoje ir tada matyti tik pardavimus, kurių skaičius didesnis nei 1500.

Taigi, norėdami tai padaryti, naudosime šį kodą:

1234567

Privatus antrinis cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"„DoCmd.ApplyFilter“, „[Pardavimas]> 1500“Pabaiga Sub

Rezultatas yra toks: