Ako volať a SQL Server Uložená procedúra z Excelu VBA

Údaje na serveri je možné upraviť preskúmaním záznamov na strane klienta v programe Excel VBA, ich prípadnou zmenou a uložením späť na server.
Efektívnejším spôsobom, ako to robiť, najmä ak je databáza na vzdialenom mieste a je tu veľa prenosu, je robiť prácu „na strane servera“. Toto cvičenie volá uloženú procedúru z Excelu na kategorizáciu zamestnancov do vekových skupín podľa ich dátumov narodenia (tj. 18-25 rokov, 26-35 rokov atď.) Bez hojnej výmeny údajov medzi serverom a Excelom.

Tento článok predpokladá, že čitateľ má zobrazenú pásku pre vývojárov a je oboznámený s editorom VBA. Ak nie, navštívte Google kartu „Vývojár Excel“ alebo „Okno kódu Excel“.

Cvičenie má tri prvky:

• Tabuľka údajov tblZamestnanci v rámci databázy TestDB;
• Uložená procedúra spAgeRange;
• Excel xlsm, ktorý zavoláme xlsm. Vzorový súbor programu Excel sa nachádza tu

Tabuľka dát

Vytvorte databázu v SQL Server tzv DBTest.

Pre tabuľku nastavte nasledujúce stĺpce tblZamestnanci.

Skopírujte do tabuľky toto:

Uložený postup.

Spustením tohto skriptu proti TestDB vytvorte uloženú procedúru:

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á procedúra sa uloží pod databázou „Programovateľnosť“.

Excel vba

Zostáva iba zavolať uloženú procedúru z Excelu a poskytnúť Dátum výplaty parameter „2017/05/25“. Všimnete si, že som jednoducho zadal údaje  Dátum výplaty ako reťazec a nie zápasiť s rôznymi formátmi dátumu. Je dosť jednoduché previesť reťazec na dátum pomocou znaku Konvertovať funkcia ak Dátum výplaty sa má používať na aritmetické účely.

Vytvorte nový zošit. Otvorte okno s kódom VBA a vložte modul.

V ponuke Nástroje v okne kódu vyhľadajte príslušné odkazy Knižnica Active X 2.nn na uľahčenie používania dátových objektov.

Vložte nasledujúci kód do okna Kód. Po aktivácii sa pripojíte k SQL Server, ako je uvedené v procedúre 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

Pridajte tlačidlo do Listu1 a priraďte ho k podprocesu “WriteStoredProcedure"

Výsledky

Stlačte tlačidlo a potom preskúmajte tblStaff, ktorý by sa mal aktualizovať podľa vekových skupín a vekových skupín. Spracovanie prebieha na strane servera.

Obnova poškodených zošitov

Ak by Excel zlyhal, mohol by so sebou strhnúť aj vašu jedinú kópiu zošita. Excel často nedokáže obnoviť poškodené zošity; v takom prípade môže byť všetka práca vykonaná od vytvorenia zošita nenávratne stratená, pokiaľ nemáte nástroj na... opraviť Excel súbory xlsx alebo xlsm.

Úvod autora:

Felix Hooker je expert na obnovu dát v DataNumen, Inc., ktorá je svetovým lídrom v oblasti technológií obnovy dát, vrátane oprava súboru RAR a softvérové ​​produkty na obnovenie sql. Pre viac informácií navštívte www.datanumen. S