Cómo llamar a un SQL Server Procedimiento almacenado de Excel VBA

Los datos en un servidor se pueden modificar examinando los registros del 'lado del cliente' en Excel VBA, cambiándolos según sea necesario y guardándolos nuevamente en el servidor.
Una forma más eficiente de hacer esto, particularmente si la base de datos está en una ubicación remota y hay mucho tráfico involucrado, es hacer el trabajo "del lado del servidor". Este ejercicio llama a un procedimiento almacenado de Excel para clasificar a los empleados en rangos de edad de acuerdo con sus fechas de nacimiento (es decir, 18-25 años, 26-35 años, etc.), sin un copioso intercambio de datos entre el servidor y Excel.

Este artículo asume que el lector muestra la cinta de desarrollador y está familiarizado con el editor de VBA. De lo contrario, busque en Google "Pestaña de desarrollador de Excel" o "Ventana de código de Excel".

Hay tres elementos en el ejercicio:

• Una tabla de datos tblPersonal dentro de una base de datos PruebaDB;
• Un procedimiento almacenado rango de edad;
• Un xlsm de Excel, al que llamaremos xlsm. Se puede encontrar un archivo de Excel de muestra aqui

Tabla de Datos

Crea una base de datos en SQL Server , que son Prueba DB.

Configure las siguientes columnas para una tabla tblPersonal.

Copie lo siguiente en la tabla:

Procedimiento almacenado.

Ejecute este script contra TestDB para crear el procedimiento almacenado:

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

El procedimiento almacenado se guardará en "Programabilidad" en la base de datos.

Excel VBA

Todo lo que queda es llamar al procedimiento almacenado desde Excel, proporcionando el Fecha de nómina parámetro de "2017/05/25". Notarás que simplemente escribí datos  Fecha de nómina como una cadena en lugar de luchar con diferentes formatos de fecha. Es bastante simple convertir una cadena en una fecha usando el Convertir función si Fecha de nómina se utilizará con fines aritméticos.

Cree un nuevo libro de trabajo. Abra la ventana del código VBA e inserte un módulo.

En el menú Herramientas de la ventana de código, consulte el Active X 2.nn libreríarary para facilitar el uso de objetos de datos.

Pegue el siguiente código en la ventana Código. Esto, una vez activado, se conectará a SQL Server, según el subprocedimiento 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

Agregue un botón a Hoja1 y asígnelo al subprocedimiento "Procedimiento de escritura almacenado"

Los resultados

Presione el botón, luego examine tblStaff, que debe actualizarse con edades y rangos de edad. El procesamiento ha tenido lugar en el servidor.

Recuperación de libros de trabajo dañados

Si Excel falla, podría llevarse consigo su única copia del libro. Un buen porcentaje de las veces Excel no puede recuperar libros dañados; En tal caso, todo el trabajo realizado desde la creación del libro de trabajo puede ser irrevocable.cabmentira yoost, a menos que tenga una herramienta para reparar Excel Archivos xlsx o xlsm.

Introducción del autor:

Felix Hooker es un experto en recuperación de datos en DataNumen, Inc., que es el líder mundial en tecnologías de recuperación de datos, incluyendo rar reparación y productos de software de recuperación de sql. Para más información visite www.datanumen.com