Data na serveru lze upravit prozkoumáním záznamů na straně klienta v aplikaci Excel VBA, jejich změnou podle potřeby a jejich uložením zpět na server.
Efektivnější způsob, jak toho dosáhnout, zvláště pokud je databáze na vzdáleném místě a je zde spousta provozu, je dělat práci „na straně serveru“. Toto cvičení volá uloženou proceduru z Excelu, aby zaměstnance roztřídil do věkových skupin podle jejich data narození (tj. 18–25 let, 26–35 let atd.), Bez velké výměny dat mezi serverem a Excelem.
Tento článek předpokládá, že čtenář má zobrazený pás karet pro vývojáře a je obeznámen s editorem VBA. Pokud ne, prosím Google „Excel Developer tab“ nebo „Excel Code Window“.
Cvičení má tři prvky:
- Tabulka dat tblZaměstnanci v databázi TestDB;
- Uložená procedura spAgeRange;
- Excel xlsm, kterému budeme říkat xlsm. Ukázkový soubor Excel najdete zde
Tabulka dat
Vytvořte databázi v SQL Server volal DBTest.
Nastavte následující sloupce pro tabulku tblZaměstnanci.

Zkopírujte do tabulky následující:
| 2017/05/25 | 1 | Hnědý | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Chytrý | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | plavba | T | 1962/07/03 | M | ||
| 2017/05/25 | 4 | Lohane | 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 | Vysavač | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Watson | E | 1990/04/15 | F |
Uložené procedury.
Spusťte tento skript proti TestDB a vytvořte uloženou proceduru:
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
Uložená procedura se uloží pod „Programovatelnost“ v databázi.
Excel VBA
Zbývá jen zavolat uloženou proceduru z Excelu a poskytnout Datum výplaty parametr „2017/05/25“. Všimnete si, že jsem jednoduše zadal data Datum výplaty jako řetězec spíše než zápasit s různými formáty data. Je dostatečně jednoduché převést řetězec na datum pomocí Konvertovat funkce, pokud Datum výplaty se používá pro aritmetické účely.
Vytvořte nový sešit. Otevřete okno kódu VBA a vložte modul.
V nabídce Nástroje v okně kódu uveďte příslušné Knihovna Active X 2.nn k usnadnění používání datových objektů.
Vložte následující kód do okna Kód. Po aktivaci se připojíte k SQL Server, podle dílčího postupu 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
Přidejte tlačítko do Listu1 a přiřaďte jej k dílčímu postupu “WriteStoredProcedure"
Výsledky
Stiskněte tlačítko a prozkoumejte tblStaff, který by měl být aktualizován podle věku a věkových skupin. Zpracování proběhlo na straně serveru.
Obnovení poškozených sešitů
Pokud by Excel zhavaroval, mohl by s sebou strhnout i jedinou kopii sešitu. Ve značném procentu případů Excel často nedokáže poškozené sešity obnovit; v takovém případě může být veškerá práce provedená od vytvoření sešitu nenávratně ztracena, pokud nemáte k dispozici nástroj, který by to umožnil. opravit Excel soubory xlsx nebo xlsm.
Úvod autora:
Felix Hooker je odborník na obnovu dat v oboru DataNumen, Inc., která je světovým lídrem v oblasti technologií pro obnovu dat, včetně oprava rar souborů a SQL softwarové produkty pro obnovu. Pro více informací navštivte www.datanumen.com
