Πώς να καλέσετε a SQL Server Αποθηκευμένη διαδικασία από το Excel VBA

Τα δεδομένα σε έναν διακομιστή μπορούν να τροποποιηθούν εξετάζοντας τις εγγραφές «πλευρά-πελάτη» στο Excel VBA, αλλάζοντας τις όπως απαιτείται και αποθηκεύοντας τις ξανά στον διακομιστή.
Ένας πιο αποτελεσματικός τρόπος για να το κάνετε αυτό, ειδικά εάν η βάση δεδομένων βρίσκεται σε απομακρυσμένη τοποθεσία και υπάρχει πολλή κίνηση, είναι να κάνετε την εργασία «από την πλευρά του διακομιστή». Αυτή η άσκηση καλεί μια αποθηκευμένη διαδικασία από το Excel για να κατηγοριοποιήσει τους υπαλλήλους σε ηλικιακές κατηγορίες σύμφωνα με τις ημερομηνίες γέννησής τους (δηλαδή 18-25 ετών, 26-35 ετών κ.λπ.), χωρίς άφθονη ανταλλαγή δεδομένων μεταξύ του διακομιστή και του Excel.

Αυτό το άρθρο προϋποθέτει ότι ο αναγνώστης έχει εμφανιστεί την κορδέλα προγραμματιστή και είναι εξοικειωμένος με τον επεξεργαστή VBA. Εάν όχι, παρακαλώ "Google Tab Developer Excel" ή "Excel Code Window" της Google.

Υπάρχουν τρία στοιχεία στην άσκηση:

• Πίνακας δεδομένων tblStaff μέσα σε μια βάση δεδομένων TestDB;
• Μια αποθηκευμένη διαδικασία SpageRange;
• Ένα Excel xlsm, το οποίο θα καλέσουμε xlsm. Μπορείτε να βρείτε ένα δείγμα αρχείου Excel εδώ

Πίνακας δεδομένων

Δημιουργήστε μια βάση δεδομένων στο SQL Server που ονομάζεται DBTest.

Ρυθμίστε τις ακόλουθες στήλες για έναν πίνακα tblStaff.

Αντιγράψτε τα ακόλουθα στον πίνακα:

Αποθηκευμένη διαδικασία.

Εκτελέστε αυτό το σενάριο έναντι TestDB για να δημιουργήσετε την αποθηκευμένη διαδικασία:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

Η αποθηκευμένη διαδικασία θα αποθηκευτεί στην ενότητα "Προγραμματισμός" στη βάση δεδομένων.

Excel vba

Το μόνο που μένει είναι να καλέσετε την αποθηκευμένη διαδικασία από το Excel, παρέχοντας το Ημερομηνία μισθοδοσίας παράμετρος του "2017/05/25". Θα σημειώσετε ότι έχω απλώς πληκτρολογήσει δεδομένα  Ημερομηνία μισθοδοσίας ως συμβολοσειρά και όχι πάλη με διαφορετικές μορφές ημερομηνίας. Είναι αρκετά απλό να μετατρέψετε μια συμβολοσειρά σε μια ημερομηνία χρησιμοποιώντας το Μετατρέπω λειτουργία εάν Ημερομηνία μισθοδοσίας πρόκειται να χρησιμοποιηθεί για αριθμητικούς σκοπούς.

Δημιουργήστε ένα νέο βιβλίο εργασίας. Ανοίξτε το παράθυρο κώδικα VBA και εισαγάγετε μια ενότητα.

Από το μενού Εργαλεία του παραθύρου κώδικα, αναφέρετε το κατάλληλο Βιβλιοθήκη Active X 2.nn για τη διευκόλυνση της χρήσης αντικειμένων δεδομένων.

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

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Προσθέστε ένα κουμπί στο Φύλλο1 και αντιστοιχίστε το στη δευτερεύουσα διαδικασία "WriteStoredProcedure"

Τα αποτελέσματα

Πατήστε το κουμπί και, στη συνέχεια, εξετάστε το tblStaff, το οποίο θα πρέπει να ενημερώνεται με ηλικίες και ηλικιακά εύρη. Η επεξεργασία πραγματοποιήθηκε από την πλευρά του διακομιστή.

Ανάκτηση κατεστραμμένων βιβλίων εργασίας

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

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

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