Jak volat a SQL Server Uložená procedura z aplikace Excel VBA

Sdílej nyní:

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.

Nastavit sloupce pro tabulku tblStaff

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ů.Pro usnadnění používání datových objektů použijte příslušnou knihovnu Active X 2.nn

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

Sdílej nyní:

Komentáře jsou uzavřeny.