So rufen Sie a SQL Server Gespeicherte Prozedur aus Excel VBA

Jetzt teilen:

Daten auf einem Server können geändert werden, indem die Datensätze "clientseitig" in Excel VBA untersucht, nach Bedarf geändert und auf dem Server gespeichert werden.
Ein effizienterer Weg, dies zu tun, insbesondere wenn sich die Datenbank an einem entfernten Ort befindet und viel Verkehr involviert ist, besteht darin, die Arbeit "serverseitig" auszuführen. In dieser Übung wird eine gespeicherte Prozedur aus Excel aufgerufen, um Mitarbeiter nach ihrem Geburtsdatum (dh 18 bis 25 Jahre, 26 bis 35 Jahre usw.) in Altersgruppen einzuteilen, ohne dass ein umfangreicher Datenaustausch zwischen dem Server und Excel erforderlich ist.

In diesem Artikel wird davon ausgegangen, dass der Reader das Developer Ribbon angezeigt hat und mit dem VBA-Editor vertraut ist. Wenn nicht, klicken Sie auf Google "Excel Developer Tab" oder "Excel Code Window".

Die Übung besteht aus drei Elementen:

  • Eine Datentabelle tblPersonal innerhalb einer Datenbank TestDB;
  • Eine gespeicherte Prozedur spAgeRange;
  • Ein Excel xlsm, das wir nennen werden xlsm. Eine Beispiel-Excel-Datei finden Sie werden auf dieser Seite erläutert

Datentabelle

Erstellen Sie eine Datenbank in SQL Server namens DBTest.

Richten Sie die folgenden Spalten für eine Tabelle ein tblPersonal.

Einrichten der Spalten für eine Tabelle tblStaff

Kopieren Sie Folgendes in die Tabelle:

2017/05/25 1 Braun J 1946/12/02 M
2017/05/25 2 Smart A 1976/03/26 F
2017/05/25 3 Kreuzfahrt T 1962/07/03 M
2017/05/25 4 Lohan L 1986/07/02 F
2017/05/25 5 Friedrichsen F 1964/03/15 M
2017/05/25 6 Snyder L 1968/07/05 F
2017/05/25 7 Lipnicki J 1983/11/25 M
2017/05/25 8 Hoover S 2002/12/08 F
2017/05/25 9 Watson E 1990/04/15 F

Gespeicherte Prozedur.

Führen Sie dieses Skript für TestDB aus, um die gespeicherte Prozedur zu erstellen:

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

Die gespeicherte Prozedur wird unter "Programmierbarkeit" in der Datenbank gespeichert.

Excel VBA

Sie müssen lediglich die gespeicherte Prozedur aus Excel aufrufen und die Gehaltsabrechnungsdatum Parameter von “2017/05/25”. Sie werden feststellen, dass ich einfach Daten eingegeben habe  Gehaltsabrechnungsdatum als String, anstatt mit unterschiedlichen Datumsformaten zu ringen. Es ist einfach genug, eine Zeichenfolge mit dem in ein Datum zu konvertieren Konvertieren Funktion wenn Gehaltsabrechnungsdatum ist für arithmetische Zwecke zu verwenden.

Erstellen Sie eine neue Arbeitsmappe. Öffnen Sie das VBA-Codefenster und fügen Sie ein Modul ein.

Verweisen Sie im Menü Extras des Codefensters auf das entsprechende Menü Active X 2.nn Bibliothek um die Verwendung von Datenobjekten zu erleichtern.Verweisen Sie auf die entsprechende Active X 2.nn-Bibliothek, um die Verwendung von Datenobjekten zu erleichtern.

Fügen Sie den folgenden Code in das Codefenster ein. Sobald dies aktiviert ist, wird eine Verbindung hergestellt SQL Server, gemäß der ConnectDatabase-Unterprozedur

'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

Fügen Sie Sheet1 eine Schaltfläche hinzu und weisen Sie sie der Unterprozedur zu. “WriteStoredProcedure"

Die Ergebnisse

Drücken Sie die Taste und untersuchen Sie dann tblStaff, das mit Alter und Altersgruppen aktualisiert werden sollte. Die Verarbeitung erfolgte serverseitig.

Wiederherstellen beschädigter Arbeitsmappen

Wenn Excel abstürzt, kann es passieren, dass Ihre einzige Kopie der Arbeitsmappe verloren geht. In vielen Fällen kann Excel beschädigte Arbeitsmappen nicht wiederherstellen; in diesem Fall sind alle seit der Erstellung der Arbeitsmappe geleisteten Arbeiten unwiederbringlich verloren, es sei denn, Sie verfügen über ein entsprechendes Tool. Excel reparieren xlsx- oder xlsm-Dateien.

Einführung des Autors:

Felix Hooker ist ein Datenrettungsexperte in DataNumen, Inc., das weltweit führend bei Datenwiederherstellungstechnologien ist, einschließlich seltene Reparatur und SQL Recovery-Softwareprodukte. Für weitere Informationen besuchen Sie www.datanumen.com €XNUMX

Jetzt teilen:

Kommentare sind geschlossen.