Πώς να χρησιμοποιήσετε το Excel για να διαβάσετε και να γράψετε μια εξωτερική βάση δεδομένων

Το Excel μπορεί να κάνει σχεδόν οτιδήποτε. αν πρέπει να γίνει να κάνουμε τα πάντα είναι άλλο θέμα. Ενώ το υπολογιστικό φύλλο είναι πολύ ισχυρό στη διαχείριση δεδομένων, δεν είναι πολύ καλό για την αποθήκευση κανονικοποιημένων δεδομένων. Αξιοποίηση του Excel σε σχεσιακή βάση δεδομένων όπως SQL Server ενισχύει τη δύναμη της εφαρμογής.

Για αρχή, θα χρειαστείτε το MS Access ή το πιο σταθερό και δωρεάν – SQL Server Εξπρές. Υποτίθεται ότι ο αναγνώστης έχει την κορδέλα του Excel Developer και είναι εξοικειωμένος με τον επεξεργαστή VBA και τη γλώσσα δομημένων ερωτημάτων (SQL). Αυτό το άρθρο χρησιμοποιεί SQL Server χορδές σύνδεσης. Για MS-Access, ανατρέξτε στο Google.

Ενώ το Excel έχει τις δικές του ενσωματωμένες ρουτίνες για τη λήψη πληροφοριών από SQL Server σε (ας πούμε) έναν συγκεντρωτικό πίνακα, το παράδειγμά μας θα δώσει μεγαλύτερη ευελιξία στην επιλογή δεδομένων.

Συμβολοσειρά σύνδεσης

Θα χρησιμοποιώ μια ιδιωτική βάση δεδομένων. εισαγάγετε τις δικές σας πληροφορίες προγράμματος οδήγησης στη θέση μου στη δευτερεύουσα ρουτίνα ConnectDatabase. Στη συνέχεια χρησιμοποιούμε connDB ως κανάλι επικοινωνίας στη βάση δεδομένων μας - στην περίπτωσή μου για να επιστρέψω αποτελέσματα από μια αποθηκευμένη διαδικασία. Μπορείτε να χρησιμοποιήσετε πιο τυπικές δηλώσεις SQL όπως "Επιλογή * από ..."

Παραγγελία επιχείρησης

Πρώτον, θα φορτώσουμε επιλογές συνδυαστικών πλαισίων από SQL Server όταν ανοίξει το βιβλίο εργασίας, χρησιμοποιώντας μια μακροεντολή Auto_open και τοποθετώντας την στο φύλλο "ComboData". Είτε ο διακομιστής βρίσκεται στο cloud είτε τοπικά, δεν θα υπάρξει αισθητή καθυστέρηση στην εκκίνηση του Excel - εφόσον η βάση δεδομένων είναι προσβάσιμη από τον σταθμό εργασίας.

Στη συνέχεια, θα εξαγάγουμε φιλτραρισμένα δεδομένα από τη βάση δεδομένων και θα τα αφήσουμε στο Excel, στήλες F έως K.

Η διασύνδεση

Το δικό μου έχει αναπτυσσόμενα πλαίσια για να φιλτράρει πληροφορίες από τη βάση δεδομένων. ο Ρόλος το σύνθετο πλαίσιο ενεργοποιεί μια αναζήτηση για τη συμπλήρωση του πίνακα στα δεξιά.

Μετονομάστε το "Sheet1" ως "Main". Προσθέστε τουλάχιστον ένα σύνθετο πλαίσιο.

Ο Κώδικας

Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer

Sub auto_Open()
    Call PopulateComboData     'kicks off the first process  on Open
End Sub

Sub PopulateComboData()
    Sheets("ComboData").Range("A3:C100").ClearContents
    Call ConnectDatabase      'use the ConnectDatabase routine
    strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("A3").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ReadData()
    intRole = Sheets("main").Range("D7")
    Sheets("Main").Range("F4:L100").ClearContents
    Call ConnectDatabase
    strSQL = "EXEC DBTest " & intRole    'calls a stored proc with parameter
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("F4").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ConnectDatabase()
    On Error GoTo ErrConnect
    If connDB.State = 1 Then connDB.Close     'closes connection if already open
    strServer = "197.200.28.164" 
    strDBase = "Qcrew_sql"
    strUser = "joesoap_sql"
    strPWD = "frU6ra!@"
    If strPWD > "" Then 
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
        ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
        ";Connection Timeout=30;"
    Else        'Use windows authentication
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
        ";Trusted_Connection=yes;DATABASE=" & strDBase
    End If
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

Διαμορφώστε το σύνθετο πλαίσιο ελέγχου για να διαβάσετε τα φύλλα "ComboData". Στη συνέχεια, κάντε δεξί κλικ στο σύνθετο πλαίσιο για να του αντιστοιχίσετε τη δευτερεύουσα διαδικασία ReadData. Όταν ένα στοιχείο έχει επιλεγεί στο σύνθετο πλαίσιο, γράψτε το κλειδί του στο φύλλο "Κύριο", κελί D7. Ο κωδικός VBA θα χρησιμοποιήσει αυτό το κλειδί ως φίλτρο (βλ. IntRole, παραπάνω).

Αναφορές στη βιβλιοθήκη dll

Χρησιμοποιήστε τα Εργαλεία>Αναφορές στο παράθυρο κώδικα για να αναφέρετε τη βιβλιοθήκη Microsoft Active X Data Objects. Αυτό θα επιτρέψει στο Excel να χρησιμοποιήσει τα αντικείμενα ADODB που δηλώνονται στον κώδικα.

Η παραπάνω ρουτίνα ReadData παραπάνω χρησιμοποιεί μια σχεσιακή δομή δεδομένων, που φαίνεται παρακάτω, η οποία είναι δύσκολο να επιτευχθεί μόνο στο Excel.

Περαιτέρω αλλαγές δεδομένων θα μπορούσαν να προκαλέσουν μια επιστροφή στη βάση δεδομένων, με την κατάλληλη δήλωση SQL Update ακολουθούμενη από connDB.execute (strSQL).

Τέλος, προστατεύστε τον κώδικά σας από την προβολή ή την αλλαγή:  Εργαλεία> Ιδιότητες> Προστασία.

Αντιμετώπιση προβλημάτων Excel:

Από καιρό σε καιρό, ειδικά όταν διαθέτει πολύπλοκα προγράμματα, το Excel ενδέχεται να παρουσιάσει σφάλμα και να μην καλύψει σωστά. Σε περίπτωση α κατεστραμμένο xlsx αρχείο, η κατοχή ενός αποτελεσματικού εργαλείου ανάκτησης θα λύσει τα περισσότερα προβλήματα.

Εισαγωγή συγγραφέα:

Ο Felix Hooker είναι ειδικός στην ανάκτηση δεδομένων DataNumen, Inc., η οποία είναι ο παγκόσμιος ηγέτης στις τεχνολογίες ανάκτησης δεδομένων, συμπεριλαμβανομένων επισκευή rar σφάλμα και sql προϊόντα λογισμικού ανάκτησης. Για περισσότερες πληροφορίες επισκεφθείτε www.datanumen.com