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.

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.
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
