Gegevens op een server kunnen worden gewijzigd door de records 'client-side' in Excel VBA te onderzoeken, ze indien nodig te wijzigen en ze weer op de server op te slaan.
Een efficiëntere manier om dit te doen, vooral als de database zich op een externe locatie bevindt en er veel verkeer bij betrokken is, is door het werk 'server-side' te doen. Deze oefening roept een opgeslagen procedure op vanuit Excel om werknemers in leeftijdscategorieën in te delen op basis van hun geboortedatum (dwz 18-25 jaar, 26-35 jaar, enz.), Zonder een uitgebreide uitwisseling van gegevens tussen de server en Excel.
In dit artikel wordt ervan uitgegaan dat de lezer het ontwikkelaarslint heeft weergegeven en bekend is met de VBA-editor. Als dit niet het geval is, gebruik dan Google "Excel Developer Tab" of "Excel Code Window".
Er zijn drie elementen in de oefening:
- Een gegevenstabel tblPersoneel binnen een database TestDB;
- Een opgeslagen procedure spAgeBereik;
- Een Excel xlsm, die we zullen noemen xlsm. Een voorbeeld van een Excel-bestand is te vinden hier
data Table
Maak een database aan in SQL Server genaamd DBTest.
Stel de volgende kolommen in voor een tabel tblPersoneel.

Kopieer het volgende naar de tabel:
| 2017/05/25 | 1 | Bruin | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Slimme | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | Cruise | T | 1962/07/03 | M | ||
| 2017/05/25 | 4 | Lohan | L | 1986/07/02 | F | ||
| 2017/05/25 | 5 | Frederiksen | 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 | Stofzuiger | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Watson | E | 1990/04/15 | F |
Opgeslagen procedure.
Voer dit script uit tegen TestDB om de opgeslagen procedure te maken:
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
De opgeslagen procedure wordt opgeslagen onder "Programmeerbaarheid" in de database.
Excel VBA
Het enige dat overblijft is om de opgeslagen procedure vanuit Excel aan te roepen en de Salarisdatum parameter van “2017/05/25”. U zult opmerken dat ik gewoon gegevens heb getypt Salarisdatum als een string in plaats van te worstelen met verschillende datumnotaties. Het is eenvoudig genoeg om een tekenreeks naar een datum te converteren met de Converteren functie als Salarisdatum moet worden gebruikt voor rekenkundige doeleinden.
Maak een nieuwe werkmap. Open het VBA-codevenster en voeg een module in.
Verwijs in het menu Extra van het codevenster naar het juiste ActiveX 2.nn-bibliotheek om het gebruik van data-objecten te vergemakkelijken.
Plak de volgende code in het codevenster. Dit zal, eenmaal geactiveerd, verbinding maken met SQL Server, volgens de subprocedure van 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
Voeg een knop toe aan Sheet1 en wijs deze toe aan de subprocedure “SchrijvenOpgeslagenProcedure"
Het Resultaten
Druk op de knop en onderzoek tblStaff, dat moet worden bijgewerkt met leeftijden en leeftijdscategorieën. De verwerking heeft server-side plaatsgevonden.
Herstel van beschadigde werkmappen
Als Excel vastloopt, kan het zomaar gebeuren dat uw enige kopie van het werkblad verloren gaat. In veel gevallen kan Excel beschadigde werkbladen niet herstellen; in dat geval kan al het werk dat sinds de aanmaak van het werkblad is gedaan onherroepelijk verloren gaan, tenzij u een hulpmiddel hebt om dit te doen. reparatie Excel xlsx- of xlsm-bestanden.
Auteur Introductie:
Felix Hooker is een expert op het gebied van gegevensherstel DataNumen, Inc., de wereldleider in technologieën voor gegevensherstel, waaronder zeldzame reparatie en sql-herstelsoftwareproducten. Voor meer informatie bezoek www.datanumen.com
