Αυτό το σεμινάριο θα δείξει πώς να εργάζεστε με πίνακες και ListObjects σε VBA.
Πίνακες VBA και ListObjects
Οι πίνακες είναι μία από τις πιο χρήσιμες και ισχυρές δυνατότητες του Excel, σε αυτό το σεμινάριο, θα εξετάσουμε πώς να χρησιμοποιήσετε το VBA για να δημιουργήσετε έναν πίνακα, να προσθέσετε μια απλή ταξινόμηση σε έναν πίνακα, να φιλτράρετε έναν πίνακα και να εκτελέσετε άλλες εργασίες που σχετίζονται με τον πίνακα.
Δημιουργήστε έναν πίνακα με VBA
Η μέθοδος ListObjects.Add μπορεί να προσθέσει έναν πίνακα σε ένα φύλλο εργασίας, με βάση ένα εύρος σε αυτό το φύλλο εργασίας. Έχουμε το εύρος που εμφανίζεται σε ($ A $ 1: $ B $ 8) σε ένα φύλλο εργασίας που ονομάζεται Sheet1.
Ο ακόλουθος κώδικας θα προσθέσει έναν πίνακα, που ονομάζεται Table1 στο φύλλο εργασίας σας, με βάση το εύρος ($ A $ 1: $ B $ 8) χρησιμοποιώντας το προεπιλεγμένο στυλ πίνακα:
| 123456 | Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlΝαι) .Name = _"Τραπέζι 1"Τέλος υπο |
Το αποτέλεσμα είναι:
Εισαγωγή στήλης στο τέλος του πίνακα με VBA
Μπορείτε να χρησιμοποιήσετε τη μέθοδο ListColumns.Add για να προσθέσετε μια στήλη στο τέλος του πίνακά σας. Έχουμε τον πίνακα μας που ονομάζεται Table1 που φαίνεται παρακάτω.
Μπορείτε να προσθέσετε μια στήλη στον πίνακά σας χρησιμοποιώντας τον ακόλουθο κώδικα, ο οποίος θα προσθέτει πάντα μια στήλη στο τέλος του πίνακα:
| 12345 | Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). ListColumns.AddΤέλος υπο |
Το αποτέλεσμα είναι:
Εισαγωγή μιας σειράς στο κάτω μέρος του πίνακα με VBA
Μπορείτε να χρησιμοποιήσετε τη μέθοδο ListRows.Add για να προσθέσετε μια γραμμή στο κάτω μέρος του πίνακά σας. Έχουμε τον πίνακα μας που ονομάζεται Table1 που φαίνεται παρακάτω.
Ο ακόλουθος κώδικας θα προσθέτει πάντα μια γραμμή στο κάτω μέρος του πίνακά σας.
| 12345 | Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Πίνακας 1"). ListRows.AddΤέλος υπο |
Το αποτέλεσμα είναι:
Προσθήκη απλής ταξινόμησης με VBA
Μπορείτε να ταξινομήσετε έναν πίνακα με VBA. Έχουμε τον πίνακά μας που ονομάζεται Table1 που φαίνεται παρακάτω και μπορούμε να χρησιμοποιήσουμε το VBA για να ταξινομήσουμε τη στήλη πωλήσεων από τη χαμηλότερη στην υψηλότερη.
Ο ακόλουθος κώδικας θα ταξινομήσει τη στήλη Πωλήσεις σε αύξουσα σειρά.
| 12345678910111213141516171819 | Sub SimpleSortOnTheTable ()Εύρος ("Table1 [[#Headers], [Sales]]"). ΕπιλέξτεActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Sort.SortFields.Add _Κλειδί: = Εύρος ("Table1 [[#All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalΜε ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). Ταξινόμηση.Header = xlΝαι.MatchCase = Λάθος. Προσανατολισμός = xlTopToBottom.SortMethod = xlPinYin.ΙσχύουνΤέλος μεΤέλος υπο |
Το αποτέλεσμα είναι:
Φιλτράρετε έναν πίνακα με VBA
Μπορείτε επίσης να φιλτράρετε έναν πίνακα Excel χρησιμοποιώντας VBA. Έχουμε τον πίνακά μας που ονομάζεται Table1 και θα θέλαμε να φιλτράρουμε τον πίνακα έτσι ώστε να εμφανίζονται μόνο πωλήσεις μεγαλύτερες από 1500.
Μπορούμε να χρησιμοποιήσουμε τη μέθοδο Αυτόματου φιλτραρίσματος, η οποία έχει πέντε προαιρετικές παραμέτρους. Δεδομένου ότι θα θέλαμε να φιλτράρουμε τη στήλη Πωλήσεις που είναι η δεύτερη στήλη, ορίζουμε το Πεδίο σε 2 και χρησιμοποιούμε την παράμετρο xlAnd χειριστή, η οποία χρησιμοποιείται για ημερομηνίες και αριθμούς.
| 123456 | Sub SimpleFilter ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). Range.AutoFilter Field: = 2, Criteria1: = _"> 1500", Χειριστής: = xlAndΤέλος υπο |
Το αποτέλεσμα είναι:
Διαγράψτε το φίλτρο με τη μέθοδο ShowAllData στο VBA
Μπορείτε να αποκτήσετε πρόσβαση στη μέθοδο ShowAllData της κλάσης φύλλου εργασίας για να καθαρίσετε το φίλτρο. Εάν πρόκειται για φίλτρα (τα) ενός πίνακα που θέλετε να καθαρίσετε, τότε πρέπει πρώτα να επιλέξετε ένα κελί στον πίνακα, το οποίο μπορείτε να κάνετε σε VBA.
Η μέθοδος ShowAllData θα δημιουργήσει σφάλμα εάν κάποιος δεν χρησιμοποιεί λογική υπό όρους για να ελέγξει εάν έχει εφαρμοστεί φίλτρο στο φύλλο εργασίας. Ο παρακάτω κώδικας σάς δείχνει πώς να το κάνετε αυτό:
| 123456789 | Sub ClearingTheFilter ()Εύρος ("Table1 [[#Headers], [Sales]]"). ΕπιλέξτεΑν ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ΤότεActiveSheet.ShowAllDataΤέλος εανΤέλος υπο |
Εκκαθάριση όλων των φίλτρων από έναν πίνακα Excel
Μπορείτε να αποκτήσετε πρόσβαση στη μέθοδο ShowAllData της κλάσης ListObject χωρίς να χρειαστεί να επιλέξετε πρώτα ένα κελί στον πίνακα. Ο παρακάτω κώδικας σάς δείχνει πώς να το κάνετε αυτό:
| 123 | Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataΤέλος υπο |
Διαγραφή μιας σειράς με VBA
Μπορείτε να διαγράψετε μια γραμμή στη βάση δεδομένων του πίνακα σας χρησιμοποιώντας τη μέθοδο ListRows.Delete. Πρέπει να καθορίσετε ποια σειρά χρησιμοποιεί τον αριθμό σειράς. Έχουμε τον ακόλουθο πίνακα που ονομάζεται Πίνακας 1.
Ας υποθέσουμε ότι θέλετε να διαγράψετε τη δεύτερη σειρά στη βάση δεδομένων του πίνακα σας, ο ακόλουθος κώδικας θα σας επιτρέψει να το κάνετε αυτό:
| 12345 | Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteΤέλος υπο |
Το αποτέλεσμα είναι:
Διαγραφή στήλης με VBA
Μπορείτε να διαγράψετε μια στήλη από τον πίνακά σας χρησιμοποιώντας τη μέθοδο ListColumns.Delete. Έχουμε τον ακόλουθο πίνακα που ονομάζεται Πίνακας 1 που φαίνεται παρακάτω:
Για να διαγράψετε την πρώτη στήλη, θα χρησιμοποιήσετε τον ακόλουθο κώδικα:
| 12345 | Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns (1) .DeleteΤέλος υπο |
Το αποτέλεσμα είναι:
Μετατροπή πίνακα πίσω σε εύρος στο VBA
Μπορείτε να μετατρέψετε έναν πίνακα σε κανονικό εύρος χρησιμοποιώντας VBA. Ο παρακάτω κώδικας σάς δείχνει πώς μπορείτε να μετατρέψετε έναν πίνακα που ονομάζεται Table1 ξανά σε εύρος:
| 12345 | Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistΤέλος υπο |
Προσθήκη ζωνών στηλών και μορφοποίηση σε όλους τους πίνακες σε ένα φύλλο εργασίας χρησιμοποιώντας VBA
Μπορείτε να έχετε πρόσβαση σε όλους τους πίνακες στο φύλλο εργασίας σας χρησιμοποιώντας τη συλλογή ListObjects. Στο παρακάτω φύλλο έχουμε δύο πίνακες και θα θέλαμε να προσθέσουμε μια συστοιχία στήλης και στους δύο πίνακες ταυτόχρονα και να αλλάξουμε τη γραμματοσειρά της ενότητας δεδομένων και των δύο πινάκων σε έντονη, χρησιμοποιώντας VBA.
| 12345678910111213 | Sub AddingBandedColumns ()Dim tbl Ως ListObjectDim sht Ως φύλλο εργασίαςΟρισμός sht = ThisWorkbook.ActiveSheetΓια κάθε tbl Σε sht.ListObjectstbl.ShowTableStyleColumnStripes = Αλήθειαtbl.DataBodyRange.Font.Bold = TrueΕπόμενο tblΤέλος υπο |
Το αποτέλεσμα είναι:
Δημιουργία πίνακα στην Access στο VBA χρησιμοποιώντας το DoCmd.RunSQL
Ένας από τους κύριους τρόπους δημιουργίας ενός πίνακα στην Access στο VBA, είναι μέσω της χρήσης της μεθόδου DoCmd.RunSQL για την εκτέλεση ενός ερωτήματος ενέργειας με μια δήλωση SQL.
Έχουμε ένα κουμπί στο δείγμα της φόρμας μας και όταν κάνουμε κλικ στο κουμπί που θέλουμε να δημιουργήσουμε έναν πίνακα που ονομάζεται ProductsTable με δύο πεδία ή στήλες, το ένα θα είναι το κύριο πεδίο κλειδιού που ονομάζεται ProductsID και το άλλο θα είναι ένα πεδίο που ονομάζεται Πωλήσεις.
Για να δημιουργήσουμε αυτόν τον πίνακα θα χρησιμοποιήσουμε τον ακόλουθο κώδικα:
| 123456 | Private Sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"Τέλος υπο |
Το αποτέλεσμα είναι:
Φιλτράρισμα πίνακα στην Access χρησιμοποιώντας VBA
Μπορείτε επίσης να φιλτράρετε έναν πίνακα στην Access χρησιμοποιώντας τη μέθοδο DoCmd.ApplyFilter. Έχουμε τον απλό μας πίνακα που φαίνεται παρακάτω στην Access που ονομάζεται ProductsTable.
Θα θέλαμε να πατήσουμε αυτό το κουμπί στη φόρμα μας και στη συνέχεια να βλέπουμε μόνο Πωλήσεις μεγαλύτερες από 1500.
Έτσι, θα χρησιμοποιήσουμε τον ακόλουθο κώδικα για να το κάνουμε αυτό:
| 1234567 | Private Sub cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Πωλήσεις]> 1500"Τέλος υπο |
Το αποτέλεσμα είναι:
