Hogyan hívjunk a SQL Server Tárolt eljárás az Excel VBA-ból

A szerveren lévő adatok úgy módosíthatók, hogy az Excel VBA „kliensoldali” rekordjait megvizsgáljuk, szükség szerint módosítjuk, majd visszamentjük a szerverre.
Ennek hatékonyabb módja, különösen akkor, ha az adatbázis távoli helyen található, és nagy a forgalom, ha a munkát „kiszolgálóoldalon” végezzük. Ez a gyakorlat egy tárolt eljárást hív meg az Excelből, hogy az alkalmazottakat születési dátumuk szerinti korosztályokba sorolja (pl. 18-25 év, 26-35 év stb.), a szerver és az Excel közötti bőséges adatcsere nélkül.

Ez a cikk feltételezi, hogy az olvasó megjeleníti a Fejlesztői szalagot, és ismeri a VBA-szerkesztőt. Ha nem, használja a Google „Excel Developer Tab” vagy az „Excel Code Window” szolgáltatását.

A gyakorlatnak három eleme van:

• Adattábla tblSzemélyzet egy adatbázison belül TestDB;
• Tárolt eljárás spAgeRange;
• Egy Excel xlsm, amit hívunk xlsm. Egy minta Excel fájl található itt

Adattáblában

Hozzon létre egy adatbázist SQL Server hívott DBTest.

Állítsa be a következő oszlopokat egy táblázathoz tblSzemélyzet.

Másold be a táblázatba a következőket:

Tárolt eljárás.

Futtassa ezt a szkriptet a TestDB-n a tárolt eljárás létrehozásához:

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

A tárolt eljárás az adatbázis „Programozhatóság” részébe kerül mentésre.

Excel VBA

Nem marad más hátra, mint meghívni a tárolt eljárást Excelből, biztosítva a PayrollDate paramétere: „2017/05/25”. Megjegyzendő, hogy egyszerűen beírtam az adatokat  PayrollDate mint egy karakterlánc, nem pedig a változó dátumformátumokkal való birkózás. Elég egyszerű egy karakterláncot dátummá alakítani a Megtérít függvény if PayrollDate számtani célokra kell használni.

Hozzon létre egy új munkafüzetet. Nyissa meg a VBA kód ablakot, és helyezzen be egy modult.

A kódablak Eszközök menüjében hivatkozzon a megfelelőre Active X 2.nn könyvtár az adatobjektumok használatának megkönnyítése érdekében.

Illessze be a következő kódot a Kód ablakba. Ez az aktiválás után csatlakozni fog a következőhöz SQL Server, a ConnectDatabase aleljárás szerint

'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

Adjon hozzá egy gombot az 1. munkalaphoz, és rendelje hozzá a " aleljáráshozWriteStoredProcedure"

Az eredmények

Nyomja meg a gombot, majd vizsgálja meg a tblStaff-ot, amelyet frissíteni kell az életkorokkal és korosztályokkal. A feldolgozás szerver oldalon történt.

Sérült munkafüzetek helyreállítása

Ha az Excel összeomlik, könnyen elveszhet a munkafüzet egyetlen példánya is. Az Excel az esetek jelentős százalékában nem tudja helyreállítani a sérült munkafüzeteket; ilyen esetben a munkafüzet létrehozása óta végzett összes munka visszavonhatatlanul elveszhet, hacsak nincs erre eszköz. javítás Excel xlsx vagy xlsm fájlokat.

Szerző Bevezetés:

Felix Hooker adat-helyreállítási szakértő DataNumen, Inc., amely világelső az adat-helyreállítási technológiák területén, beleértve rar javítás és SQL helyreállítási szoftvertermékek. További információért látogasson el www.datanumen.com