Generando Planillas de Microsoft Excel con Datos de MS SQL Server

Gestionando en Microsoft Excel

No creo necesario explicar porque Microsoft Excel es la mejor herramienta existente para trabajar con datos, tanto para usuarios técnicos como no técnicos, porque en ella podemos manipular los datos libremente, formatearlos, filtrarlos, totalizarlos, y hasta construir con los mismos tablas dinámicas y gráficos para control y análisis.

En Microsoft Excel generalmente trabajamos generando contenido, tal el caso de simulaciones o planillas para el registro de nueva información, o trabajamos controlando y analizando contenido existente en nuestro sistema de gestión.

Sistemas de Gestión

Cuando la necesidad es trabajar sobre datos existentes, he visto innumerables y complejas formas de llevar dichos datos a Microsoft Excel, las cuales dependen generalmente de la versatilidad de nuestro sistema de gestión, los cuales en la mayoría de los casos están diseñados para obtener de los mismos los reportes pre-establecidos, pero suelen olvidar que no todos los reportes que ofrecen resuelven las necesidades de control y análisis que se requieren para mejorar la gestión de una empresa.

Esto implica que o nos limitamos a gestionar la empresa en base a las herramientas, o con tiempo, dedicación, y un trabajo reiterativo cada vez que necesitamos realizar la tarea, busquemos esos complejos procesos de llevar la información del sistema de gestión a nuestra planilla de Excel para lograr nuestro objetivo.

Y esto se repite tanto para usuarios técnicos, como no técnicos, los primeros resolvemos el problema quizás de manera más eficiente, pero generalmente cometiendo el error de repetir la tarea cada vez, los segundos a prueba y error repitiendo paso a paso el procedimiento que al final nos hizo alcanzar el objetivo.

Solución

Dentro de las innumerables prestaciones de Microsoft Excel, existe la posibilidad de importar a una planilla, esto puede realizarse mediante el uso de las herramientas Obtener datos externos que se encuentran en la solapa Datos, o la de programar una Macro en VBA (Visual Basic for Applications), ejemplo que les quiero dejar hoy para que con unas sencillas modificaciones puedan utilizar no una, sino todas las veces que sea necesario, simplemente ejecutando la Macro que vamos a crear.

Ejemplo

A continuación, trataré de mostrarles paso a paso, como crear una Macro en Microsoft Excel que obtendrá  la lista de Contactos directamente desde mi Sistema de Gestión (En este caso ZetaBIZ de Colorada Soluciones de Negocio y ZetaSoftware, sobre una Base de Datos Microsoft SQL Server 2008 R2), pero el ejemplo podrán aplicarlo a cualquier dato contenido en el Sistema de Gestión, ya sean Clientes, Proveedores, Productos, Vendedores, o incluso los Movimientos de Compras, Ventas, Finanzas, siendo el único requerimiento conocer los nombres de las Tablas donde se encuentra cada dato, lo cual si no lo conocen, deberán solicitar al desarrollador o proveedor de la solución.

A su vez, si bien el ejemplo se basa en un sistema que utiliza la Base de Datos Microsoft SQL Server 2008 R2, es posible también hacerlo para versiones anteriores de Microsoft SQL Server, u otras Bases de Datos como ser Microsoft Visual FoxPro, Oracle, MySql, IBM DB2, etc.


1. Crear la planilla.


Comenzaremos ingresando a un nuevo Libro de Microsoft Excel.


2. Creando la Macro.

Presionaremos la tecla ALT+F8, para acceder a Macros, lo cual también podemos hacer desde la barra de herramientas Programador, la cual si no esta visible, deben habilitarla desde Archivo/Opciones/Personalizar cinta de opciones, y marcar la cinta Programador.

Al abrirse el cuadro de diálogo Macros, escribiremos el nombre de la Macro a crear, pudiéndose en una misma planilla, crear todas las Macros que necesiten, en mi ejemplo escribí Contactos, y luego deben presionar el botón Crear.


3. Utilizando la Biblioteca Microsoft ActiveX Data Objetcts


Al crearse la Macro, Microsoft Excel abrirá el Diseñador de VBA, pero aquí si no son usuarios expertos, no deberán asustarse, ya que sólo deberán copiar y modificar el ejemplo.

Sólo la primera vez, será necesario agregar una Referencia a las Bibliotecas Microsoft ActiveX Data Objetcts (ADO), las cuales son las que permiten que Microsoft Excel se conecte a la Base de Datos de Microsoft SQL Server, para ello en el menú Herramientas seleccionaremos la opción Referencias, y buscaremos dentro de la lista ordenada alfabéticamente Microsoft ActiveX Data Objects 2.8 Library, marcaremos la misma y presionaremos el botón Aceptar.

4. Escribiendo la Macro


Entre el inicio y el fin de la Macro:

Sub Contactos()


EndSub

Escribiremos el siguiente código, el cual podrán entender leyendo los comentarios en verde:

 'Se crea la Conexión a la Base de Datos Zeta.BIZ en el Servidor AGNOTEBOOK
    Dim ZCadenaConexion As String
    ZCadenaConexion = "Provider=SQLOLEDB;Data Source=AGNOTEBOOK;Initial Catalog=Zeta.BIZ;Integrated Security=SSPI"
 
    Dim ZConexion As ADODB.Connection
    Set ZConexion = New ADODB.Connection
 
    ZConexion.ConnectionString = ZCadenaConexion
    ZConexion.Open ( ZCadenaConexion )
 
    'Se crea el Set de Datos donde se guarda el resultado de la Consulta a la Tabla de Contactos
    Dim ZDatos As ADODB.Recordset
    Set ZDatos = New ADODB.Recordset
    ZDatos.ActiveConnection = ZConexion
    ZDatos.Open "SELECT * FROM ZBIZContactos"
     
    'Se copia el contenido del Set de Datos a la celda A1 de la Hoja1
    Hoja1.Range("A2").CopyFromRecordset ZDatos
     
    'Se cierra el Set de Datos y la Conexión a la Base de Datos
    ZDatos.Close
    ZConexion.Close

5. Personalizando la Macro

Para personalizar el ejemplo, las modificaciones que deben realizar, son el modifica la Cadena de Conexión, por los datos del Servidor y Base de Datos de donde desean obtener la información, modificando AGNOTEBOOK por el nombre del Servidor, y Zeta.BIZ por el nombre de la Base de Datos:

ZCadenaConexion = "Provider=SQLOLEDB;Data Source=AGNOTEBOOK;Initial Catalog=Zeta.BIZ;Integrated Security=SSPI"

Si no conocen esta información deberán solicitársela al encargado técnico, el desarrollador o el proveedor de la solución.,

Y la consulta a la Base de Datos, ya sea para obtener los datos existentes en una Tabla o en varias Tablas, en este ejemplo que obtiene los datos de una sola Tabla, modificando el nombre de la misma ZBIZContactos por la que deseen.

ZDatos.Open "SELECT * FROM ZBIZContactos"

Al igual que en el punto anterior, deberán solicitar esta información al encargado técnico, el desarrollador o el proveedor de la solución, si la desconocen.

Al terminar presionen el botón Volver a Microsoft Excel o cierren la ventana de VBA.

6. Ejecutando la Macro

Para ejecutar la Macro, simplemente deberán presionar la tecla ALT+F8 o , para acceder a Macros, lo cual también podemos hacer desde la barra de herramientas Programador, y si todo sale de acuerdo a lo previsto verán en la Hoja de Excel los datos en este caso de los Contactos, a partir de la celda A2, lo cual sugiero para que en la primer Fila luego de dar formato completen los nombres de las columnas.

7. Formateando los Datos

Para que nuestros datos queden presentados de forma profesional, es posilble formatearlos de manera muy sencilla gracias a las herramientas de formato de Microsoft Excel, para lo cual simplemente parandose en A1, en la cinta Inicio seleccionen la opción Dar formato como tabla, y seleccionen el formato de su preferencia, y posteriormente identifiquen en la primer Fila los nombres de las Columnas.

Conclusión

Hasta aquí una nueva forma de obtener los datos de nuestro sistema de gestión, pero lo mejor de todo, será que para la próxima vez, sólo tendrán que guardar la Planilla, abrirla y ejecutar la Macro nuevamente, con lo cual en pocos segundos contarán con la información actualizada al instante con el mínimo esfuerzo.

Espero haber podido brindarles un ejemplo lo suficientemente simple y efectivo para que puedan poner en práctica, pero si no lo he logrado, los invito a que me contacten para que pueda ayudarlos a implementarlo haciéndome llegar sus consultas a garridoalejandro@gmail.com, en particular para aquellos usuarios no técnicos que son los que en definitiva requieren en la mayoría de los casos estas soluciones, y quienes habitualmente pierden la mayor parte del tiempo generando la información que trabajando con ella.


Hasta la próxima.