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 aquí
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:
| 2017/05/25 | 1 | Marrón | J | 1946/12/02 | M | ||
| 2017/05/25 | 2 | Smart | A | 1976/03/26 | F | ||
| 2017/05/25 | 3 | Cruise | T | 1962/07/03 | M | ||
| 2017/05/25 | 4 | Lohan | 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 | Aspiradora | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Watson | E | 1990/04/15 | F |
Procedimiento almacenado.
Ejecute este script contra TestDB para crear el procedimiento almacenado:
USE [TestDB] GO / ****** Objeto: StoredProcedure [dbo]. [SpAgeRange] Fecha del script: 2017/05/10 12:16:28 PM ****** / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON IR A CREAR PROCEDIMIENTO [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 Actualizar tblStaff set AgeRange = '> 56' donde Age> = 56 y PayrollDate = @PayrollDate Actualizar tblStaff set AgeRange = '46 a 55 'donde Age> = 46 y Age <56 y PayrollDate = PayrollDate Actualizar tblStaff set AgeRange = '39 a 45' donde Age> = 39 y Age <46 y PayrollDate = @PayrollDate Actualizar tblStaff set AgeRange = '31 a 38 'donde Age> = 30 y Age <39 y PayrollDate = @PayrollDate Update tblStaff set AgeRange = '25 to 30' donde Age> = 25 y Age <30 y PayrollDate = @PayrollDate Update tblStaff set AgeRange = '18 a 24 'donde Age> = 18 y Age <25 y PayrollDate = @PayrollDate Update tblStaff set AgeRange =' <18 'donde Age <18 y 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
'Todo "público" en caso de que el código se distribuya en varios módulos. Public connDB como nuevo ADODB.Connection Public rs como nuevo ADODB.Recordset Public strSQL como string Public strConnectionstring como string Public strServer como string Public strDBase como string Public strUser como string Public strPwd como string Public PayrollDate como string Sub WriteStoredProcedure () PayrollDate = "2017 / 05/25 "Llamar a ConnectDatabase en caso de error GoTo errSP strSQL =" EXEC spAgeRange '"& PayrollDate &"' "connDB.Execute (strSQL) Exit Sub errSP: MsgBox Err.Description End Sub Sub ConnectDatabase () If connDB.State = 1 Luego connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'El nombre o dirección IP del SQL Server
strDBase = "TestDB" strUser = "" 'deje este espacio en blanco para la autenticación de Windows strPwd = "" Si strPwd> "" Entonces strConnectionstring = "DRIVER = {SQL Server}; Servidor = "& strServer &"; Base de datos = "& strDBase &"; Uid = "& strUser &"; Pwd = "& strPwd &"; Tiempo de espera de conexión = 30; "Else strConnectionstring =" DRIVER = {SQL Server}; SERVER = "& strServer &"; Trusted_Connection = yes; DATABASE = "& strDBase 'Autenticación de Windows 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