Comment appeler un SQL Server Procédure stockée à partir d'Excel VBA

Les données sur un serveur peuvent être modifiées en examinant les enregistrements "côté client" dans Excel VBA, en les modifiant si nécessaire et en les réenregistrant sur le serveur.
Un moyen plus efficace de le faire, en particulier si la base de données se trouve à un emplacement distant et qu'il y a beaucoup de trafic impliqué, consiste à effectuer le travail « côté serveur ». Cet exercice fait appel à une procédure stockée d'Excel pour catégoriser les employés par tranches d'âge selon leur date de naissance (soit 18-25 ans, 26-35 ans, etc.), sans échange copieux de données entre le serveur et Excel.

Cet article suppose que le lecteur a affiché le ruban Développeur et est familiarisé avec l'éditeur VBA. Si ce n'est pas le cas, veuillez Google "Excel Developer Tab" ou "Excel Code Window".

Il y a trois éléments dans l'exercice :

• Un tableau de données tblPersonnel dans une base de données TestDB;
• Une procédure stockée spAgeRange;
• Un xlsm Excel, que nous appellerons xlsm. Un exemple de fichier Excel peut être trouvé ici

Table de données

Créer une base de données dans SQL Server appelé Test DBT.

Configurer les colonnes suivantes pour une table tblPersonnel.

Copiez ce qui suit dans le tableau :

Procédure stockée.

Exécutez ce script sur TestDB pour créer la procédure stockée :

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

La procédure stockée sera enregistrée sous "Programmabilité" dans la base de données.

Excel VBA

Il ne reste plus qu'à appeler la procédure stockée depuis Excel, en fournissant Date de paie paramètre de "2017/05/25". Vous remarquerez que j'ai simplement tapé des données  Date de paie comme une chaîne plutôt que de lutter avec différents formats de date. Il est assez simple de convertir une chaîne en une date en utilisant le Convertir fonction si Date de paie doit être utilisé à des fins arithmétiques.

Créez un nouveau classeur. Ouvrez la fenêtre du code VBA et insérez un module.

Dans le menu Outils de la fenêtre de code, référencez le Bibliothèque Active X 2.nn pour faciliter l'utilisation des objets de données.

Collez le code suivant dans la fenêtre Code. Celui-ci, une fois activé, se connectera à SQL Server, conformément à la sous-procédure 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

Ajoutez un bouton à Sheet1 et affectez-le à la sous-procédure "Procédure d'écriture stockée »

Les Résultats

Appuyez sur le bouton, puis examinez tblStaff, qui doit être mis à jour avec les âges et les tranches d'âge. Le traitement a eu lieu côté serveur.

Récupération de classeurs corrompus

En cas de plantage d'Excel, votre unique copie du classeur risque d'être perdue avec lui. Bien souvent, Excel est incapable de récupérer les classeurs endommagés ; dans ce cas, tout le travail effectué depuis la création du classeur peut être définitivement perdu, sauf si vous disposez d'un outil de récupération. réparer Excel fichiers xlsx ou xlsm.

Introduction de l'auteur:

Felix Hooker est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparation rar et produits logiciels de récupération sql. Pour plus d'informations, visitez www.datanumen.com