Data på en server kan modifieras genom att undersöka posternas "klientsida" i Excel VBA, ändra dem efter behov och spara dem på servern.
Ett mer effektivt sätt att göra detta, särskilt om databasen befinner sig på en avlägsen plats och det är mycket trafik inblandad, är att göra arbetet "på serversidan". Denna övning kallar ett lagrat förfarande från Excel för att kategorisera anställda i åldersintervall efter deras födelsedatum (dvs. 18-25 år, 26-35 år, etc.), utan ett omfattande utbyte av data mellan servern och Excel.
Den här artikeln förutsätter att läsaren har utvecklarbandet och är bekant med VBA Editor. Om inte, vänligen Google "Excel-fliken för utvecklare" eller "Excel-kodfönstret".
Det finns tre element i övningen:
- En datatabell tblPersonal inom en databas TestDB;
- En lagrad procedur spAgeRange;
- En Excel xlsm, som vi kallar xlsm. Ett exempel på en Excel-fil kan hittas här.
data~~POS=TRUNC
Skapa en databas i SQL Server kallas DBTest.
Ställ in följande kolumner för en tabell tblPersonal.

Kopiera följande till tabellen:
| 2017/05/25 | 1 | Brun | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Smart | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | Kryssning | T | 1962/07/03 | M | ||
| 2017/05/25 | 4 | Lohan | L | 1986/07/02 | F | ||
| 2017/05/25 | 5 | Fredricksen | 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 |
Lagrad procedur.
Kör det här skriptet mot TestDB för att skapa den lagrade proceduren:
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
Den lagrade proceduren sparas under "Programmerbarhet" i databasen.
Excel VBA
Allt som återstår är att ringa till den lagrade proceduren från Excel och tillhandahålla Lönedatum parameter "2017/05/25". Du kommer att notera att jag helt enkelt har datatypat Lönedatum som en sträng snarare än att brottas med olika datumformat. Det är tillräckligt enkelt att konvertera en sträng till ett datum med hjälp av Konvertera funktion om Lönedatum ska användas för aritmetiska ändamål.
Skapa en ny arbetsbok. Öppna VBA-kodfönstret och sätt in en modul.
Från kodfönstrets verktygsmeny hänvisar du till lämpligt Active X 2.nn-bibliotek för att underlätta användningen av dataobjekt.
Klistra in följande kod i kodfönstret. Detta, när det är aktiverat, kommer att ansluta till SQL Serverenligt ConnectDatabase-underproceduren
'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
Lägg till en knapp i Sheet1 och tilldela den till underproceduren “WriteStoredProcedure"
Resultatet
Tryck på knappen och undersök sedan tblStaff, som ska uppdateras med åldrar och åldersintervall. Bearbetningen har ägt rum på serversidan.
Återställa skadade arbetsböcker
Om Excel kraschar kan det mycket väl ta bort din enda kopia av arbetsboken. En stor andel av gångerna kan Excel ofta inte återställa skadade arbetsböcker; i ett sådant fall kan allt arbete som gjorts sedan arbetsboken skapades gå oåterkalleligt förlorat, såvida du inte har ett verktyg för att... reparera Excel xlsx- eller xlsm-filer.
Författarintroduktion:
Felix Hooker är en dataåterställningsexpert i DataNumen, Inc., som är världsledande inom teknik för återställning av data, inklusive rar-reparation och mjukvaruprodukter för SQL-återställning. För mer information besök www.datanumen.com
