Η βάση δεδομένων Access είναι ένα σύστημα διαχείρισης σχεσιακής βάσης δεδομένων που εξοικονομεί αποτελεσματικά μεγάλο αριθμό δεδομένων με οργανωμένο τρόπο. Όπου το Excel είναι ένα ισχυρό εργαλείο για την ανάλυση δεδομένων σε σημαντικές πληροφορίες. Ωστόσο, το Excel δεν μπορεί να αποθηκεύσει πάρα πολλά δεδομένα. Αλλά όταν χρησιμοποιούμε Excel και Access μαζί, η ισχύς αυτών των εργαλείων αυξάνεται εκθετικά. Έτσι, ας μάθουμε πώς να συνδέουμε τη βάση δεδομένων της Access ως πηγή δεδομένων στο Excel μέσω VBA.
Σύνδεση βάσης δεδομένων πρόσβασης ως προέλευσης δεδομένων Excel
1: Προσθήκη αναφοράς στο αντικείμενο δεδομένων AcitveX
Θα χρησιμοποιούμε ADO για σύνδεση για πρόσβαση στη βάση δεδομένων. Έτσι, πρώτα πρέπει να προσθέσουμε την αναφορά στο αντικείμενο ADO.
Προσθέστε μια ενότητα στο έργο σας VBA και κάντε κλικ στα εργαλεία. Εδώ κάντε κλικ στις αναφορές.
Τώρα αναζητήστε τη Βιβλιοθήκη αντικειμένων δεδομένων Microsoft ActiveX. Ελέγξτε την τελευταία έκδοση που έχετε. Έχω 6,1 Κάντε κλικ στο κουμπί OK και τελείωσε. Τώρα είμαστε έτοιμοι να δημιουργήσουμε έναν σύνδεσμο προς τη βάση δεδομένων της Access.
2. Γράψτε έναν κωδικό VBA Για να δημιουργήσετε μια σύνδεση με τη βάση δεδομένων πρόσβασης
Για να συνδέσετε το Excel σε μια βάση δεδομένων της Access, πρέπει να έχετε μια βάση δεδομένων της Access. Το όνομα της βάσης δεδομένων μου είναι "Δοκιμή βάσης δεδομένων.accdb "Το Αποθηκεύεται στο "C: \ Users \ Manish Singh \ Desktop" τοποθεσία. Αυτές οι δύο μεταβλητές είναι σημαντικές. Θα χρειαστεί να τα αλλάξετε ανάλογα με τις ανάγκες σας. Ο κωδικός ανάπαυσης μπορεί να διατηρηθεί ως έχει.
Αντιγράψτε τον παρακάτω κώδικα για να δημιουργήσετε τη μονάδα Excel VBA και να κάνετε αλλαγές σύμφωνα με τις απαιτήσεις σας. Έχω εξηγήσει κάθε γραμμή του κώδικα παρακάτω:
Υπο ADO_Connection () «Δημιουργία αντικειμένων σύνδεσης και εγγραφής Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String «Δήλωση πλήρως αναγνωρισμένου ονόματος βάσης δεδομένων. Αλλάξτε το με τη θέση και το όνομα της βάσης δεδομένων σας. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" «Αυτός είναι ο πάροχος σύνδεσης. Θυμηθείτε το για τη συνέντευξή σας. PRVD = "Microsoft.ace.OLEDB.12.0;" Αυτή είναι η συμβολοσειρά σύνδεσης που θα χρειαστείτε κατά το άνοιγμα της σύνδεσης. connString = "Πάροχος =" & PRVD & "Πηγή δεδομένων =" & DBPATH «ανοίγοντας τη σύνδεση conn.Open connString «το ερώτημα που θέλω να τρέξω στη βάση δεδομένων. query = "SELECT * from customerT;" "Εκτέλεση του ερωτήματος στην ανοιχτή σύνδεση. Θα λάβει όλα τα δεδομένα στο rec αντικείμενο. rec. Ανοιχτό ερώτημα, σύνδεσμος «εκκαθάριση του περιεχομένου των κελιών Cells.ClearContents «λήψη δεδομένων από τη συσκευή εγγραφής εάν υπάρχει και εκτύπωση στη στήλη Α του φύλλου excel. Αν (rec.RecordCount 0) Στη συνέχεια, κάνετε ενώ δεν είστε rec.EOF Range ("A" & Cells (Rows.Count, 1). End (xlUp). Row). Offset (1, 0). Value2 = _ rec.Fields (1). Αξία αναφοράς. Μετακίνηση επόμενου τέλους βρόχου Εάν «κλείνοντας τις συνδέσεις rec. Κλείσιμο σύνδεσης. Close End Sub
Αντιγράψτε τον παραπάνω κώδικα ή κατεβάστε το παρακάτω αρχείο και κάντε αλλαγές στο αρχείο που ταιριάζουν στις απαιτήσεις σας.
Λήψη αρχείου: Εκμάθηση βάσης δεδομένων VBAΌταν εκτελείτε αυτόν τον κώδικα VBA, το Excel θα δημιουργήσει μια σύνδεση με τη βάση δεδομένων. Στη συνέχεια, θα εκτελέσει το σχεδιαζόμενο ερώτημα. Θα καθαρίσει κάθε παλιό περιεχόμενο στο φύλλο και θα συμπληρώσει τη στήλη Α με τιμές του Πεδίου 1 (δεύτερο πεδίο) της βάσης δεδομένων.
Πώς λειτουργεί αυτή η σύνδεση βάσης δεδομένων VBA Access;
Dim conn ως νέα σύνδεση, rec ως νέο Recordset
Στην παραπάνω γραμμή, δεν δηλώνουμε απλώς τις μεταβλητές σύνδεσης και εγγραφής, αλλά τις προετοιμάζουμε απευθείας χρησιμοποιώντας τη νέα λέξη -κλειδί.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Αυτές οι δύο γραμμές είναι διαγωνιζόμενες. Το DBPATH θα αλλάξει μόνο με τη βάση δεδομένων σας. Το PRVD συνδέει τον πάροχο OLE DB.
conn.Open connString
Αυτή η γραμμή ανοίγει τη σύνδεση με τη βάση δεδομένων. Open είναι η συνάρτηση του αντικειμένου σύνδεσης που λαμβάνει πολλά ορίσματα. Το πρώτο και απαραίτητο επιχείρημα είναι το ConnectingString. Αυτή η συμβολοσειρά περιέχει τον πάροχο OLE DB (εδώ PRVD) και την πηγή δεδομένων (εδώ DBPATH). Μπορεί επίσης να λάβει τον διαχειριστή και τον κωδικό πρόσβασης ως προαιρετικά ορίσματα για προστατευμένες βάσεις δεδομένων.
Η σύνταξη του Connection. Open είναι:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Δεδομένου ότι δεν έχω κανένα αναγνωριστικό και κωδικό πρόσβασης στη βάση δεδομένων μου, χρησιμοποιώ μόνο το ConnectionString. Η μορφή του ConnectionString είναι "Provider =provider_you θέλετε να χρησιμοποιήσετε? Πηγή δεδομένων =πλήρως αναγνωρισμένο όνομα βάσης δεδομένων". Φτιάξαμε και αποθηκεύσαμε αυτήν τη συμβολοσειράconnString μεταβλητός.
query = "SELECT * from customerT;"
Αυτό είναι το ερώτημα που θέλω να εκτελέσω στη βάση δεδομένων. Μπορείτε να έχετε όποια ερωτήματα θέλετε.
rec. Ανοιχτό ερώτημα, σύνδεσμος
Αυτή η δήλωση εκτελεί το καθορισμένο ερώτημα στην καθορισμένη σύνδεση. Εδώ χρησιμοποιούμε τη μέθοδο Open του αντικειμένου setset. Όλη η έξοδος αποθηκεύεται στο αντικείμενο εγγραφήςrecΤο Μπορείτε να ανακτήσετε χειρισμό ή διαγραφή τιμών από το αντικείμενο εγγραφής.
Cells.ClearContents
Αυτή η γραμμή διαγράφει το περιεχόμενο του φύλλου. Με άλλα λόγια, διαγράφει τα πάντα από τα κελιά του φύλλου.
Αν (rec.RecordCount 0) Στη συνέχεια, κάνετε ενώ δεν είστε rec.EOF Range ("A" & Cells (Rows.Count, 1). End (xlUp). Row). Offset (1, 0). Value2 = _ rec.Fields (1). Αξία rec.MoveNext Loop End If
Το παραπάνω σύνολο γραμμών ελέγχει εάν το σύνολο εγγραφών είναι κενό ή όχι. Εάν το σύνολο εγγραφών δεν είναι κενό (σημαίνει ότι το ερώτημα επέστρεψε κάποιες εγγραφές) ο βρόχος ξεκινά και αρχίζει να εκτυπώνει κάθε τιμή του πεδίου 1 (δεύτερο πεδίο, πρώτο όνομα σε αυτήν την περίπτωση) στο τελευταίο αχρησιμοποίητο κελί στη στήλη.
(Αυτό χρησιμοποιείται απλώς για εξήγηση. Ενδέχεται να μην έχετε αυτές τις γραμμές. Αν θέλετε απλώς να ανοίξετε μια σύνδεση με τη βάση δεδομένων, τότε ο κώδικας VBA πάνω από αυτές τις γραμμές είναι αρκετός.)
Έχουμε χρησιμοποιήσει το rec.EOF για να τρέξουμε τον βρόχο μέχρι το τέλος της εγγραφής. Το rec.MoveNext χρησιμοποιείται για να προχωρήσει στο επόμενο σύνολο εγγραφών. rec. Τα πεδία (1) χρησιμοποιούνται για τη λήψη τιμών από το πεδίο 1 (το οποίο είναι δεύτερο καθώς η ευρετηρίαση πεδίου ξεκινά από το 0. Στη βάση δεδομένων μου, το δεύτερο πεδίο είναι το Όνομα του πελάτη).
rec. Κλείσιμο σύνδεσης. Κλείσιμο
Τέλος, όταν ολοκληρωθεί όλη η δουλειά που θέλαμε από το rec and conn, τα κλείνουμε.
Μπορεί να έχετε αυτές τις γραμμές σε ξεχωριστή υπορουτίνα εάν θέλετε να ανοίγετε και να κλείνετε ξεχωριστά συγκεκριμένες συνδέσεις.
Λοιπόν, ναι, έτσι δημιουργείτε μια σύνδεση με τη βάση δεδομένων ACCESS χρησιμοποιώντας το ADO. Υπάρχουν και άλλες μέθοδοι, αλλά αυτός είναι ο ευκολότερος τρόπος σύνδεσης σε μια πηγή δεδομένων πρόσβασης μέσω VBA. Το εξήγησα όσο πιο αναλυτικά μπορώ. Ενημερώστε με αν αυτό ήταν χρήσιμο στην παρακάτω ενότητα σχολίων.
Σχετικά Άρθρα:
Χρησιμοποιήστε ένα κλειστό βιβλίο εργασίας ως βάση δεδομένων (DAO) χρησιμοποιώντας VBA στο Microsoft Excel | Για να χρησιμοποιήσετε ένα κλειστό βιβλίο εργασίας ως βάση δεδομένων με σύνδεση DAO χρησιμοποιήστε αυτό το απόσπασμα VBA στο Excel.
Χρησιμοποιήστε ένα κλειστό βιβλίο εργασίας ως βάση δεδομένων (ADO) χρησιμοποιώντας VBA στο Microsoft Excel | Για να χρησιμοποιήσετε ένα κλειστό βιβλίο εργασίας ως βάση δεδομένων με σύνδεση ADO χρησιμοποιήστε αυτό το απόσπασμα VBA στο Excel.
Ξεκινώντας με το Excel VBA UserForms | Για την εισαγωγή δεδομένων στη βάση δεδομένων, χρησιμοποιούμε φόρμες. Τα Excel UserForms είναι χρήσιμα για τη λήψη πληροφοριών από τον χρήστη. Δείτε πώς πρέπει να ξεκινήσετε με τις μορφές χρηστών VBA.
Αλλάξτε την τιμή/το περιεχόμενο πολλών στοιχείων ελέγχου UserForm χρησιμοποιώντας το VBA στο Excel | Για να αλλάξετε το περιεχόμενο των στοιχείων ελέγχου μορφής χρήστη, χρησιμοποιήστε αυτό το απλό απόσπασμα VBA.
Αποτρέψτε το κλείσιμο μιας μορφής χρήστη όταν ο χρήστης κάνει κλικ στο κουμπί x χρησιμοποιώντας VBA στο Excel | Για να αποτρέψουμε το κλείσιμο της φόρμας χρήστη όταν ο χρήστης κάνει κλικ στο κουμπί x της φόρμας, χρησιμοποιούμε το συμβάν UserForm_QueryClose.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένη τιμή. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.